databasepowerbipowerquerydata-modelingm

Power Bi Data Transform - Returning only currently active records


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!


Solution

  • Table 1:

    enter image description here

    Table 2:

    enter image description here

    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:

    enter image description here

    In Table 1, do a left outer join.

    enter image description here

    Expand:

    enter image description here

    Filter:

    enter image description here

    enter image description here

    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"