Forum Discussion
Typhoon74
Helper I
Helper IHow to extend a table and updateing the date?
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?
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"
2 Replies
- MFelix
Super User
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"
