Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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
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.
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"
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
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.
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"
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
Check out the July 2025 Power BI update to learn about new features.