Forum Discussion

KNP's avatar
KNP
Icon for Super User rankSuper User
6 years ago
Solved

Filter Table based on values in a "rules" table

Hi,    I have an interesting scenario where I'd like to filter my main data table...   Row Customer  Consignor  Supplier  Region  SubRegion  Count  Value 1 A ZZ       20 11.3 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi KNP 

     

    if your rules table not excessively big - under 50-60 rows (even 100 should be fine, but a bit slower).

    This is the main data table:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBNDsQgCEav0rhuiIC/y3Y6l6jp/a8xgnZCmnTxkRieT7A1h251W8959rKYkO8FEdhda3M0seVJSQMJwsDY2vZdegL1cFIncFAw9NNnOpS7hazCAr4oFw1nE4uykLNiIj+sDuehSicGoPFsNpxNqqIrUEtUTuz3VIe9EJJMRKm/TAOtBt1kd/kDWS/q6gUKDhD9UxrvSGEEnycpN7/W+Z9T1uEMVAdIb6B2EuTxi8ivQvEBd931Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, Customer = _t, Consignor = _t, Supplier = _t, Region = _t, SubRegion = _t, Count = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"Customer", type text}, {"Consignor", type text}, {"Supplier", type text}, {"Region", Int64.Type}, {"SubRegion", Int64.Type}, {"Count", Int64.Type}, {"Value", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Filter", each fApplyRules(_)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Filter] = false))
in
    #"Filtered Rows"

     

    This is the fApplyRules function:

    (pRow as record) =>
    let
        mRow = pRow,
        Source = Rules,

        // Filter Supplier/Customer
        
        #"SC Filtered" = Table.SelectRows(Source, each ([Field] = "Supplier" and [Company] = mRow[Supplier]) or ([Field] = "Customer" and [Company] = mRow[Customer])),
        Output = List.Accumulate(#"SC Filtered"[Check Field], true, (s, a)=> s and (Record.Field(mRow, a) <> null  and Text.Trim(Text.From(Record.Field(mRow, a))) <> "" ))
    in
        Output

     

    Kind regards,

    JB

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This solution attempts to resolve the performance issue for larger rules tables by joining on Customer and Supplier separately.

First the rules query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIuLS7Jz00tAjHz84oz0/PyQWxDpVidaCUnJyAzuLSgICcTrCIoNT0zPw+ndHBpEpoKZ1QbkNRC5F3wyccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Company" = _t, Field = _t, #"Check Field" = _t, Required = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Required] = "1")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Company", "Field"}, {{"CheckField", each List.Combine(List.Transform([Check Field], each {_})), type text}})
in
    #"Grouped Rows"

 

Now the main query

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBdDoMwCIDv0mdDCvT3UecuYeP9r7ECnXWLCRDFr1/B1hy6xa09j6OXK8j3ggjszqU5GswPIU0kCIbwXbNt8k2gnpzUBxwUDP3tZQrFLFhtBXxRKE7oiliUg5wVEet+86A9V+nHAGS35UldkaqICtQSlRHvmGSfbEgyBKV+IRlXJ7fKqrKybBN10wIFjUP/J4zflMIIPg9QDr5vxjGfrMAZqBpFj5S2E2T7YcjPKjEBd9H5AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, Customer = _t, Consignor = _t, Supplier = _t, Region = _t, SubRegion = _t, Count = _t, Value = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Row", "Customer", "Consignor", "Supplier", "Region", "SubRegion", "Count", "Value"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"Customer"}, Rules, {"Company"}, "Rules", JoinKind.Inner),
    #"Expanded Rules" = Table.ExpandTableColumn(#"Merged Queries", "Rules", {"Field", "CheckField"}, {"Field", "CheckField"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Rules", each ([Field] = "Customer") and 
not List.Accumulate([CheckField], true, (s,c) => s and Record.Field(_,c) <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Field", "CheckField"}),
    StartSupplier = #"Replaced Value",
    #"Merged Queries1" = Table.NestedJoin(StartSupplier, {"Supplier"}, Rules, {"Company"}, "Rules", JoinKind.Inner),
    #"Expanded Rules1" = Table.ExpandTableColumn(#"Merged Queries1", "Rules", {"Field", "CheckField"}, {"Field", "CheckField"}),
    #"Filtered Rows 1" = Table.SelectRows(#"Expanded Rules1", each ([Field] = "Supplier") and 
not List.Accumulate([CheckField], true, (s,c) => s and Record.Field(_,c) <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows 1",{"Field", "CheckField"}),
    Combine = Table.Distinct(#"Removed Columns"  & #"Removed Columns1")
in
    Combine