Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
KevinLongeway
Regular 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

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.

View solution in original post

2 REPLIES 2
KevinLongeway
Regular Visitor

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors