Forum Discussion
PolarBear
Helper I
Helper ILookup (on multiple tables) with If condition on date
I need to create a table based on the values from three other tables. Anytime a new date for an ID is entered in any one of the three tables, the new date needs to be added to the new table along wit...
Hi PolarBear ,
You can handle it in Power Query Editor，please paste the following code in Advance Editor and replace the table name with corrected one:
let Source = Table.NestedJoin(t2, {"ID", "Date"}, t1, {"ID", "Date"}, "t1", JoinKind.LeftOuter), #"Expanded t1" = Table.ExpandTableColumn(Source, "t1", {"ID", "Date", "Status"}, {"t1.ID", "t1.Date", "t1.Status"}), #"Merged Queries" = Table.NestedJoin(#"Expanded t1", {"ID", "Date"}, t3, {"ID", "Date"}, "t3", JoinKind.LeftOuter), #"Expanded t3" = Table.ExpandTableColumn(#"Merged Queries", "t3", {"ID", "Date", "Severity"}, {"t3.ID", "t3.Date", "t3.Severity"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded t3",{"t1.ID", "t1.Date", "t3.ID", "t3.Date"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Date", "t1.Status", "Customers Affected", "t3.Severity"}), #"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}), #"Sorted Rows" = Table.Sort(#"Sorted Rows1",{{"ID", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"t1.Status", "t3.Severity"}), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"t1.Status", "Status"}, {"t3.Severity", "Severity"}}) in #"Renamed Columns"You can find my sample PBIX file by this link.
Best Regards
Rena
PolarBearHelper I
Helper Iamitchandak , this is close, but I need each date any of them changed, with the new value and the most recent value of the one(s) that didn't change.
Hi PolarBear ,
You can handle it in Power Query Editor，please paste the following code in Advance Editor and replace the table name with corrected one:
let
Source = Table.NestedJoin(t2, {"ID", "Date"}, t1, {"ID", "Date"}, "t1", JoinKind.LeftOuter),
#"Expanded t1" = Table.ExpandTableColumn(Source, "t1", {"ID", "Date", "Status"}, {"t1.ID", "t1.Date", "t1.Status"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded t1", {"ID", "Date"}, t3, {"ID", "Date"}, "t3", JoinKind.LeftOuter),
#"Expanded t3" = Table.ExpandTableColumn(#"Merged Queries", "t3", {"ID", "Date", "Severity"}, {"t3.ID", "t3.Date", "t3.Severity"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded t3",{"t1.ID", "t1.Date", "t3.ID", "t3.Date"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Date", "t1.Status", "Customers Affected", "t3.Severity"}),
#"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"ID", Order.Ascending}, {"Date", Order.Ascending}}),
#"Sorted Rows" = Table.Sort(#"Sorted Rows1",{{"ID", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"t1.Status", "t3.Severity"}),
#"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"t1.Status", "Status"}, {"t3.Severity", "Severity"}})
in
#"Renamed Columns"You can find my sample PBIX file by this link.
Best Regards
Rena