Microsoft Power BI allows us to connect with multiple data sources such as SQL Server, Excel, Text/Csv, XML, Pdf, and JSON and many more, here we will focus on how to import JSON data file to Power BI.
Lets see how to import data from JSON file to Power BI step by step.
As you can see below, there is one JSON file, located in system drive.
Copy Sample Data:
[
{
"Id": 1,
"Name": "Amit Singh",
"Code": "XCV7833-F",
"Dob": "17/09/1994"
},
{
"Id": 2,
"Name": "Gaurav Negi",
"Code": "NCV6841-F",
"Dob": "28/08/1989"
},
{
"Id": 3,
"Name": "Pawan Kumar",
"Code": "JSB7F43-F",
"Dob": "23/08/1987"
},
{
"Id": 4,
"Name": "Sonu Kumar",
"Code": "NNV7183-F",
"Dob": "31/12/1986"
},
{
"Id": 5,
"Name": "Vijay Singh",
"Code": "BCV5433-F",
"Dob": "30/05/1989"
},
{
"Id": 6,
"Name": "A Raj Singh",
"Code": "XGVG843-F",
"Dob": "19/06/1989"
},
{
"Id": 7,
"Name": "Akash Kumawat",
"Code": "ECBB343-F",
"Dob": "19/07/1981"
},
{
"Id": 8,
"Name": "Sujoy Rawat",
"Code": "HCB7813-F",
"Dob": "18/10/1994"
},
{
"Id": 9,
"Name": "Sumit Singh",
"Code": "DGT7823-F",
"Dob": "18/05/1992"
},
{
"Id": 10,
"Name": "Surya Agarwal",
"Code": "XBV7493-F",
"Dob": "11/02/1988"
}
]
You can see, this JSON file contains employee’s data and each employee has four properties as Id, Name, Code and Dob, and file contains records of 10 employees.
To import JSON file, Go to Get Data in Ribbon Bar, then select JSON and click on Connect button.
Once you click on Connect button, you will see the Power Query Editor window opens.
There you can see the List link of 10 records in Power Query Editor preview window.
Now, when you click on any record, you will see the details such as Id, Name, Code and Dob of one employee as shown below.
Lets click on the first record in List link, and you can it displays the details of that employee: Id, Name, Code, and Dob.
Now to going back to previous window, just click on X Navigation, or you can say remove the Navigation step from Applied Steps.
After that, we will convert this list into table. For this just click on To Table icon in Covert tab.
Once you click on To Table, you will see a To Table dialog box opens, here you need to select if any delimiters exist and specify how to handle extra columns else you can go with default selected values, and click on OK button.
Once you click on OK button, you will see the list is converted into a table with one column that is column1 which contains one record per employee, and each record contains 4 values.
Now click on Split column icon at the top right corner of the column1 as shown below.
Here you can uncheck the column that you do not want to keep in table.
Also check and uncheck the use original name of column as prefix option, If you check this option then column name will be created as Column1.Id, Column1.Name, Column1.Code, and Column1.Dob.
If you uncheck this option then column name will be created with original name such as Id, Name, Code, and Dob, here we uncheck this option.
After that click OK button.
Once you click on Ok button, you can see JSON data is converted into a table as shown below.
You can see the table contains four column with same name and data which is correspond to the properties and it’s values in JSON files.
Now after that, do not forget to save you change, click on Close & Apply button.
Once you click, change have been applied to your query.
Once it is done, you will see a table name Employee is created that you can see in Fields pane.
So far you have successfully imported JSON file data into Power BI.
If want to change the data type of any column then you can do this in Power Query Editor.
As you can see the data type of Dob column is showing in text type as there is no calendar icon showing in the starting of date column also there is no expand icon showing to see the date hierarchy for this column.
Lets change the data type of date column from text to date type.
Go to Power Query Editor, in Transform tab > Select the entire Dob column from preview window >
Expand the Data Type drop down and select Date.
Once you done this, data type of date column is converted into date from text.
Similarly, you can also change the data type of other columns.
After that click on Close & Apply to save your changes.
Now you can see, Calendar icon appears in the starting of Dob column, also you can now expand this column to see the date hierarchy.
Also Read..
Connect Power BI to SQL Server
Load Data From Multiple DataSource
Import text using examples feature Power BI
Pivot columns – turn rows into columns
Power BI Connectivity with AZURE BLOB Storage
