Power Query

[Gladiador1392]

Tenho uma Base Dados cuja data é uma coluna com o ano e outra é o mês (Ex: Colu1 2025, Colu2 Janeiro). Gostaria de transformar a data para uma única coluna (Ex: 01/01/2025). Mas, existe um outro problema, o mês de Janeiro, que vai de 1º a 31 , na tabela, contem uma quantidade de linhas que excede a quantidade de dias do mês. Minha intenção é que se o mês de janeiro tiver 100 linhas de dados, essas linhas pudessem ficar da seguinte forma: Linhas 1 a 31 -> 01 a 31/01/2025; Linhas 32 a 62 -> 01 a 31/01/2025; Linhas 63 a 93 -> 01 a 31/01/2022, ... ate cobrir as 100 linhas. Confesso que não consegui realizar essa operação no Power Query. Alguém mais experiente poderia me ajudar dom isso, por favor? Obrigado!

[AlienSx · Accepted solution]

easy 

let
    // get date from day, month and year
    get_date = (lst) => Date.From(Text.Format("#{0} #{1} #{2}", lst)),
    // generate consequtive dates and add dates column to original table
    fx = (tbl) => [
        rows = Table.RowCount(tbl),
        year = tbl{0}[year],
        month = tbl{0}[month],
        days_in_month = Date.DaysInMonth(get_date({1, month, year})),
        dates = List.Generate(
            () => 1, 
            (x) => x <= rows,
            (x) => x + 1,
            (x) => get_date(
                {
                    ((d) => if d = 0 then days_in_month else d)(Number.Mod(x, days_in_month)),
                    month,
                    year
                }
            )
        ),
    z = Table.FromColumns(Table.ToColumns(tbl) & {dates}, Table.ColumnNames(tbl) & {"date"})
    ][z],
    // this is sample table, use yours
    Source = #table(
        type table [year = number, month = text, some_data = text],
        List.Repeat({{2025, "January", "jan data"}}, 100) & 
        List.Repeat({{2025, "February", "feb data"}}, 300)
    ),
    // group data by year and month, apply fx function
    group = Table.Group(Source, {"year", "month"}, {"x", fx}),
    // combine year-month tables
    result = Table.Combine(group[x])
in
    result

[v-veshwara-msft]

Hi @Gladiador1392 ,
Thanks for reaching out to Microsoft Fabric Community.
Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[Gladiador1392]

Me ajudaram sim. Obrigado.

[v-veshwara-msft]

Hi @Gladiador1392 ,
Thanks for confirming and glad that the responses were helpful. If further assistance is needed, please reach out.

Thank you.

[ralf_anton]

Hi,

in meinem Beispiel gibt es in der Quelltabelle "Tabelle1" nur die Spalten "Year", "Month" und "Wert".

Ziel: zu jeder Zeile aus den Monatsspalten pro Jahr und Wert ein Tagesdatum zu bilden.

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    AddMonat = Table.AddColumn(Quelle, "MonatsNr", each Date.Month( Date.FromText("1." & [Month] & Text.From([Year])))),
    AddAnzTage = 
    Table.AddColumn(
        AddMonat, "AnzTage", each 
            Date.DaysInMonth(#date([Year],Date.Month( Date.FromText("1." & [Month] & Text.From([Year]))),1))
        ),
    #"Gruppierte Zeilen" = Table.Group(AddAnzTage, {"Year", "MonatsNr"}, {{"Gruppe", each _, type table [Year=number, Month=text, Wert=text, MonatsNr=number, AnzTage=number,AllIndex=number]}}),
    AddGroupIndex = Table.AddIndexColumn(#"Gruppierte Zeilen", "Index", 0, 1, Int64.Type),
    AddDaylist = Table.AddColumn(AddGroupIndex, "Tagesliste", each  {1..Number.From([Gruppe][AnzTage]{[Index]})}),    
    ExpandDaylist = Table.ExpandListColumn(AddDaylist, "Tagesliste"),
    AddDate = Table.AddColumn(ExpandDaylist, "Datum", each #date([Year],[MonatsNr],[Tagesliste])),
    SelectDatCol = Table.SelectColumns(AddDate,{"Year", "Datum"}),
    AddIndex = Table.AddIndexColumn(SelectDatCol, "Index", 0, 1, Int64.Type),
    AddValues = Table.AddColumn(AddIndex, "Wert", each AddAnzTage[Wert]{[Index]}),
    SelectColumns = Table.RemoveColumns(AddValues,{"Index"})
in
    SelectColumns

[ronrsnfld · Accepted solution]

Try this:

After grouping by month and year, we use an Index column and the Number.Mod function to calculate the relevant day of the month according to your rules

let

    //Create Sample Table 100 rows Jan; 50 rows February; 50 rows March
    //You should replace this section with your own table
    ColumnHeaders = {"Year", "Month","Category","Description","Value"},
    Year = List.Repeat({2025},200),
    Month = List.Repeat({"January"},100) & List.Repeat({"February"},50) & List.Repeat({"March"},50),
    Category = List.Repeat({"Expense"}, 200),
    Description = List.Repeat({"Food"},200),
    Value = List.Repeat({147.28}, 200),
Source=Table.FromColumns({
        Year , Month , Category , Description, Value}, ColumnHeaders),

//Group by Year and Month Columns
    #"Grouped Rows" = Table.Group(Source, {"Year", "Month"}, {
        {"Date", (t)=> [a=Table.AddIndexColumn(t, "Index",0,1,Int64.Type), //Add Index Column
                        b=Table.AddColumn(a,"Date", each //add date column will = first of the month
                            Date.FromText(Text.Combine({Text.From([Year]),[Month]}," "))),

                        c=Date.Day(Date.EndOfMonth(List.First(b[Date]))), //Number of days in relevant month

                        //Transform the date column into a date using the Index column and the 
                        // Number.Mod function to calculate the relevant day
                        d=Table.ReplaceValue(
                            b,
                            each [Date],
                            each [Index],
                            (x,y,z)=>Date.AddDays(y,Number.Mod(z,c)),
                            {"Date"}
                        )][d],
                        type table [Year=number, Month=text, Category=text, Description=text, Value=number, Date=date]}}),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Year", "Month"}),
    #"Expanded Date" = Table.ExpandTableColumn(#"Removed Columns", "Date", 
                            {"Year", "Month", "Category", "Description", "Value","Date"})
in
    #"Expanded Date"

[AlienSx · Accepted solution]

easy 

let
    // get date from day, month and year
    get_date = (lst) => Date.From(Text.Format("#{0} #{1} #{2}", lst)),
    // generate consequtive dates and add dates column to original table
    fx = (tbl) => [
        rows = Table.RowCount(tbl),
        year = tbl{0}[year],
        month = tbl{0}[month],
        days_in_month = Date.DaysInMonth(get_date({1, month, year})),
        dates = List.Generate(
            () => 1, 
            (x) => x <= rows,
            (x) => x + 1,
            (x) => get_date(
                {
                    ((d) => if d = 0 then days_in_month else d)(Number.Mod(x, days_in_month)),
                    month,
                    year
                }
            )
        ),
    z = Table.FromColumns(Table.ToColumns(tbl) & {dates}, Table.ColumnNames(tbl) & {"date"})
    ][z],
    // this is sample table, use yours
    Source = #table(
        type table [year = number, month = text, some_data = text],
        List.Repeat({{2025, "January", "jan data"}}, 100) & 
        List.Repeat({{2025, "February", "feb data"}}, 300)
    ),
    // group data by year and month, apply fx function
    group = Table.Group(Source, {"year", "month"}, {"x", fx}),
    // combine year-month tables
    result = Table.Combine(group[x])
in
    result

[BA_Pete]

Hi @Gladiador1392 ,

If there is no indication in the data what the exact date (day) of the row is, then there's absolutely no value in assigning days to the rows - you won't be able to use this information for charts or trends as it will be invented/meaningless.

Just create your date column as the first of each month (2025-01-01, 2025-02-01 etc.) and this will relate to a calendar table and allow monthly reporting/visualisation/time intelligence just fine (assuming you relate to a proper date table).

Pete