cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KNP Resolver I
Resolver I

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...

 

RowCustomer Consignor Supplier Region SubRegion Count Value
1AZZ   2011.3
2A    2212.43
3AZZBB123620.34
4C BB  3218.08
5C    5832.77
6D BB1 9654.24
7D    6938.985
8B DD  465262.725
9B AA345128.815
10B DD555531.075
11E AA  6637.29
12E AA  126.78
13E AA  63.39

 

By certain rules that reside in a "rules" table...

 

Company Field Check FieldRequired
ACustomerConsignor1
BBSupplierRegion1
BBSupplierSubRegion1
CCustomerSupplier1
DCustomerSupplier1

 

The "Company" column in the rules table doesn't map directly to a column in the data table. The "Field" column in the rules table defines which column it matches with in the data table. E.g. Company A, C and D are Customer's in the data table but company BB is a Supplier in the data table. 

 

The "Check Field" column defines the column that the rule relates to in the data table. All of the rules are designed to enforce a value if defined, i.e. they can't be null or blank if defined in the rules table. 

 

So, for the first rule, Company A who is a Customer in the data table must always have a Consignor entered. 

The next rules, Company BB who is a Supplier in the data table must always have Region and SubRegion entered. 

 

Following that logic, the end result I am looking for in this example is, only rows 2, 4, 5, 6 and 7 visible. 

Reason for this (in case it is not clear):

Row 2 doesn't have a Consignor entered for Customer A.

Row 4 doesn't have a Region or SubRegion entered for Supplier BB so would fail on two rules.

Row 5 doesn't have a Supplier entered for Customer C.

Row 6 doesn't have a SubRegion entered for Supplier BB.

Row 7 doesn't have a Supplier entered for Customer D.

 

I need the solution in M code.

I thought I'd be able to do something with... 

 

= Table.AddColumn(#"Removed Other Columns", "Custom", each List.Distinct(Table.SelectRows(Rules, (x) => Text.Contains([Customer], x[Company]) and ... )[Required]))

 

but I don't know how to incorporate the "Field" and "Check Field" columns to this.

Any suggestions appreciated. 

 

Thanks,

Kim

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: Filter Table based on values in a "rules" table

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

 

View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: Filter Table based on values in a "rules" table

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

 

View solution in original post

mcybulski Solution Specialist
Solution Specialist

Re: Filter Table based on values in a "rules" table

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

 

 

 

 

KNP Resolver I
Resolver I

Re: Filter Table based on values in a "rules" table

Hi @jborro  @mcybulski , 

 

Thank you both. I like both of those solutions for different reasons. My rules table is not likely to get very big. Probably 20 max. Although there might be a few new combinations of Field and Check Field that would require either adding to the function or repeating the merge steps depending on which solution I use.  

 

JB - if you have time could you please explain a couple of things to help my understanding. 

This line in the function, I don't fully understand what it is doing...

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))) <> "" ))

and this line in the main query (I think it is just my understanding of how the function is invoked with the '_')...

#"Added Custom" = Table.AddColumn(#"Changed Type", "Filter", each #"fApplyRules"(_)),

 

 

@mcybulski - likewise, if you wouldn't mind helping my understanding of a couple of things.

I understand this is grouping the Check Field by the Company and Field and creating a list of possible CheckFields... 

#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Company", "Field"}, {{"CheckField", each List.Combine(List.Transform([Check Field], each {_})), type text}})

...I guess I just need to understand better what this part of the main query is doing...

#"Filtered Rows" = Table.SelectRows(#"Expanded Rules", each ([Field] = "Customer") and not List.Accumulate([CheckField], true, (s,c) => s and Record.Field(_,c) <> null)),

 

 

Thanks,

Kim

Super User I
Super User I

Re: Filter Table based on values in a "rules" table

Hi Kim,

 

1. This is where filtering magic happens. As you may have more than one column "required" for each customer, this piece of code checks every condition set in the rules table: i.e. for your BB rule it consequently checks that both region and then subregion are not empty (trimming was added to eliminate " " cases). "and" logic makes the output value false if any of the sub-checks fail. If you add another rule of type:

BBSupplierConsignor1

 this code will make 3 checks (region, subregion & consignor) and so forth.

 

For more detail and examples, you can search the List.Accumulate(). This is one of the most useful functions in M. There are several really good articles on the Internet.

 

2. Correct. The _ is used as a reference to the current "hidden" parameter passed to several Table & List functions and usually has the meaning of "current row/record" for table functions and "current item" for list functions. In this case, it passes a current row from the Main table into the filtering function in a form of "type record".

 

Kind regards,

JB 

 

KNP Resolver I
Resolver I

Re: Filter Table based on values in a "rules" table

Thank you both. 

The function will work perfectly for my needs. 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors