How to filter a table by multiple (dynamic) conditions from another table?

Question

I am stuck trying to get more flexibility in my queries.

I have facts table:

enabled	country	color	count	vehicle
yes	DE	yellow	one	car
no	FR	blue	one	car
no	DE	red	one	bike
yes	DE	green	one	car
yes	ES	yellow	one	car
yes	IT	yellow	two	car
yes	IT	yellow	one	car

and in Excel I also would like to have two simple configuration tables:

Table: RemoveRecordsByValue a table with field-value pairs, that act as conditions to remove those records if there is any match.

field	value
vehicle	bike
color	green
color	red

Table: KeepRecordsByValue a table with potentially

field	value
country	DE
country	IT
count	one

The expected table would be:

enabled	country	color	count	vehicle
yes	DE	yellow	one	car
yes	IT	yellow	one	car

Anybody might have a hint how to solve this?

Thanks in advance,

***FactsTable***

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqkwtVtJRcnEFEpWpOTn55UBGfl4qkExOLFKK1YlWyssHctyCgERSTmkqdmmw/qLUFLhsUmZ2Klgayfz0otTUPAz9EAWuwbgdAFHhGYKsoqQ8n4AKJDNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [enabled = _t, country = _t, color = _t, count = _t, vehicle = _t]),
    Result = Table.TransformColumnTypes(Source,{{"enabled", type text}, {"country", type text}, {"color", type text}, {"count", type text}, {"vehicle", type text}})
in
    Result


***RemoveRecordsByValue***

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkvNyEzOSVXSUUrKzE5VitWJVkrOz8kvAgqkF6Wm5qGIFKWmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [field = _t, value = _t]),
    Result = Table.TransformColumnTypes(Source,{{"field", type text}, {"value", type text}})
in
    Result


***KeepRecordsByValue***

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs4vzSspqlTSUXJxVYrVQRbwDEEIALn5ealKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [field = _t, value = _t]),
    Result = Table.TransformColumnTypes(Source,{{"field", type text}, {"value", type text}})
in
    Result
´´´

horseyride · Accepted Answer · 2021-11-11 21:42:06Z

1

Likely a million ways to do this. Here's one

Load both KeepRecordsByValue and RemoveRecordsByValue as separate powerquery queries

Load the facttable. Add index. Click select index and unpivot other columns.

Merge facttable with RemoveRecordsByValue using left outer join and expand one field.

Merge results with KeepRecordsByValue using left outer join and expand one field.

Group results on index, and combine the expanded fields from above

Filter out anything with text in the RemoveRecordsByValue combined column and anything with a blank in the KeepRecordsByValue combined column

That table has the only index items you want to keep. Merge that back against original facttable with inner join on index

let Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns",{"Attribute", "Value"},RemoveRecordsByValue,{"field", "value"},"RemoveRecordsByValue",JoinKind.LeftOuter),
#"Expanded RemoveRecordsByValue" = Table.ExpandTableColumn(#"Merged Queries", "RemoveRecordsByValue", {"value"}, {"Remove"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded RemoveRecordsByValue",{"Attribute", "Value"},KeepRecordsByValue,{"field", "value"},"KeepRecordsByValue",JoinKind.LeftOuter),
#"Expanded KeepRecordsByValue" = Table.ExpandTableColumn(#"Merged Queries1", "KeepRecordsByValue", {"value"}, {"keep"}),
Grouped= Table.Group(#"Expanded KeepRecordsByValue", {"Index"}, {{"Remove", each Text.Combine([Remove]," "), type text},{"Keep", each Text.Combine([keep]," "), type text}}),
#"Filtered Rows" = Table.SelectRows(Grouped, each ([Remove] = "") and ([Keep] <> "")),
#"Merged Queries2" = Table.NestedJoin(#"Added Index",{"Index"},#"Filtered Rows",{"Index"},"zzz",JoinKind.Inner),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries2",{"Index", "zzz"})
in  #"Removed Columns"

edited Nov 11, 2021 at 21:42

answered Nov 11, 2021 at 21:03

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SimplePowerBIUser Over a year ago

Hi horseyride, thanks for your feedback. Using the joins is an interesting way to solve it. I did not thought about it yet, because I tried not to touch the whole tables until I reduced it by filtering for performance reasons. (facts table is 2x 5 MB external xlsx) Going to check and try out your solution tomorrow. One thing I noticed: KeepRecordsByValue is not meant to keep by any value, but if a field is defined, one of it's values has to be there. i.e.: if country is defined , the record has to be with DE or IT , but a different one is not valid.

SimplePowerBIUser Over a year ago

The way I tried to solve it the whole day was by using one Table.Select for each KeepRecordsTable and RemoveRecordsTable in combination with nested / recursive List.Contains and referencing the ColumnNames or RecordFields by using their texts to reference to column (by field) and compare against value. ` filterTable = Table.SelectRows(factsTable, (row) => .....some recursive ....... (recordList, (columnToFilter) => List.Contains(columnToFilter[Value], Record.Field(row, columnToFilter[Field]) ))),

Collectives™ on Stack Overflow

How to filter a table by multiple (dynamic) conditions from another table?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related