Forum Discussion
Super UserFilter 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 OutputKind regards,
JB
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
- KNP
Hi Anonymous Anonymous ,
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"(_)),Anonymous - 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
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:
BB Supplier Consignor 1 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
Thank you both.
The function will work perfectly for my needs.
