Filter data based on varying conditions.

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.