cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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.

View solution in original post

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.
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.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 2,116 guests
Please welcome our newest community members: