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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Gabe_V
Helper I
Helper I

Receiving errors when trying to pass and evaluate fields between functions

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, named Output Check, 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!

17 REPLIES 17
ImkeF
Super User
Super User

Hi @Gabe_V ,
in general, this sounds absolutely doable.
Just that I don't get the context/filters that you want to apply here: Where shall the information come from of which fields (row- and column index) from each table you want to drag.
If you would care to provide a sample file with a description of the desired output we could be there in a breeze I guess.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

Hi @Gabe_V ,
sorry that I still don't get your requirement / use case.
Usually, even complex things are possible in PQ, but I don't understand what you want to achieve here.
So without a proper file with sample data, I am afraid that I cannot help you any further here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

ImkeF
Super User
Super User

Hi @Gabe_V ,
I don't understand the task that you want to achieve here. 
One could check for the occurrance like so:

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

How would that differ from your desired result?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

ImkeF
Super User
Super User

Hi @Gabe_V ,
could you please paste same sample data or a link with some sample data?
I have the feeling that I am missing a crucial part here.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF,

 

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

Gabe_V_0-1693253820903.png

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

I hope this helps!

 

Cheers,

Gabe

ImkeF
Super User
Super User

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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"

ImkeF
Super User
Super User

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

 


Wrapping the _ into curly brackets: {_} will put the current record into a list.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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, @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)"))

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. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.