I am attempting to transform my data in Power BI to return only the currently active (non-suspended) records, however I am struggling with it.
The tables I am working with follow the below structure:
Table #1 - Address:
PLACEID | ADDRESS |
---|---|
REF123 | 1 TEST ROAD |
REF1234 | 2 TEST ROAD |
REF12345 | 3 TEST ROAD |
Table #2 - AddressSuspendedHistory:
PLACEID | SUSPENDED? | EFFECTIVEDATE |
---|---|---|
REF1234 | TRUE | 2021-01-01 |
REF1234 | FALSE | 2023-02-01 |
REF12345 | TRUE | 2022-05-01 |
I need to return records from the Address table but only ones that aren't currently marked as suspended = true in the AddressSuspendedHistory table, but as you can see from the data, sometimes the addresses are unsuspended at a later date.
Here is the data I would wish to return from the above sample data:
Outcome Table:
PLACEID | ADDRESS |
---|---|
REF123 | 1 TEST ROAD |
REF1234 | 2 TEST ROAD |
Thanks in advance for any assistance with this!
Table 1:
Table 2:
Group the rows in Table 2 as follows:
= Table.Group(#"Changed Type", {"PLACEID"}, {{"All", each Table.First( Table.Sort( _, {{"EFFECTIVEDATE", Order.Descending}} ))}})
Expand as follows:
In Table 1, do a left outer join.
Expand:
Filter:
Full code Table 1:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PLACEID", type text}, {"ADDRESS", type text}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PLACEID"}, Table2, {"PLACEID"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"SUSPENDED"}, {"SUSPENDED"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table2", each ([SUSPENDED] <> true))
in
#"Filtered Rows"
Full code Table 2
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PLACEID", type text}, {"SUSPENDED", type logical}, {"EFFECTIVEDATE", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PLACEID"}, {{"All", each Table.First( Table.Sort( _, {{"EFFECTIVEDATE", Order.Descending}} ))}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"PLACEID"}),
#"Expanded All" = Table.ExpandRecordColumn(#"Removed Columns", "All", {"PLACEID", "SUSPENDED", "EFFECTIVEDATE"}, {"PLACEID", "SUSPENDED", "EFFECTIVEDATE"})
in
#"Expanded All"