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

Question

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

Answer 1

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!

Comments

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.