Forum Discussion
Calling a function and function name is in a data row
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])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
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
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.
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 OutputIn 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
Hello jborro,
I added _=_ in the beginning of the square bracket, but same error still occurs: Step #"Invoked Custom Function" is referring to other query or step, therefore cannot directly access data source.
Any more advice?
Moreover, it will be very troublesome if number of my custom functions keeps on growing. Is there any way of making a dynamic record of my functions easier without using #shared? I can manually maintain a list of function names somewhere such as excel.
I am so sorry for asking so much, greatly appreciated. m(_ _)m
- Hi,
This is a commonly known issue. Usually, it can be cured by brunching you code. Right-click on the step in the editor and select Extract previous. In you scenario you may need to brunch from a couple of steps before #Invoke. At the point where you define the var_Shared. The main idea is to separate the source of external data from processing with internal data.
As to the list in Excel - this is a good idea. But just as simple you can create a table directly in PBI vis Enter Data. If you keep your functions separately - you always can count-reconcile it.
Another alternative is defining a single function which is called in the main code. You pass the actual name to this function and it decides through if-then or table-filter which function to call. In this case evaluation o ly need to know the name-reference to this wrapper function. It does not need to know that other function even exist.
Kind regards
JB