Append Json Files from a list of dynamic URLS

[dmbd1904]

Is this possible? The structure of the Json files are consistent

[PhilipTreacy]

Hi @dmbd1904 

You can store the list of the URL's in a table, a column, a list, an Excel table, as the result of another query or whatever.  You can get all queries in one go with this code (in this example URLList stores the list of URL's)

Source = List.Transform( URLList, each Json.Document(Web.Contents(_))) 

Any transformations applied after this step will affect all received JSON so you can add more URL's as needed and won't need to change the query.

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[dmbd1904]

Hi, so I can create individual queries to pull the Json data for each URL. I'm just wondering if there's a way of doing this in one operation. The list of URLs will continue to increase and I was hoping to avoid the need for creating a new query and then appending it to the 'master' table each time

[v-jingzhang · Accepted solution]

Hi @dmbd1904 

Yes, you could do this in one operation. You could first connect to a Json file as an example file, transform it, and make it a custom function query in Advanced Editor like below. Use VarURL to replace its original URL string in Source.

(VarURL as text)=>
let
    Source = Json.Document(Web.Contents(VarURL)),
    #"Converted to Table" = Record.ToTable(Source)
in
    #"Converted to Table"

Then create a table that contains all URLs in a column. You could use Excel/CSV or any other file type to maintain and update the URLs.

In this URL table query, click Add Column > Invoke Custom Function, use above funtion query in Function query field and JsonURL column as VarURL field.

At last, expand the new table column and you will get data from all URLs.

Additionally, if you want to combine the data as in below format, you could pivot the columns in the example file query before making it a function query.

Here is a demo PBIX you could download. Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[dmbd1904]

Hi @v-jingzhang ust one problem. When I include the custom function in Dataflows I get the below message....

one or more entities references a dynamic data source

Do you know how to resolve?

Query2 =

(VarURL as text)=>
let
Source = Json.Document(Web.Contents(VarURL))
in
#"Source"

Query1 = 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rc5BboJAFADQu7joqoEUmQgmxmAtRlER1BY0xozwg+KMf5z5lOrpu+6+7wRvv++ciZTp27YB/Q3asZ4NQWEVKG2QCjTqYyXwxEXX1qBQ05GXXBFoS53VsMVyMLmwuxuHz6QeSQrxzZX8neU/8zbNrtlC3NgdvAl5VRFtvnbTrGC+8iu5hdBZyXjENy/YkGpoUBu8dQ6v/zBKF1evTWLzoMhPZliX0555wLq3EhQsQ0IObc7mqfshxsCc+BJ9nmaBm+wwqsd56QR/R4df", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t]),
#"Invoked Custom Function" = Table.AddColumn(Source, "Query2", each Query2([URL])),
#"Expanded Query2" = Table.ExpandListColumn(#"Invoked Custom Function", "Query2"),
#"Expanded Query1" = Table.ExpandRecordColumn(#"Expanded Query2", "Query2", {"fld_uid", "fld_nodeid", "fld_id", "fld_date_raised", "fld_originator", "fld_organisation", "fld_type", "fld_zone", "fld_level", "fld_apt_number", "fld_area", "fld_description", "fld_assigned_to_org", "fld_assigned_to", "fld_target_date", "fld_date_closed", "fld_timestamp", "fld_deletetime", "fld_phase", "fld_street", "fld_unit_no"}, {"fld_uid", "fld_nodeid", "fld_id", "fld_date_raised", "fld_originator", "fld_organisation", "fld_type", "fld_zone", "fld_level", "fld_apt_number", "fld_area", "fld_description", "fld_assigned_to_org", "fld_assigned_to", "fld_target_date", "fld_date_closed", "fld_timestamp", "fld_deletetime", "fld_phase", "fld_street", "fld_unit_no"})
in
#"Expanded Query1"

[v-jingzhang]

Hi @dmbd1904 , I also met this problem. The solution to it is in the following link. Please try Anonymous' solution. It worked well at my side.

Solved: Re: Dataflow: use of M function turns workspace in... - Microsoft Power BI Community

[dmbd1904]

Thank you all for the responses.  @v-jingzhang I managed to follow your approach and it's working, so thank you this is one of the best things I've learned in Power BI this year! 

[v-jingzhang]

@dmbd1904  Not sure. If the Jsons files are able to be imported into Power BI from these URLs, it is probably able to append them. Have you tried connecting to the Json files through URLs?

[amitchandak]

@dmbd1904 , Not very clear. But you can convert to tables and append

For json
https://www.youtube.com/watch?v=ipI6mrWLQKA
https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/
https://zappysys.com/blog/howto-import-json-rest-api-power-bi/?gclid=EAIaIQobChMI7Za92YSi6wIVFSQrCh0...

Append -https://radacad.com/append-vs-merge-in-power-bi-and-power-query