Filter data based on varying conditions.

[Syndicate_Admin]

Hello:

I'd like to install an advanced, variable filter in PowerQuery, which depending on one table will filter another table of data. An example would be this:

With PowerQuery I want to filter the first table depending on the conditions indicated in the second table.

The columns in table1 can be different in both names and number.

The rows in table2 can be different in both names/values and number.

The output of the powerQuery should be table1 filtered by as many rows as there are in table2 with the values indicated.

In the example, ColA = 2 and ColZ = 2.

That said, I've given the example of table 3 (which would be a more complex filter).

Solving the first example (filtering by table2) would be useful for me, but if the solution works for table 3, so much the better.

Thank you

[v-xinruzhu-msft · Accepted solution]

Hi @Syndicate_Admin 

You can create two blank query and put the following codes to advanced editor, the first table is named Query1 abd the second table is named Query2, you can filter the Query1 then the Query 2 will changed.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PcVTSUTJSitWBc4xhnCiITCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Columna = _t, Valor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Columna", type text}, {"Valor", Int64.Type}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIE4gggNlaK1YlWMgKynIA4EoiNwCLGQJYzEEcBsSFYxBAqEgFXYwQ1JxJujjHUHKiuWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, ColC = _t, ColZ = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ColZ", Int64.Type}, {"ColA", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Value", "Index"}, {"Value", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Data", {"Attribute"}, Query1, {"Columna"}, "Query1", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each List.Min(Table.SelectRows([Query1],(x)=>x[Valor]=[Value])[Valor])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]<>null then List.Min(Table.SelectRows(#"Added Custom",(x)=>x[Index]=[Index] and x[Custom]<>null and x[Attribute]<>[Attribute])[Value]) else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Table.RowCount(Table.SelectRows(#"Added Custom1",(x)=>x[Index]=[Index] and x[Custom.1]<>null))>0 then [Index] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.2] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Query1", "Custom", "Custom.1", "Custom.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.