[PowerQuery] Automatically create new data rows

[gianmarco]

Dear All,

I have the following table:

Date	Cost category	Bdg
01/01/2023	Consultants	5000
01/01/2023	Software	12000
01/01/2023	Machinery	5000
01/01/2023	Insurance	6000
01/01/2023	Cleaning	8000
01/01/2023	Maintenance	9000
01/01/2023	Taxes	10000

I want to automatically create the same exact rows till the end of the year.
In other words, create repetitions of column B and C, but column A should complete the months till 01/12/2023.

Is there any way to achieve this?

Thank you very much
gianmarco

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.ExpandListColumn(Table.AddColumn(Source, "Month number", each {1..12}),"Month number"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Date.AddMonths([Date],[Month number]-1), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Date", "Month number"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Bdg", Int64.Type}, {"Custom", type date}})
in
    #"Changed Type"

Hope this helps.

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyNjJR0l5/y84tKcksS8kmIgz9TAwEApVgdNTXB+Wkl5YlEqkGlohFWFb2JyRmZealElTjM8gdYUJeYlgwwxw6rCOSc1MS8zLx3ItMBhSWZeSWoe1BBLrGpCEitSQR4xNABLxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Cost category" = _t, Bdg = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Bdg", Currency.Type}}),
    Months = {1..12},
    #"Converted to Table" = Table.FromList(Months, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Table.ReplaceValue(#"Changed Type",#date(2023, 1, 1),#date(2023, [Column1], 1),Replacer.ReplaceValue,{"Date"})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "Cost category", "Bdg"}, {"Date", "Cost category", "Bdg"})
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".