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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors