How to import JSON and CSV data into Power BI

This tutorial shows how to connect JSON and CSV data from an API (Application Programming Interface) to Power BI using Windsor.ai.

 

What is Power BI and what are its features

Microsoft Power BI is a suite of business intelligence tools: software services, apps, and data connectors.

 

JSON

JSON stands for JavaScript Object Notation, a lightweight, text-based, open-standard data format.

 

A comma-separated values (CSV) file

A CSV file is a delimited text file that uses a comma to separate values. Each line is a data record, and each record consists of one or more fields separated by commas. A CSV file typically stores tabular data (numbers and text) as plain text, with each line having the same number of fields.

 

How to connect JSON/CSV data from an API to Power BI

1. Select and authenticate the data source you want to connect through Windsor.ai.

2. Go to Select Destination and choose the file format you need.

Importing JSON data into Power BI

2.1. Click the JSON logo, as shown below.

2.2. In Power BI Desktop, click Get Data, then choose Web.

2.3. Paste the link in the box that appears.

Importing CSV data into Power BI

3.1. Download the CSV file, as shown below.

3.2. In Power BI Desktop, click Get Data, then choose CSV.

3.3. Select the downloaded file from your computer.

 

Wrapping up: How to connect JSON/CSV data from an API to Power BI

We support 9,000+ fields across 70 connectors. The whole list is here.

To pull connector data into a data frame, create your own Windsor.ai account (free forever plan, no credit card required), connect your data sources, and build your API URL. Here’s a sample:

https://connectors.windsor.ai/all?api_key=[your API key]&date_preset=last_7d&fields=source,medium,campaign,clicks,impressions,spend,date

This returns a JSON containing the fields source, medium, campaign, clicks, impressions, spend, and date for the past 7 days.

 

Similar Articles:
Known Issues and Limitations with Power BI

Accessing any fields from your marketing platforms for your data pipelines

What data sources can Power BI connect to?