Search code examples
excelpowerquerym

Power query Calendar from Month or calendar from table

I'm going to reformulate my question (first all sorry and thank you for your support).

I've a table "Horas trabajadores" with date column called "Fecha" (I cannot add images yet so I type on this way) where I don´t have all the days of the month if not several days:

Fecha
01/03/2024
03/03/2024
05/03/2024
10/03/2024
31/03/2024

I'd like create a new table called "NumberFromTo" with a column "WholeMonth" that from the month of the column "Fecha" of the table "Horas Trabajadores" generates all the days of the month to "WholeMonth"so:

WholeMonth
01/03/2024
02/03/2024
03/03/2024
04/03/2024
05/03/2024
06/03/2024
07/03/2024
...one by one until the last day
31/03/2024

Hope that would be easier to understand.

Solution

  • It is a little hard to figure out exactly what you want.

    But to create a list of dates that includes every date in the month that includes myDate, you can use:

       monthDays = List.Dates(
                    myDate, 
                    Duration.Days(Date.EndOfMonth(myDate)- Date.StartOfMonth(myDate)) +1, 
                    #duration(1,0,0,0))

    If myDate is in March 2024, this will generate a list of all the dates in March (MDY format here):
    enter image description here

    This can be converted into the column names for a table by using the Date.ToText function.

    Edit:
    *Examining your most recent edit, where Fecha has a few dates in it in the same month, you can easily produce a table with all the dates merely by, as I wrote in my comment replacing myDate in the code above, with the single date #"Horas trabajadores"[Fecha]{0}.

    So the code to produce that table could be:

    let
    Source = Excel.CurrentWorkbook(){[Name="baseDates"]}[Content],
    #"Horas trabajadores" = Table.TransformColumnTypes(Source,{{"Fecha", type date}}),
    #"Todo el mes" = Table.FromColumns ( 
        {List.Dates(
            #"Horas trabajadores"[Fecha]{0}, 
            Duration.Days(
                Date.EndOfMonth(#"Horas trabajadores"[Fecha]{0}) 
                - Date.StartOfMonth(#"Horas trabajadores"[Fecha]{0})) +1, 
            #duration(1,0,0,0))},
            type table[#"Todo el mes" = date])

in
    #"Todo el mes"

    Which produces a Table:
    enter image description here