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

+1 vote

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,220 points
14,034 views

1 answer to this question.

+1 vote

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

If you are interested in learning Power BI, check out Power BI Syllabus now!

answered Sep 24, 2018 by Hannah
• 18,520 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, 2019 in Power BI by Avantika
• 1,520 points

edited May 17, 2019 by Omkar 7,547 views
0 votes
1 answer

If there a way to have the current month by default in a KPI representing MoM% in Power BI

The PREVIOUSMONTH DAX function can be used ...READ MORE

answered Feb 16, 2022 in Power BI by CoolCoder
• 4,420 points
2,318 views
0 votes
1 answer

What is the best way to handle and display hierarchical data in Power BI?

Display of hierarchical data in Power BI ...READ MORE

answered Nov 5 in Power BI by pooja
• 6,670 points
57 views
0 votes
1 answer
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,130 points
1,342 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

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

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,739 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
• 18,520 points
1,511 views
+1 vote
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
• 52,350 points
1,658 views
+1 vote
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
• 18,520 points
723 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
• 18,520 points
1,147 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP