Receiving errors when trying to pass and evaluate fields between functions

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?

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"

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.

Hi ImkeF,

When I use the shorter function and don't use the curly braces in the function call, I get the following errors:

If the [DescriptionTypeValue] field is null, then I get: "We cannot convert the value null to type Text"

If the [DescriptionTypeValue] field is another value (even if the value is the "(SMT)" that I'm looking for, as used in the example here), I get "The field '(SMT)' of the record wasn't found.

It looks like its looking for the value as the field name...?

Hi ImkeF,

Here's a screenshot of the table (duplicate rows and irrelevant columns removed):

There's really nothing more to it. Its a pretty simple data table at the moment.

I hope this helps!

Cheers,

Gabe

Hi ImkeF, 

To quote the response I gave to a very similar question posed by 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. "

I understand that I could very easily do exactly as suggested from within the referenced table, but I want to be able to make these kinds of checks across tables. This is what I hoped would be a very simple preliminary step to do that. I am getting the impression now that this is going to be a VERY complex task. I really didn't think it would be.

Hi ImkeF,

I'm sorry its not a great use case, but could I instead ask a general question: In general terms, is it possible to evaluate a specific field in a query by an external function, or am I just completely barking up the wrong tree? I just thought it would be really great if I had a function that I could re-use over and over again and be accessed from a wide variety of other queries. I mean, if I wanted to compare a field in query A to a field in query B and return a result to query C, wouldn't I need an external function to do that?

Anyway, thank you for trying. I really do appreciate the effort. 🙂

Cheers,

Gabe