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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Creating a new table by conditionally copying rows from an existing table

I am trying to create a forecasting table from two tables of existing data based on some conditions. The existing tables look like this:

 

Table 1:

Project_ID

Renewable_Y/N

Renewable_Years

 

Table 2:

Project_ID

Amount

Begin_Date

End_Date

 

They are related one-many on Project_ID

 

I want to use PowerQuery to create a new table based on the following:

If Renewable_Y/N="Y", then create Renewable_Years number of rows in the new table by copying the last record in Table 2 where Project_ID=Project_ID. Increase the Begin_Date and End_Date years by 1 on each of the new records.

 

Is this possible?

 

Thanks!

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

First, add a column to show which record is the last one for each project id.

assume the max "start date" for eact product id is the last record.

in Table2, group by "product id", then add a conditional column

Capture6.JPG

you can click on the icon(blue circled) to see details.

Code in Advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDdCcAgDATgXXwWNP47i3T/NdqcbXOCgomfEm4tJ85jzyAhRZlaxlD3+fIbJIBEoBnQywyQCfQTFIBCYBjQ1xWgEpgnaACNwLN+of93iM5CTjEgBotkAiNATBbZRENrpxXZFDM6hbyJcqTyZXrd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project_ID = _t, Amount = _t, Begain_Date = _t, End_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project_ID", Int64.Type}, {"Amount", Int64.Type}, {"Begain_Date", type date}, {"End_Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project_ID"}, {{"max date", each List.Max([Begain_Date]), type date}, {"all", each _, type table [Project_ID=number, Amount=number, Begain_Date=date, End_Date=date]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Amount", "Begain_Date", "End_Date"}, {"all.Amount", "all.Begain_Date", "all.End_Date"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded all", "last record", each if [max date] = [all.Begain_Date] then 1 else 0)
in
    #"Added Conditional Column"

 

Then create Table 3,  in Table 3, merge table2 with Table3, filter rows, then add two column to show new "start date" and "end date" by adding 1 year.

Capture5.JPG

let
    Source =Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Renewable_Y/N"] = "Y")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Project_ID"}, Table2, {"Project_ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"all.Amount", "all.Begain_Date", "all.End_Date", "last record"}, {"Table2.all.Amount", "Table2.all.Begain_Date", "Table2.all.End_Date", "Table2.last record"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table2", each ([Table2.last record] = 1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "new begain date", each Date.AddYears([Table2.all.Begain_Date],1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "new end date", each Date.AddYears([Table2.all.End_Date],1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Renewable_Y/N", "Table2.all.Begain_Date", "Table2.all.End_Date", "Table2.last record"})
in
    #"Removed Columns"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

First, add a column to show which record is the last one for each project id.

assume the max "start date" for eact product id is the last record.

in Table2, group by "product id", then add a conditional column

Capture6.JPG

you can click on the icon(blue circled) to see details.

Code in Advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdDdCcAgDATgXXwWNP47i3T/NdqcbXOCgomfEm4tJ85jzyAhRZlaxlD3+fIbJIBEoBnQywyQCfQTFIBCYBjQ1xWgEpgnaACNwLN+of93iM5CTjEgBotkAiNATBbZRENrpxXZFDM6hbyJcqTyZXrd", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project_ID = _t, Amount = _t, Begain_Date = _t, End_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project_ID", Int64.Type}, {"Amount", Int64.Type}, {"Begain_Date", type date}, {"End_Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project_ID"}, {{"max date", each List.Max([Begain_Date]), type date}, {"all", each _, type table [Project_ID=number, Amount=number, Begain_Date=date, End_Date=date]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Amount", "Begain_Date", "End_Date"}, {"all.Amount", "all.Begain_Date", "all.End_Date"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded all", "last record", each if [max date] = [all.Begain_Date] then 1 else 0)
in
    #"Added Conditional Column"

 

Then create Table 3,  in Table 3, merge table2 with Table3, filter rows, then add two column to show new "start date" and "end date" by adding 1 year.

Capture5.JPG

let
    Source =Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Renewable_Y/N"] = "Y")),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Project_ID"}, Table2, {"Project_ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"all.Amount", "all.Begain_Date", "all.End_Date", "last record"}, {"Table2.all.Amount", "Table2.all.Begain_Date", "Table2.all.End_Date", "Table2.last record"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Table2", each ([Table2.last record] = 1)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "new begain date", each Date.AddYears([Table2.all.Begain_Date],1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "new end date", each Date.AddYears([Table2.all.End_Date],1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Renewable_Y/N", "Table2.all.Begain_Date", "Table2.all.End_Date", "Table2.last record"})
in
    #"Removed Columns"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Generating this table is definitely possible.

 

Filter Table1 on Renewable = "Y". Now Add a Renewable year column that is a list with the number of Renewable Years. Split Renewable year into multiple rows. Sample code is below.

let
    Source = Table1,
    #"Filtered Rows" = Table.SelectRows(Source, each ([#"Renewable Y/N"] = "Y")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Renewable Year", each {1..[Renewable Years]}),
    #"Expanded Renewable Year" = Table.ExpandListColumn(#"Added Custom", "Renewable Year")
in
    #"Expanded Renewable Year"

Merge the result above with Table2, Expand the table column and use the Date.AddYears function to add years to the BeginDates and the EndDates to create Forecast Begin Year and Forecast End Date.


Regards,

Mike

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors