Forum Discussion
Import several JSON files whose the list is retrieved from a SQL query
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
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.
2 Replies
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.