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
amitchandak
Super User
Super UserTry like
summarize(
New Table =
union(
selectcolumns(Table1,"ID",[ID],"Date",Table[Date],"Status",Table[Status],"Customers Affected","","Severity",""),
selectcolumns(Table2,"ID",[ID],"Date",Table[Date],"Status","","Customers Affected",Table[Customers Affected],"Severity",""),
selectcolumns(Table2,"ID",[ID],"Date",Table[Date],"Status","","Customers Affected","","Severity",Table[[Severity])
),
[ID],"Date",Max([Date]),"Status",max(Status)," Customers Affected",max([Customers Affected]),"Severity",max([Severity]))
PolarBearamitchandak , 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