Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Typhoon74
Helper I
Helper I

How 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.

Typhoon74_1-1668176071764.png

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

 

PROJECTQUOTESIDSTARTMONTHAVERAGECOST
852023-0111111
852023-0211111
852023-0311111
852023-0411111
852023-0511111
852023-0611111
852023-0711111
852023-0811111
852023-0911111

 

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

1 ACCEPTED SOLUTION
MFelix
Super User
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

MFelix_0-1668272243613.png

  • Add a new column with the following code:
Date.AddMonths([StartMonth] ,[AdittionalPeriod]-1)

Final result:

MFelix_1-1668272284477.png

 

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"

 

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
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

MFelix_0-1668272243613.png

  • Add a new column with the following code:
Date.AddMonths([StartMonth] ,[AdittionalPeriod]-1)

Final result:

MFelix_1-1668272284477.png

 

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"

 

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.