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.
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,
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.
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.)
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.
.....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!
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:
If you want to level-up your data management, try Dataddo free for 14 days! Start your free trial at the link below:
Connect JSON to Power BI Just a few quick steps to get all your JSON data to your favorite BI tool. |
Continue reading the Dataddo blog for more Power BI tips, tricks, and resources!