Forum Discussion
PQ: Invoking custom function suddenly stopped working
I have a query that has been running weekly for several months and that suddenly has stopped working.
This part of the data model basically consists of getting a file(/sheet) list from Excel files in a folder, and invoking a custom function on these. Now suddenly the invoking results in the following error:
Formula.Firewall: Query 'kPeriods' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
The "calling" query is a simple table including field name "File Name" and "Sheet Name", respectively. They are used as parameters when invoking the below query):
("PlannedSpendDir" is a working parameter containing the full folder path)
(fName, sName) =>
let
Source = Excel.Workbook(File.Contents(PlannedSpendDir & fName), null, true),
_Sheet = Source{[Item=sName,Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(_Sheet,1),
#"Filled Up" = Table.FillUp(#"Removed Top Rows",{"Column1", "Column2", "Column3", "Column4"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Column6] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column5", "rng"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"rng", "Country", "Track", "Channel", "Format"}, "Attribute", "Value"),
#"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "MonthNameShort"}}),
#"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[rng]), "rng", "Value")
in
#"Pivoted Column"
Any help will be appreciated !
Hi KennIpsen,
Based on my test, I cannot reproduce your issue here following your steps. Here is my code for your reference. Could you please share your excel file to me if possible? Also you can check the blog to have a try of that way.
(fName, sName) => let Source = Excel.Workbook(File.Contents(fName), null, true), Sheet1_Sheet = Source{[Item=sName,Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"cat", type text}, {"Sales", Int64.Type}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",1), #"Filled Up" = Table.FillUp(#"Removed Top Rows",{"Date"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([cat] <> "VS")), #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"1/2/2018", "1"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"1"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "2"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[#"2"]), "2", "1", List.Count) in #"Pivoted Column"Regards,
Frank
2 Replies
Hi KennIpsen,
Based on my test, I cannot reproduce your issue here following your steps. Here is my code for your reference. Could you please share your excel file to me if possible? Also you can check the blog to have a try of that way.
(fName, sName) => let Source = Excel.Workbook(File.Contents(fName), null, true), Sheet1_Sheet = Source{[Item=sName,Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"cat", type text}, {"Sales", Int64.Type}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",1), #"Filled Up" = Table.FillUp(#"Removed Top Rows",{"Date"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([cat] <> "VS")), #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers1",{{"1/2/2018", "1"}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"1"}, "Attribute", "Value"), #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "2"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns1", List.Distinct(#"Renamed Columns1"[#"2"]), "2", "1", List.Count) in #"Pivoted Column"Regards,
Frank
Thansk for the reply, i found out that it wasn't the code causing the problem, but the excel sheet.
Thanks for confirming this to me though.
There was a bad reference within the datasourcebest regards
Kenn
