cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KevinLongeway Frequent Visitor
Frequent Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
KevinLongeway Frequent Visitor
Frequent Visitor

Re: Invoked Custom Function to load folder full of JSON files based on function of loading 1 JSON

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 2
KevinLongeway Frequent Visitor
Frequent Visitor

Re: Invoked Custom Function to load folder full of JSON files based on function of loading 1 JSON

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.
Highlighted
KevinLongeway Frequent Visitor
Frequent Visitor

Re: Invoked Custom Function to load folder full of JSON files based on function of loading 1 JSON

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.