Import JSON files from multiple URLs into Power BI Desktop and Dataflow

[v-lili6-msft]

Scenario:    

In Power BI Desktop, it is easy to import a single JSON file from the local drive through JSON connector or from the web through Web connector. It is also easy to import and combine multiple local JSON files through Folder connector. However, what should we do if we have multiple web URLs of JSON files that have the same structure and want to combine all the files?

A simple method is to use the custom function feature in Power Query. There is a great blog (Import Multiple json File in Power Query | Power Blogger BI) about this topic which was written nearly 5 years ago. It creates custom function manually mostly with M codes and formula. In this blog, I will not repeat that method but use Power Query’s user interface to realize the same goal. It is similar to the experience when we use Folder connector to combine files. It is friendly for users who don't have much experience in M language.

Sample Data: 

In this blog, I use two web JSON files (SampleJsonWith100Records.json, SampleJsonWith200Records.json) to explore the process.

Operations：

First, create a query to get all the URLs from where you save and update the URLs. Here I enter these two URLs manually and name it as JsonFiles.

Then, create a query with Web connector and connect to one of the URL. I will use this query as Transform Sample query. I apply some transform steps to the query and now it looks like below. This result is what I want for the sample file as well as all other JSON files.

Now, create a parameter JsonURL and enter the sample URL as current value. The parameter type is Text.

Open the Advanced Editor of Transform Sample query and replace its source URL with the parameter JsonURL.

Now it comes to the key step to create a custom function. Right-click on Transform Sample query and select Create function in the menu. Name the function as Transform JSON File. It contains the parameter JsonURL by default.

After creating the custom function, the queries will be divided into two groups like below. We need to invoke Transform JSON File function for all URLs.

Select JsonFiles query in Other Queries, click Add column > Invoke Custom Function. Name the new column as Content, invoke Transform JSON File function and select JsonFileURL column for JsonURL parameter.

Finally, expand the new Content column and all records from these URLs are loaded into the query. Click Close & Apply, then all records will be imported into the model. Don’t forget to uncheck Enable load for Transform Sample query, so that it will not be loaded into the model.

Extension:

Since we can use custom function to combine multiple JSON files from web URLs, can we realize this in Dataflow? The answer is Yes.

Go to Power BI Service and enter a workspace where you can create a dataflow. Create a new dataflow and select Define new entities, or edit a dataflow you already have and add new entities. And you enter the Power Query Online Editor.

Go back to Power Query Desktop Editor and copy JsonFiles query in Other Queries, then paste it in Power Query Online. You will see JsonFiles query and Transform JSON File function appear automatically in Power Query Online.

It seems work well, so I click Save & close to save the entity. However, after a while it fails and pops up a message saying “One or more entities references a dynamic data source.” How to deal with this?

I cannot tell why or how it occurs. It seems the cause is with the Web connector. It cannot accept a whole URL parameter in Power Query Online but can accept it if the URL has been broken into basic path and relative path. So below is the solution to this problem.

As there is no Invoke Custom Function option under Add column tab in Power Query Online, we need to apply bellow steps in Power Query Desktop first and copy the query to Power Query Online later.

After analyzing the URL structure, we find that only the id parameter is different. So we extract the id from the URLs into a new column JsonFileId and will use this for the parameter in custom function later.

Then open Transform JSON File function’s Advanced Editor and replace JsonURL with "https://docs.google.com/",[RelativePath="uc?export=download&id=" & jsonFileId] in Source, and also replace JsonURL parameter with jsonFileId parameter. RelativePath is a valid option in Web.Contents function.

When invoking the custom function, use the JsonFileId column in the custom function to add a new column Content. Then expand the Content column and we will get all the records.

The final step is to copy this JsonFiles query and paste it into Power Query Online. This time, the dataflow can be saved successfully and all the data will be imported into it.

Author: Jing Zhang

Reviewer: Kerry & Ula

Import JSON files from multiple URLs into Power BI Desktop and Dataflow.pbix