Import several JSON files whose the list is retrieved from a SQL query

[Anonymous]

Hi,

I try to import several JSON files, whose the list is retrieved from a SQL query.

The SQL query is really simple with 2 columns, some thing like that:

ItemID   |   JSONPath

1                //folderA/file.json

2                //folderB/file.json

3                //folderC/file.json

4                //folderD/file.json

let
Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT #(lf)#(tab)[ItemId],#(lf)#(tab)[JSONpath] #(lf)#(lf)FROM [Database].[table1]"])
in
Source

And for all these files I want to import the JSON content.

I need like a loop on the following function but by replacing "Source = Json.Document(File.Contents("//folderA/file.json"))," by the result of the sql query 

id   |   source   |   uri   |   web url

let
    Source = Json.Document(File.Contents("//folderA/file.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "source", "uri", "web url"}, {"id", "source", "uri", "web url"})
in
    #"Expanded Column1"

Then, I need to add the ItemID value coming from the SQL queries in order to have something like that:

ItemID   |   id   |   source   |   uri   |   web url

I found this blog https://www.dutchdatadude.com/loading-multiple-json-files-using-power-query/  but without success ...

Thank you for your help

[Anonymous · Accepted solution]

Hi arnaudm,

You can craete function and pass the folder name and file name parameter. and the you can add invoke this fucntion in your base query table. This will take all the folder and file name which is availabel on database and will load the data. Yes, you may need to do some data clearification to remove the error if any. Below are the sample function code

(FolderName as text,FileName as text) as table=>
let
Source = try Json.Document(Web.Contents("//"&FolderName&"/"&FileName&"")) otherwise error "Error in File Path ",
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Value", "Date"}, {"Value", "Date"})
in
#"Expanded Column1"

Hope this will help you.

[Anonymous]

Thank you, it works perfectly !