Forum Discussion
Invoked Custom Function to load folder full of JSON files based on function of loading 1 JSON
2 months ago I wrote the following script which works perfectly fine today as well, the sample code is from a table created after the error in the other Power BI instance.
let
Source = Folder.Files("S:\17. AIMSIO\Raw Data BI\tickets_raw"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from tickets_raw (8)", each #"Transform File from tickets_raw (8)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from tickets_raw (8)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from tickets_raw (8)", Table.ColumnNames(#"Transform File from tickets_raw (8)"(#"Sample File (11)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Value", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Source.Name", "FileName"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name", "Value"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Duplicates", "LoadFolder", each LoadFolder([FileName]))
in
#"Invoked Custom Function"
I am trying to change up this process to keep ticket data in folders by Quarter instead of entire year. I have changed the folder destination and try to use the following script but I am getting an error and cannot figure out what is different.
Error:
Formula.Firewall: Query 'Q2_18' (step 'Invoked Custom Function') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination
Scipt is:
let
Source = Folder.Files("S:\17. AIMSIO\Raw Data BI\Aimsio data\Q2_18"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Q2_18", each #"Transform File from Q2_18"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Q2_18"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Q2_18", Table.ColumnNames(#"Transform File from Q2_18"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Value", type any}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Source.Name", "FileName"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Name", "Value"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Duplicates", "Q2Load", each Q2Load([FileName]))
in
#"Invoked Custom Function"
The sample JSON is in the folder being queried for both examples. Any thoughts on what I am missing between the two?
It looks like I found a reason for the error. On my desktop I have two ways to access Power BI desktop and the link on my Windows shortcuts was taking me to a version from Aug and my other file was built with a more recent version. Once I updated the link and was going to the current version, all the scripts were working again.
2 Replies
I forgot to add the script from the Custom Invoke function:
(file as text) =>let
Source = Json.Document(File.Contents("S:\17. AIMSIO\Raw Data BI\Aimsio data\Q2_18\"&(file))),
#"Converted to Table" = Record.ToTable(Source),
#"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")
in
#"Pivoted Column"The mapped example JSON in the Source line was deleted and "&(file)" was inserted in its place.It looks like I found a reason for the error. On my desktop I have two ways to access Power BI desktop and the link on my Windows shortcuts was taking me to a version from Aug and my other file was built with a more recent version. Once I updated the link and was going to the current version, all the scripts were working again.