Invoking a list of custom functions to a table in another query resulting in a column with tables

[JoeConradie]

Good day everyone

I've been struggling with various issues on a report for days (or even weeks) now and I really need some assistance. I've been watching various videos on Youtube, I read various blogs and also read a lot of posts on Power BI community. This has assisted me quite a few times and gave me solutions to some of my issues, but I'm at a point now that I don't know what to do anymore and really need some additional assistance.

To give some background of what I want to achieve:
I want my query to evaluate a list of descriptions for certain rules. Each rule has certain words that needs to be contained and words that should not be contained within the description that will indicate a true or false for the particular rule (e.g. if the rule is that the description should contain "test" and not contain "account", then the description "Testing account" will result in a false but the description "Test product" will result in a true.

I have a table that contains the various rules (which also enables me to add additional rules where necessary). Within this table the list of contains words and does not contain words are also included.

 

 

I then have another table that contains all the descriptions on which the rules should be evaluated. The rules should be evaluated on the "Part number & description":

 

At this stage I have functions that will get the contains list and does not contains list for any rule and then also another function that firstly identifies the list of contains words and the list of does not contains words for any particular rule (by using the two rules previously mentioned) and the function then also can be applied to any string to test whether the string results in a true or false for the particular rule. See the script for these rules below:

Contains rule:

(Rule as text) =>
let

Source = Table.SelectColumns(#"Version 3 rules",{"Version 3 rule number","Contains (AND)","Does not contain (OR)"}),
#"Filtered Rows" = Table.SelectRows(Source, each ([Version 3 rule number] = Rule)),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Contains (AND)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Contains (AND)"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contains (AND)", type text}}),
#"Contains (AND)" = #"Changed Type"[#"Contains (AND)"]

in

if #"Contains (AND)" = {""}
then {} else #"Contains (AND)"

Does not contain rule:

(Rule as text) =>
let

Source = Table.SelectColumns(#"Version 3 rules",{"Version 3 rule number","Contains (AND)","Does not contain (OR)"}),
#"Filtered Rows" = Table.SelectRows(Source, each ([Version 3 rule number] = Rule)),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Does not contain (OR)", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Does not contain (OR)"),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Does not contain (OR)", type text}}),
#"Does not contain (OR)" = #"Changed Type"[#"Does not contain (OR)"]

in

if #"Does not contain (OR)" = {""}
then {} else #"Does not contain (OR)"

Overall rule:

(Rule as text) =>
(String) =>
let

//Gets Contains list
ContainsList = #"Contains rule"(Rule),

//Gets Does not contain list
DoesNotContainList = #"Does not contain rule"(Rule),

//check if values in Contains list is included in String
ContainsMatchFound = List.Transform(List.Buffer(ContainsList), each Text.Contains(String, _, Comparer.OrdinalIgnoreCase)),

//index position of contains match found
ContainsPosition = List.PositionOfAny(ContainsMatchFound, {true}, 2),

//Size of original contains list
ContainsListSize = List.Count(ContainsList),

//Size of contained list (words on contains list found in description)
ContainedListSize = List.Count(ContainsPosition),

//check if values in Does not contain list is included in String
DoesNotContainMatchFound = List.Transform(List.Buffer(DoesNotContainList), each Text.Contains(String, _, Comparer.OrdinalIgnoreCase)),

//index position of does not contains match found
DoesNotContainPosition = List.PositionOfAny(DoesNotContainMatchFound, {true}, 2),

//Size of does not contains list
DNCListSize = List.Count(DoesNotContainPosition),

//return matched does not contain word if a does not contain word was found, returns true if not does not contain is found and a contained is found otherwise returns null
Return = if DNCListSize = 0 and ContainsListSize = ContainedListSize

then true
else if DNCListSize = 0 and (ContainedListSize = 0 or ContainedListSize < ContainsListSize)
then "FALSE - Not all CONTAINS words found"
else if DNCListSize - 1 = 0
then "FALSE - Contains: " & #"V3R1 Does not contain"{DoesNotContainPosition{0}}
else if DNCListSize - 2 = 0
then "FALSE - Contains: " & #"V3R1 Does not contain"{DoesNotContainPosition{0}} & " & " & #"V3R1 Does not contain"{DoesNotContainPosition{1}}
else if DNCListSize - 3 = 0
then "FALSE - Contains: " & #"V3R1 Does not contain"{DoesNotContainPosition{0}} & ", " & #"V3R1 Does not contain"{DoesNotContainPosition{1}} & " & " & #"V3R1 Does not contain"{DoesNotContainPosition{2}}
else if DNCListSize - 4 = 0
then "FALSE - Contains: " & #"V3R1 Does not contain"{DoesNotContainPosition{0}} & ", " & #"V3R1 Does not contain"{DoesNotContainPosition{1}} & ", " & #"V3R1 Does not contain"{DoesNotContainPosition{2}} & " & " & #"V3R1 Does not contain"{DoesNotContainPosition{3}}
else "FALSE - Contains: " & #"V3R1 Does not contain"{DoesNotContainPosition{0}} & ", " & #"V3R1 Does not contain"{DoesNotContainPosition{1}} & ", " & #"V3R1 Does not contain"{DoesNotContainPosition{2}} & ", " & #"V3R1 Does not contain"{DoesNotContainPosition{3}} & " & " & #"V3R1 Does not contain"{DoesNotContainPosition{4}}

in

Return

I invoked the overall function to a referenced table of my rules table (to the column that contains the various rule numbers) which then results in a column that contains the function for each of the rules (that needs to be invoked over the part table "Part number and description" field).

Next I want to invoke each of these functions to the Part table (the Part number and description column). This should result in a column that contains the tables of the various rules invoked over the Part table. When I try to do this, however, it results in an error: "Expression.Error: The field 'Part number & description' of the record wasn't found.
Details:
Version 3 rule number=V3R1
Query1=[Function]"

See my script for these last steps below:

let

Source = Table.SelectColumns(#"Version 3 rules","Version 3 rule number"),
#"Invoked Custom Function" = Table.AddColumn(Source, "Query1", each Query1([Version 3 rule number])),
AddedColumn = Table.AddColumn(#"Invoked Custom Function","Tables",each Table.AddColumn(#"Testing parts table","Rule",[#"Query1"]([#"Part number & description"])))

in

AddedColumn

Can someone please assist me with this? What is the reason for the error? Is what I want to achieve possible? What would be a different way to approach this?

Thank you so much in advance.

[AlB]

Hi @JoeConradie 

Can you share the pbix with the queries? It'll be much easier to try and find the cause of the error.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[AlB · Accepted solution]

Hi @JoeConradie 

I'm not sure I am following completely but I do see a problem with your last step that I believe is the reason for the error. You have:

AddedColumn = Table.AddColumn(#"Invoked Custom Function","Tables",each Table.AddColumn(#"Testing parts table","Rule",[#"Query1"]([#"Part number & description"]) ))

Table.AddColumn needs a function as third argument, the function that will be applied to each row of the new column. Table.AddColumn invokes, for each row, that function it receives with the current trow as argument.  You are not passing a function to Table.AddColumn but a call to a function with an argument; so you are effectively passing on whatever that function returns. Try this instead (changes in green):      

AddedColumn = Table.AddColumn(#"Invoked Custom Function","Tables",each Table.AddColumn(#"Testing parts table","Rule",(currentRow)=>[#"Query1"](currentRow[#"Part number & description"]) ))

With this you are defining a function that receives the current row as input argument and applies the function [#"Query1"] to the  [#"Part number & description"] field in that row. Note that we do not use the usual each for the inner Table.AddColumns because you have two nested Table.AddColumns and we need to explicitly establish which row we are referring to (the one for the outer or the one for inner Table.AddColumn). That should eliminate the error you are getting now, although I am not sure it is exactly what you want in terms of functionality.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[JoeConradie]

Hi @AlB 

Thank you so so much for this. It took away the errors and is exactly what I want in terms of functionality. I really appreciate your assistance on this and will definitely request your assistance with future issues I encounter.

I'm still quite new to the M language so I think I understand what you tried to explain but I don't understand exactly what the problem was. I will spend some time on this, however, to make sure that I understand the logic of why I got the errors and why your solution took away the errors.

Have a blessed day and rest of the week.

Regards,

Joe Conradie