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

[Anonymous]

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!

[v-juanli-msft · Accepted 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"

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]

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