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
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) => ...
]
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,
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...
- 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.
- 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...
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.
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 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
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
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
- 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 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.