Calling a function and function name is in a data row

[primolee]

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.

[artemus · Accepted solution]

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

[Anonymous]

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]

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]

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

[primolee]

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

[Anonymous]

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

[primolee]

Hello jborro,

I just did what you said, Extract Previous at "var_Shared = #shared" step.  However, same error still occurs at #Invoke step.

Formula.Firewall: Query 'GenerateOutput' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I defined var_Shared in the very beginning of my advance editor, so when I did extract previous, new query is very short.  And my #Invoke step is exactly the same as what you wrote previously.

I just added a table via Enter Data.  And I want to manually generate a record like the screenshot below.  Is there a way to turn this table into such record?  Screenshot is done with #shared, and I am trying to avoid using #shared.

Image 1, table I manually added:

Image 2, hoping to turn the above table to this...

I think there is something to do with name=name, but I can't figure out where to put it... (T_T)

Thank you so much in advance.

[Anonymous]

Hi,

Sorry, I think, this may be the case where you can only sort it from your end :(.

Do you want to share the entire code you have - remove all sensitive data like url or username and/or send me in private.
I will see if I can suggest anything.

Unfortunately I do nor know any simple solution to your second question- re function table - but I will send you a code which I use in similar circumstances.

Kind regards
JB

[primolee]

Hello jborro,

Sure, I will send you a private message with google drive link to the files.

Thank you so much!

Best regards,

David

[artemus]

What your doing won't work. If you used #shared it won't be able to use any of the other queries you defined when you load your query into the model. It will work fine in preview, but a query can only refer to other queries directly by name, and not using #shared, #sections, or Expression.Evaluate.

Instead you will need to define all your functions into a single record query like:

[
   function1 = () => ...,
   function2 = (arg) => ...
]

[primolee]

Hello Artemus,

Thank you so much for your reply.  I think this is why my data won't show in Power BI Desktop but will show in Power Query.

Please see the right-side red box, I firstly generate the function names that I want in Processor column, then I will use Expression.Evaluate, Record.Combine and #shared to add a new column with corresponding functions.  In other words, processor names will be dynamic.  At the same time, I need to pass the absolute URL of file location which is also dynamically generated with folder path and file name.

I did what you said, and the code is as follows:

Output =
    [
        GoogleGSMDailyProcessor = (fileName as text, publisherName as text) => GoogleGSMDailyProcessor,
        GoogleGSMHourProcessor = (fileName as text, publisherName as text) => GoogleGSMHourProcessor,
        GoogleGDNDailyProcessor = (fileName as text, publisherName as text) => GoogleGDNDailyProcessor,
        GoogleGDNAdGroupProcessor = (fileName as text, publisherName as text) => GoogleGDNAdGroupProcessor,
        GoogleGDNSizeProcessor = (fileName as text, publisherName as text) => GoogleGDNSizeProcessor
    ],
.....
.....
.....
.....
#"Invoke Custom Functions" = Table.AddColumn(#"Check If Publisher Exists", "Processed Tables", each Expression.Evaluate( _[Processor] & "( _[Folder Path] & _[Name] , _[Publisher] )", Record.Combine({[_=_],Output})))

However, I am still getting this error:

Formula.Firewall: Query 'GenerateOutput' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

I also tried Extract Previous which jborro mentioned, but same error occurs... I did Extract Previous at var_foldersPath step, maybe I am not extracting from the correct step.

Here is the relative codes of this query.  I am getting excel/csv files from MS Teams.  I defined folderPath and record at the very beginning.

/*
 * MAIN SUMMARY GENERATOR CODE
 */
let
    Output =
    [
        GoogleGSMDailyProcessor = (fileName as text, publisherName as text) => GoogleGSMDailyProcessor,
        GoogleGSMHourProcessor = (fileName as text, publisherName as text) => GoogleGSMHourProcessor,
        GoogleGDNDailyProcessor = (fileName as text, publisherName as text) => GoogleGDNDailyProcessor,
        GoogleGDNAdGroupProcessor = (fileName as text, publisherName as text) => GoogleGDNAdGroupProcessor,
        GoogleGDNSizeProcessor = (fileName as text, publisherName as text) => GoogleGDNSizeProcessor
    ],
    var_foldersPath = #table({"path"},{{foldersPath}}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(var_foldersPath, {{"path", each Text.BeforeDelimiter(_, "/", {1, RelativePosition.FromEnd}), type text}}),
    #"Site List" = SharePoint.Contents(#"Extracted Text Before Delimiter"[path]{0}, [ApiVersion = 15]),
    #"Shared Documents" = #"Site List"{[Name="Shared Documents"]}[Content],
    #"General" = #"Shared Documents"{[Name="General"]}[Content],
    #"Extracted Text After Delimiter" = Table.TransformColumns(General, {{"Name", each Text.AfterDelimiter(_, "_"), type text}}),

    //Access Client
    #"Client" = Table.SelectRows(#"Extracted Text After Delimiter", each [Name] = ClientFilter),
    #"Removed Columns" = Table.RemoveColumns(Client,{"Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Name", "Client"}}),
    #"Expanded Client" = Table.ExpandTableColumn(#"Renamed Columns1", "Content", {"Content", "Name", "Extension","Folder Path"}, {"Content.1", "Name.1","Extension.1", "Folder Path.1"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Client", each ([Extension.1] = "")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows2",{"Extension.1", "Folder Path.1"}),
    #"Extracted Text After Delimiter1" = Table.TransformColumns(#"Removed Columns1", {{"Name.1", each Text.AfterDelimiter(_, "_"), type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Extracted Text After Delimiter1",{{"Name.1", "Publisher"}}),
    
    //Access Publisher
    #"Publisher" = Table.ExpandTableColumn(#"Renamed Columns2", "Content.1", {"Content", "Name", "Extension","Folder Path"}, {"Content.2", "Name.2","Extension.2", "Folder Path.2"}),
    #"Removed Columns2" = Table.RemoveColumns(Publisher,{"Extension.2", "Folder Path.2"}),
    #"Extracted Text After Delimiter2" = Table.TransformColumns(#"Removed Columns2", {{"Name.2", each Text.AfterDelimiter(_, "_"), type text}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Extracted Text After Delimiter2",{{"Name.2", "Campaign_Id"}}),
    #"Filtered Campaign" = if CampaignFilter = null then #"Renamed Columns3" else Table.SelectRows(#"Renamed Columns3", each [Campaign_Id] = CampaignFilter),
    #"Expanded Content.2" = Table.ExpandTableColumn(#"Filtered Campaign", "Content.2", {"Extension", "Folder Path", "Name"}, {"Extension", "Folder Path", "Name"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Content.2", each [Extension] <> null and [Extension] <> ""),
    已加入條件資料行 = Table.AddColumn(#"Filtered Rows1", "Channel", each if Text.Contains([Name], "GSM") then "GSM" else if Text.Contains([Name], "GDN") then "GDN" else if Text.Contains([Name], "Youtube") then "Youtube" else null),
    已加入條件資料行1 = Table.AddColumn(已加入條件資料行, "Type", each if Text.Contains([Name], "品質分數") then "Daily" else if Text.Contains([Name], "時段") then "Hour" else if Text.Contains([Name], "日") then "Daily" else if Text.Contains([Name], "Size") then "Size" else if Text.Contains([Name], "Ad Group") then "AdGroup" else null),
    已新增自訂 = Table.AddColumn(已加入條件資料行1, "Processor", each [Publisher]&[Channel]&[Type]&"Processor"),
    //Respective Processors to be invoked on each record, based on Publishers
    #"Added Custom Functions" = Table.AddColumn(已新增自訂, "Custom Function", each matchPublishers([Publisher]) ),
    #"Check If Publisher Exists" = Table.SelectRows(#"Added Custom Functions", each true),
    //Specially for Yahoo, commented out first
    //Call processor
    #"Invoked Custom Function" = 
        Table.AddColumn(#"Check If Publisher Exists", "Processed Tables", each Expression.Evaluate( _[Processor] & "( _[Folder Path] & _[Name] , _[Publisher] )", Record.Combine({[_=_],Output}))),

    已移除錯誤 = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Processed Tables"}),
    已篩選資料列 = Table.SelectRows(已移除錯誤, each not Text.Contains([Custom Function], "CustomProcessor")),
    //Clean up to present the final output table
    #"Filter Processed Table" = Table.SelectColumns(已篩選資料列,{"Client","Publisher","Campaign_Id", "Processed Tables"}),
    依分隔符號分割資料行 = Table.SplitColumn(#"Filter Processed Table", "Publisher", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Publisher", "ToBeDeleted"}),
    已移除資料行 = Table.RemoveColumns(依分隔符號分割資料行,{"ToBeDeleted"}),
    已取代值 = Table.ReplaceValue(已移除資料行,"EENO","8891",Replacer.ReplaceText,{"Publisher"}),
    #"已展開 Processed Tables" = Table.ExpandTableColumn(已取代值, "Processed Tables", {"Date", "Channel", "Category", "Placement", "Content", "Material Size", "Period", "DateStart", "DateEnd", "Ad Group", "Keyword", "TA", "Device", "Hour", "Impressions", "Clicks", "Link Clicks", "Page Views", "Video Views", "Video View 3s", "Video View 5s", "Video View 10s", "Video watch to 25%", "Video watch to 50%", "Video watch to 75%", "Video watch to 100%", "Cost", "List Cost", "Retention", "URL", "Quality Score", "Average Ranking", "Reach", "Engagements", "Pagelikes", "Buying Type", "Key"}, {"Date", "Channel", "Category", "Placement", "Content", "Material Size", "Period", "DateStart", "DateEnd", "Ad Group", "Keyword", "TA", "Device", "Hour", "Impressions", "Clicks", "Link Clicks", "Page Views", "Video Views", "Video View 3s", "Video View 5s", "Video View 10s", "Video watch to 25%", "Video watch to 50%", "Video watch to 75%", "Video watch to 100%", "Cost", "List Cost", "Retention", "URL", "Quality Score", "Average Ranking", "Reach", "Engagements", "Pagelikes", "Buying Type", "Key"}),
    已變更類型 = Table.TransformColumnTypes(#"已展開 Processed Tables",{{"Client", type text}, {"Publisher", type text}, {"Campaign_Id", type text}, {"Date", type date}, {"Channel", type text}, {"Placement", type text}, {"Content", type text}, {"Period", type text}, {"DateStart", type date}, {"DateEnd", type date}, {"Ad Group", type text}, {"Keyword", type text}, {"TA", type text}, {"Device", type text}, {"Impressions", Int64.Type}, {"Clicks", Int64.Type}, {"Page Views", Int64.Type}, {"Video Views", Int64.Type}, {"Video View 3s", Int64.Type}, {"Video View 5s", Int64.Type}, {"Video View 10s", Int64.Type}, {"Video watch to 25%", Int64.Type}, {"Video watch to 50%", Int64.Type}, {"Video watch to 75%", Int64.Type}, {"Video watch to 100%", Int64.Type}, {"Cost", type number}, {"List Cost", type number}, {"Retention", Int64.Type}, {"URL", type text}, {"Quality Score", type number}, {"Average Ranking", type number}, {"Reach", Int64.Type}, {"Engagements", Int64.Type}, {"Category", type text}, {"Material Size", type text}, {"Hour", type text}, {"Link Clicks", Int64.Type}, {"Pagelikes", Int64.Type}, {"Buying Type", type text}, {"Key", type text}}),
    #"Cleaned Text" = Table.TransformColumns(已變更類型,{{"Publisher", Text.Clean, type text}, {"Channel", Text.Clean, type text}, {"Placement", Text.Clean, type text}, {"TA", Text.Clean, type text}, {"Device", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Publisher", Text.Trim, type text}, {"Channel", Text.Trim, type text}, {"Placement", Text.Trim, type text}, {"TA", Text.Trim, type text}, {"Device", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Year", each Date.Year([Date])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Year", "Client", "Campaign_Id", "Date", "Publisher", "Channel", "Placement", "DateStart", "DateEnd", "TA", "Device", "Impressions", "Clicks", "Page Views", "Video Views","Video View 3s", "Video View 5s", "Video View 10s", "Video watch to 25%", "Video watch to 50%", "Video watch to 75%", "Video watch to 100%", "Cost", "List Cost", "Retention"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Year", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Publisher", Order.Ascending}})
in
    #"Sorted Rows"

Any idea of how to fix this?

Thank you so much.

Best regards,

David

[artemus]

You can turn off these checks in the privacy settings in the options menu, if you aren't worried about leaking data from one data source to another.

[primolee]

Hello Artemus,

I cannot believe how simple this issue can be solved...

I changed the global privacy and now data are correctly shown in Power BI Desktop as well!!!!!!  I cannot believe that I was stuck at this for nearly 2 weeks... 😆

Now the next question is to get on Power BI Service.  As everyone mentioned that #shared will not work in Power BI Service, I will still need to make my own function record.

Image 1: with #shared, new column will have the desired table

Image 2: with a manually-generated record, column value becomes "Function"

Following is my manually-created record "Output":

    Output =
    [
        GoogleGSMDailyProcessor = (fileName as text, publisherName as text) => GoogleGSMDailyProcessor,
        GoogleGSMHourProcessor = (fileName as text, publisherName as text) => GoogleGSMHourProcessor,
        GoogleGDNDailyProcessor = (fileName as text, publisherName as text) => GoogleGDNDailyProcessor,
        GoogleGDNAdGroupProcessor = (fileName as text, publisherName as text) => GoogleGDNAdGroupProcessor,
        GoogleGDNSizeProcessor = (fileName as text, publisherName as text) => GoogleGDNSizeProcessor
    ],

Could you please tell me how to use manually-created record and have the correct table value that I want?

I think I am almost there.  I am so grateful that both jborro and artemus have been such great help!  Thank you!

Best regards,

David

[primolee]

Hello,

I have just simplified my code and put everything in google drive.

https://drive.google.com/open?id=163K8dplI9xqc0cxYSkGFcdUfvD8LE1Ta 

I put TestData folder under c:\ and everything should work.

Funny thing I found...

1. In my original environment, I access .csv data source from MS Sharepoint.  Original pbix will have issue loading data into Power BI Desktop and no problem in preview in Power Query as Artemus mentioned.  However, in this newly created pbix which uses local data, all data is loaded into Power BI Desktop correctly even with #shared command.
2. In my original environment, using manually-created function record will cause an error mentioned previously "Formula.Firewall: Query 'GenerateOutput' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.".  But in this newly created pbix, there won't be any error, instead column value becomes "Function".

If it is credential issue, it will be a whole new question then...

[artemus · Accepted solution]

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

[primolee]

---

@artemus wrote:

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

---

Hello Artemus,

I just did what you said, and the result is the same as my previous reply.  Values of this new column becomes "Function" instead of "Table".

Last mile here now... 😣

Best regards,

David

[artemus · Accepted solution]

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

[primolee]

Hello Artemus,

GoogleGSMDailyProcessor = GoogleGSMDailyProcessor,

I actually figured it out also this afternoon by changing to the above, works as well. 🙂

Last step is to make this record dynamically as number of pre-made functions will keep growing and I don’t want to manually entering the above whenever a new function is made.

Thank you so much for your time and help!

Best regards,
David

[artemus]

That won't work, you can't refer to any other query dynamically unless you have already done so explicitly in the same query. When you load it into the Power Bi model, all other queries that are not referenced will not resolve. This is why you should have a single query with all function in a single master record, and any new ones need to be added there first.

In theory you could also load the definitions from github or VSTS, but your millage will vary.