CODEWITHSUNDEEP
databasepowerbipowerquerydata-modelingm
Gwillz
Gwillz

Reputation: 165

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!

Upvotes: 1

Views: 170

Answers (2)

Marcus
Marcus

Reputation: 3995

I have made a similar solution to @David but I like to avoid grouping when I can in Power Query - instead I use a small trick involving removing duplicates, ordering of the table, and a call to Table.StopFolding to isolate the last given status of each distinct PLACEID in Table 2.

The solution is completed by isolating all PLACEIDs you want to get rid of in Table 2 (i.e. where the last status is true), and removing these from Table 1 with a left anti join.

Example snippets for Table 2 and Table 1 (in reverse order since you need Table 2 for Table 1):

let
  Source = Table.FromRecords({
    [PLACEID="REF1234", SUSPENDED=true, EFFECTIVEDATE="2021-01-01"],
    [PLACEID="REF1234", SUSPENDED=false, EFFECTIVEDATE="2023-02-01"],
    [PLACEID="REF12345", SUSPENDED=true, EFFECTIVEDATE="2022-05-01"]
  }),
  #"Sorted rows" = Table.Sort(Source, {{"EFFECTIVEDATE", Order.Descending}}),
  #"Removed duplicates" = Table.Distinct(Table.StopFolding(#"Sorted rows"), {"PLACEID"}),
  #"Filtered rows" = Table.SelectRows(#"Removed duplicates", each ([SUSPENDED] = true))
in
  #"Filtered rows"

let
  Source = Table.FromRecords({
    [PLACEID="REF123", ADDRESS="1 Test Road"],
    [PLACEID="REF1234", ADDRESS="2 Test Road"],
    [PLACEID="REF12345", ADDRESS="3 Test Road"]
  }),
  #"Merged queries" = Table.NestedJoin(
    Source, "PLACEID", 
    #"Table 2", "PLACEID", 
    "Data", JoinKind.LeftAnti
  ),
  #"Removed columns" = Table.RemoveColumns(#"Merged queries", {"Data"})
in
  #"Removed columns"

Upvotes: 2

davidebacci
davidebacci

Reputation: 30174

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"

Upvotes: 2

Related Questions