How to connect JSON with Power BI: Direct vs. Dataddo

What do we do when we suddenly find ourselves needing to use JSON to funnel your business data into dashboarding services like Power BI? If your data is in a JSON format, you can either connect an immutable file directly into Power BI, authorize Power BI to gather data from a URL, or use a third party data connection platform like Dataddo to create a stable, live connection to your data source.  

JSON to Power BI direct connector

Power BI offers a couple different ways for you to use JSON to connect your data sources: via a local .json file, or a web connection. 

To load a local .json file into Power BI, 

1. Click on Get Data from the Power BI home page and choose Files > Local File from the connector selection.

3. You’ll then need to enter additional information for Power BI to access and read the file, such as the local path to the JSON file, an on-premises data gateway, and your authentication credentials for password-secured servers. 

(image source: docs.microsoft.com)

4. This process will launch the Power Query Editor, where you can transform the data if needed. 

To load a .json file from the web (usually a URL), 

(image source: docs.microsoft.com)

3. Follow additional credential prompts until you reach the Query Editor for transformation. 
(Note: in order to import a file from the web, you can also use the steps for loading a local file, but enter a web address into your local file browser instead of a file name.) 

JSON to Power BI using Dataddo

Dataddo is a data management platform that gives you the option to create custom data source connections to any API that returns a .json file using the “universal connector.” Data from your source is funneled into Dataddo and transformed into a 2D table before being loaded to Power BI. To set it up requires some technical knowledge, but Dataddo’s support team is available 24/7 to walk you through it. 

1. From within your Dataddo control panel, click “New Source”...

.....and search for “JSON Universal.”

2. Input the URL for the API endpoint and provide authorization credentials, if necessary.

3. Define a short script to instruct the transformation pipeline on how to transform your data. This is necessary for Dataddo to be able to refashion your data into the metrics and dimensions you require. No need for concern in case this isn't your wheelhouse - Dataddo provides personalized support to take you through it. 

4. Choose an HTTP request method, and optional HTTP Headers and Body.

If you'd prefer, the Dataddo team will also create the connector on your behalf at your request! 

What makes Dataddo the better option? 

Dataddo’s universal connector is a great solution for anyone who needs to pull frequently-updating data from a web address API that returns a .json file (which is most of them). Though it involves adding another platform to your existing data architecture, using Dataddo to move your JSON data into Power BI has a plethora of benefits, including: 

• A stronger data connection to minimize breakdowns in your Power BI dashboard. 
• Transformation parameters set in advance - data is automatically transformed in Dataddo before loading to Power BI; no extra fiddling on your part. 
• Power BI receives only the data that you need, and none that you don’t, for a cleaner and more useful table. 
• Real-time data connection with Power BI so that your dashboards are always up-to-date. 
• Scheduled data syncing at time intervals of your choice. 
• Managing multiple data sources being fed into Power BI and any of your other data destinations, all from one place. 

If you want to level-up your data management, try Dataddo free for 14 days! Start your free trial at the link below: