Forum Discussion

primolee's avatar
primolee
Helper V
6 years ago
Solved

Calling a function and function name is in a data row

Hello everyone,

 

Sorry for the repost, I think this is more Power Query related so I am posting here again.

 

I would like to add a new column which calls pre-made functions, and the name of functions are in a column.

 

I used Expression.Evaluate + Record.Combine to accomplish so previously.  However, as #shared only works in Power Query but does not work in Power BI, I need other solutions.

 

First, turn the functions that I need into a list then use that list.

 

var_Shared = #shared,
ProcessorFunctionNames = List.Select(Record.FieldNames(var_Shared),each Text.EndsWith(_,"Processor")),
Result = List.Transform(ProcessorFunctionNames, each _ & " = " & _ & ","),

 

Original expression:

Expression.Evaluate( _[Processor] & "( _[Folder Path] & _[Name] , _[Publisher] )", Record.Combine({[_=_],#shared}))

 

New expression:

Expression.Evaluate( _[Processor] & "( _[Folder Path] & _[Name] , _[Publisher] )", Record.Combine({[_=_],Result}))

 

However, I am getting an expression error "Cannot turn values of List type into Record type.

 

 

Could someone please help me with the correct M expression?

 

Thank you so much for the time and help.

Need Help
  • artemus's avatar
    artemus
    6 years ago

    I think I understand what your trying to do now... Don't use Expression.Evaluate, it will just make your life harder. Instead use something like:

    Record.Field(Output, [Processor])([Folder Path] & [Name], [Publisher])
  • artemus's avatar
    artemus
    6 years ago

    Oh I see what is missing...

     

    Change:

     

     

     GoogleGSMDailyProcessor = (fileName as text, publisherName as text) => GoogleGSMDailyProcessor,

     

     

    to

     

     

     GoogleGSMDailyProcessor = (fileName as text, publisherName as text) => GoogleGSMDailyProcessor(fileName, publisherName),

     

     

    Also, if you hit the firewall issue in the online service, go to edit credentialls (for the dataset object, not the report itself) and set "Privacy level setting for this data source" to None

     

18 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi primolee ,

     

    Well, a straight answer to your question is that Result in your code is a List and the second argument of Expression.Evaluate() should be a Record. You can use Record.FromList() for conversion.

     

    However, the problem, I think, is a bit deeper. If you look at the #shared as an example, this is a record where each field has the following template: DelegateName = Function. It is kind of accidental and a matter of convenience that the delegate and the function are called the same, it does not have to be so. For instance, these two lines of code do exactly the same:

     

    Sum1 = Expression.Evaluate("List.Sum(SomeList)", [List.Sum= List.Sum]),
Sum2 = Expression.Evaluate("Sum(SomeList)", [Sum= List.Sum])

     

    What you seem trying to create is a Record where fields look like Text=Text. This is not going to work for Expression.Evaluate().

     

    You may need to use Field.SelectFields as in the code below:

     

    let
    var_Shared = #shared,
    ProcessorFunctionNames = List.Select(Record.FieldNames(var_Shared),each Text.EndsWith(_,"Contains")),
    Result = Record.SelectFields(var_Shared, ProcessorFunctionNames),
    Output = Expression.Evaluate("Text.Contains(""asd"", ""a"")", Result)
in
    Output

     

     

    This code will work in Power Query and Power BI Desktop, but is not suitable for Power BI Online as it does not like #shared and will not refresh. To make it work for PBI Online you need to list all the functions you are referring to in the Expression.Evaluate manually:

     

    let
     Output = Expression.Evaluate("Text.Contains(""asd"", ""a"")", [Text.Contains=Text.Contains])
in
    Output

     

     

    Kind regards,

    JB

    • primolee's avatar
      primolee
      Helper V
      6 years ago

      Hello jborro,

       

      Thank you so much for your reply.  The reason of doing this is in this post.

      https://community.powerbi.com/t5/Desktop/Data-won-t-show-in-Power-BI/m-p/866163#M415474

       

      I don't know why but mine is not working in Power BI Desktop, either.  Once I get to Expression.Evaluate, there won't be any data shown in Power BI Desktop.  All columns are there but there won't be any data.  However, all data are there in Power Query.

       

      In worst case, I will simply refer manually as you said...

      #"Invoked Custom Function" = Table.AddColumn(#"Check If Publisher Exists", "Processed Tables", each Expression.Evaluate( _[Processor] & "( _[Folder Path] & _[Name] , _[Publisher] )", [GoogleGSMDailyProcessor=GoogleGSMDailyProcessor,GoogleGSMHourProcessor=GoogleGSMHourProcessor,GoogleGDNDailyProcessor=GoogleGDNDailyProcessor,GoogleGDNAdGroupProcessor=GoogleGDNAdGroupProcessor,GoogleGDNSizeProcessor=GoogleGDNSizeProcessor])),

       

      But Power Query shows an error saying that #"Invoked Custom Function" is refering to other queries therefore cannot directly access data source.

       

      So far I don't need to use Power BI Service online, I am just trying to make Desktop version work.  Could you please help me and see what might be the problem?

       

      Thank you so so much for the reply.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi primolee ,

         

        Icey is right on #2, this is for when you need referencing to a current row. My example would look something like:

         

        let
    var_Shared = #shared,
    ProcessorFunctionNames = List.Select(Record.FieldNames(var_Shared),each Text.EndsWith(_,"Contains")),
    Result = Record.SelectFields(var_Shared, ProcessorFunctionNames),
    Output = Expression.Evaluate("_[Function]", Record.Combine({[_=_], Result})
in
    Output

         

        In your post, you did not add [_=_] to the list of references. This should work:

        #"Invoked Custom Function" = Table.AddColumn(#"Check If Publisher Exists", "Processed Tables", each Expression.Evaluate( _[Processor] & "( _[Folder Path] & _[Name] , _[Publisher] )", [_=_, GoogleGSMDailyProcessor=GoogleGSMDailyProcessor,GoogleGSMHourProcessor=GoogleGSMHourProcessor,GoogleGDNDailyProcessor=GoogleGDNDailyProcessor,GoogleGDNAdGroupProcessor=GoogleGDNAdGroupProcessor,GoogleGDNSizeProcessor=GoogleGDNSizeProcessor])),

         

         Kind regards,

        JB