Calling a function and function name is in a data row

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