Receiving errors when trying to pass and evaluate fields between functions

[Syndicate_Admin]

Hi folks,

Hoping someone can help me because I'm stumped (and also kind of a newbie).

I have made a custom function, and need to reference it from my main query, passing in each record and a specific field, then doing some if then analysis on it, and returning true or false. However, now matter what I try, I get this error: "We cannot convert a value of type Record to type List.", but I went off Microsoft's example here: Understanding Power Query M functions - PowerQuery M | Microsoft Learn

Can anyone help me figure out what is wrong?

Here is my function that is erroring:

let
#"Output Test" = (r,c) =>
if Table.SelectRows(
Table.FromRecords(r),
each c = Text.Contains(Record.Field(r, c),"(SMT)")
) then true else false
in
#"Output Test"

This is the call I'm making to it:

    #"Added Custom" = Table.AddColumn(#"Flag 2b", "Flag 2 Test", each #"Output Check"(_,[DescriptionTypeValue]))

 I'm really stumped. Any help is greatly appreciated.

Cheers!

[Syndicate_Admin]

Hi @Gabe_V ,
your function expects a list of records as the first parameter, but you are feeding it a simple record instead (_ represents all fields of the current row in a record format).
Modifying the call like so should work technically, but I cannot say if that is what you were really after with your function at the end:

  #"Added Custom" = Table.AddColumn(#"Flag 2b", "Flag 2 Test", each #"Output Check"({_},[DescriptionTypeValue]))

[Syndicate_Admin]

Hi @ImkeF Thank you for your feedback. I tried what you suggested, and although I think it fixed that problem, now I get "Expression.Error: We cannot convert a value of type Table to type Logical." In my function, I am using two nested Table functions, but I don't see how else I can reach my objective of inputting a field (r row and c column), evaluating if it contains the text "(SMT)", and then returning true or false to the custom column, "Flag 2 Test" based on if it finds that text or not. Am I overthinking/over-engineering it?

[Syndicate_Admin]

Hi, @Gabe_V do you really need a custom function? Why not simply 

#"Added Custom" = Table.AddColumn(#"Flag 2b", "Flag 2 Test", each Text.Contains([DescriptionTypeValue], "(SMT)"))

? 

[Syndicate_Admin]

Hi @Gabe_V ,
yes, you would have to add a check if the table is empty to return a boolean here:

let
#"Output Test" = (r,c) =>
if Table.RowCount(Table.SelectRows(
Table.FromRecords(r),
each c = Text.Contains(Record.Field(r, c),"(SMT)")
))>0 then true else false
in
#"Output Test"

Not completely sure, but maybe this would also work? (as there is only 1 record to examine):

Text.Contains(Record.Field(r, c),"(SMT)")

[Syndicate_Admin]

Hi @AlienSx,

That's a good question, and one that I knew would probably be asked eventually. Its mostly because this is a bare-bones version of the ultimate function, which will eventuall need to a) perform much more complex "if then" logic, and b) be able to take values from different tables' fields and compare them against each other, etc. 

[Syndicate_Admin]

Hi @ImkeF, Unfortunately, when I make those changes, I still get an error: "We cannot convert a value of type List to type Record". I thought "ok, I'll just take the curly braces off the function call so its not a list anymore". However, that produced the error: "We cannot convert the value null to type Text"

[Syndicate_Admin]

Hi @Gabe_V ,
I cannot follow which approach you are actually taking now.
Could you please post the full code you are currently using?

[Syndicate_Admin]

Hi @ImkeF,

Sorry about that. Here's what I'm currently using:

Function:

let
  #"Output Test" = (r,c) =>
if Table.RowCount(Table.SelectRows(
      Table.FromRecords(r),   
      each c = Text.Contains(Record.Field(r, c),"(SMT)")   
))>0 then "true" else "false"
  in
  #"Output Test"

The call I'm making is:

= Table.AddColumn(#"Flag 2b", "Flag 2 Test", each #"Output Check"({_},[DescriptionTypeValue]))

I should note that I also tried your other suggestion for the function, of:

let
  #"Output Test" = (r,c) =>
Text.Contains(Record.Field(r, c),"(SMT)")
  in
  #"Output Test"

But no luck there either.

[Syndicate_Admin]

Hi @Gabe_V ,
when you use the 2nd short function without the curly brackets in the function call:
What does the error message say then?