Lookup (on multiple tables) with If condition on date

[PolarBear]

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 with the most recent entry for the ID from all three tables. 

ID	Date	Status
3321	1/10/20 8:34 AM	Red
3400	1/10/20 1:52 PM	null
3412	1/11/20 7:28 AM	Green
3482	1/12/20 2:24 PM	Yellow
3512	1/13/20 9:32 AM	Yellow
3400	1/13/20 11:22 AM	Yellow
ID	Date	Customers Affected
3321	1/10/20 8:34 AM	All
3400	1/10/20 1:52 PM	null
3412	1/11/20 7:28 AM	None
3482	1/12/20 2:24 PM	None
3512	1/13/20 9:32 AM	All
3400	1/13/20 11:22 AM	None
3321	1/15/20 3:41 PM	None
ID	Date	Severity
3321	1/10/20 8:34 AM	High
3400	1/10/20 1:52 PM	null
3412	1/11/20 7:28 AM	Low
3482	1/12/20 2:24 PM	Low
3512	1/13/20 9:32 AM	High
3400	1/13/20 11:22 AM	Low

Expected Results

ID	Date	Status	Customers Affected	Severity
3321	1/10/20 8:34 AM	Red	All	High
3400	1/10/20 1:52 PM	null	null	null
3412	1/11/20 7:28 AM	Green	None	Low
3482	1/12/20 2:24 PM	Yellow	None	Low
3512	1/13/20 9:32 AM	Yellow	All	High
3400	1/13/20 11:22 AM	Yellow	None	Low
3321	1/15/20 3:41 PM	Red	None	High

[Greg_Deckler]

I cannot get your sample data to jive with the expected results, specifically for 3321, the second entry.

[PolarBear]

@Greg_Deckler , 

I apologize, I pasted incorrect data... I updated my expected results.

thank you

[amitchandak]

@PolarBear ,

Try 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]))

[PolarBear]

@amitchandak , 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.

[Anonymous · Accepted solution]

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