Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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])
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
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
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
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.
Hello jborro,
Sure, I will send you a private message with google drive link to the files.
Thank you so much!
Best regards,
David
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
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.
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
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...
If it is credential issue, it will be a whole new question then...
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 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.