How to extend a table and updateing the date?

[Typhoon74]

I do have the following data table and I am looking for a solution to turn this into a multi-row table that has the No of rows per PROJECTQUOTEID as per the PERIOD value.

Example on PROJECTQUOTEID 85 that has a Start Date 2023-01 and a period value of 9.
For this row, I would like to get 9 single rows with an updated STARTMONTH date for each new row

PROJECTQUOTESID	STARTMONTH	AVERAGECOST
85	2023-01	11111
85	2023-02	11111
85	2023-03	11111
85	2023-04	11111
85	2023-05	11111
85	2023-06	11111
85	2023-07	11111
85	2023-08	11111
85	2023-09	11111

Is that any how possible to realise with DAX or Power Query?

[MFelix · Accepted solution]

Hi @Typhoon74 

Do the following steps:

• Add a custom column with a list of values starting in 1 and ending on the number of months:

{1..[Period]}

• Expand that column

• Add a new column with the following code:

Date.AddMonths([StartMonth] ,[AdittionalPeriod]-1)

Final result:

Now you can delete the columns StartMonth and AdditionalPeriod and rename the final column

complete code here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc5RCgAhCATQu/gd7Kxl1lmi+1+jhm2FBGHgMeIY0ookUWh+8O7ENQAy0za7rNM5H9bAzEREND2wHHT7rYfxPr2qHWthfn0zFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjId = _t, StartMonth = _t, Period = _t, AverageCost = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjId", Int64.Type}, {"StartMonth", type date}, {"Period", Int64.Type}, {"AverageCost", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "AdittionalPeriod", each {1..[Period]}),
    #"Expanded AdittionalPeriod" = Table.ExpandListColumn(#"Added Custom", "AdittionalPeriod"),
    #"Added Custom1" = Table.AddColumn(#"Expanded AdittionalPeriod", "StartMonth_1", each Date.AddMonths([StartMonth] ,[AdittionalPeriod]-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"StartMonth", "AdittionalPeriod"})
in
    #"Removed Columns"

[Typhoon74]

Thank you @MFelix exactly what I was looking for, you made my day 👍