Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JoeConradie
Regular Visitor

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

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.

 

 Rules table.PNG

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

Descriptions table.PNG

 

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

Overall function invoked.PNG

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

Resultant functions invoked.PNG

 

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.

1 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 

SU18_powerbi_badge

 

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

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 

SU18_powerbi_badge

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 

SU18_powerbi_badge

 

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors