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
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
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.
- 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
JBWhat 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) => ... ]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
Hello jborro,
Sure, I will send you a private message with google drive link to the files.
Thank you so much!
Best regards,
David