Is there a way to convert JSON data to readable table in power bi

0 votes

Is it possible for power bi to read JSON data? I’ve been trying from a really long time to find this, but no luck as of now!

I want my JSON data to be converted into a readable table.

I'm retrieving a JSON payload from my website and when I try to import, it shows the data like this:

Record
Record
Record
Record

Thank you

Sep 24, 2018 in Power BI by lina
• 8,100 points
2,008 views

1 answer to this question.

0 votes

Follow these steps

step 1 - Click on get data

Step 2 - Check the JSON file

step 3 - upload the file from your system

On clicking on Open, the JSON data will be loaded into the Query Editor window. The loaded data will be present as a single row which represents the JSON root node. Click on ‘Record’ to drill down to the list of records.

step 4 - Click on list and drill down to individual records

step 5 - Now that we have a record for each item in the JSON File. We can now convert this to the Power BI table by selecting the ‘To Table’ option.

step 6 - It will give us the option to select which column should be part of the table. Ensure that you uncheck the check box ‘Use original column name as prefix’. If it is checked, the generated column will have the naming convention ‘Column1.NewColumnName’.

step 7 - Click on ok and apply and save

answered Sep 24, 2018 by Hannah
• 14,090 points
after doing all the steps also , instead of readable data I am getting Record, only
Hey, Have you drilled your data down to the bottom most level?
I'm having this same issue.  I can correctly parse and transpose a single record but am unable to copy those steps to the entire file.

hey, @kelly is your JSON data indented and organized properly.

When you click on record, the data will drill down.

[please add a link to your JSON data if it's not confidential or a dummy JSON file, it might help.]

Once you load the JSON file, only record columns will be shown, then click on record, and expand the data to tables.

I used JSON from here as example 2, https://support.oneskyapp.com/hc/en-us/articles/208047697-JSON-sample-files

Click on Record, then click on into table on the top left corner to add each record into the table.

Then click on the expand button next to fields to show all fields within each record.

Related Questions In Power BI

0 votes
1 answer

How to sum a data table in Power BI?

It's a good practice if you avoid ...READ MORE

answered May 17 in Power BI by Avantika
• 1,500 points

edited May 17 by Omkar 66 views
0 votes
1 answer

What are the components I need to excel to become a pro in power BI

Having knowledge about these topics is a must.  Power ...READ MORE

answered Oct 8, 2018 in Power BI by Kalgi
• 39,330 points
40 views
0 votes
1 answer

How to combine your data or tables in Power BI?

Power BI includes Query Editor which is ...READ MORE

answered Feb 27 in Power BI by Phalguni
• 1,010 points
50 views
0 votes
1 answer

How to create final table based on Joins of two tables in power BI?

Hi Nithin, It is easy and simple. 1. Go ...READ MORE

answered Mar 1 in Power BI by Cherukuri
• 21,470 points
102 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,060 points
71 views
0 votes
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 21, 2018 in Power BI by nirvana
• 3,060 points
554 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 14,090 points
103 views
0 votes
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 39,330 points
95 views
0 votes
1 answer

Is it possible to manage limits while using dedicated capacity power bi embedded app service?

These limits are not related to using ...READ MORE

answered Sep 24, 2018 in Power BI by Hannah
• 14,090 points
58 views
0 votes
1 answer

How do I know who in my organization has a Power BI account?

You can view the Azure Active Directory ...READ MORE

answered Oct 15, 2018 in Power BI by Hannah
• 14,090 points
34 views