Power BI Tutorial | Working with JSON data

In this Power BI Tutorial, Adam shows how you can easily work with JSON data within Power BI. Working with JSON data in Power BI Desktop is one of the best-kept secrets of Power BI.

LET'S CONNECT!

Guy in a Cube
-- Snapchat - guyinacube

***Gear***

[GuyInACube]

David Eldersveld commented on Twitter, and Geoff Richardson commented below, regarding ragged JSON, like with AuditData in the example above. I forgot to call this out in the video. If the structure changes, you could end up with bad results. Imke Feldmann pointed out a possible solution:

You could replace the hardcoded field names by a formula that expands all of them automatically:

[EricWahner]

I would love to see a video about connecting to a Web Api (web) resource and show how you can convert a huge JSON response to a I am guessing a series of tables.

[hindia.m]

I was not logged in when I first desperately landed on your video. I absolutely loved everything about it that I had to login, subscribe and like AND comment. Thank you!!

[oliverocket]

Thanks man!! I was breaking my head for two days! You are a legend!

[BroadtreeSolutions]

Came across a json data source from a customer and was able to use this so thank you! it's sweet.

[machomanrandysavage]

Is there a way to consume json formatted date into power by via just the URL? Meaning, Like Odata feed, provide a URL and expose data to power bi which is in jason format? Cant seem to make this happen.

[Pseudo___]

can you go in reverse easily? like if got a couple tables with list/records and properties i want transform into a json format

[aichayahya]

Hi thanks for this tuto, i have two seperate object json each object has his attribues. But there is a one common data between them to create a link between them . So how can i create a view to display the data of both json object on a one json object by selecting the right one.

[arg]

Great tutorial, helped me a lot ! Only problem I have now is that the REST API that is feeding the JSON only returns 100 rows at a time, you have to use a revised URL to get the next 100 rows (e.g. &next=100) and so on. How can I get PowerBI to keep getting the next 100 rows until none left ?

[avilajalfredo]

Hi Gyinacube! if you are free to decided, What do you prefer to fill data in a report? Direct connetion with azure database or use a Rest API?

[okeomslax]

You are a life saver man wow that was great

[adamsabourin]

I dont see an "expand" button on my JSON column header that appeared when you did it. Is that somewhere else now?

[keithchai]

I hit issues when I am parsing a list of json records in power bi, it is loading very long for >20 minutes of waiting time. Is this the expected outcome from the column expansion? It is consisting of over 700k records in the json list.

[danielnascimento]

Hey !! I'm Brazilian but even I could understand you pretty well, and regards connection between Plataforms I would like to know if if it's possible to link Firebase and Power BI, and I think it is since they export Json files! But my intention is connecting them ! it would be awesome if so !! Thank you mister

[NCITPro]

Great tip! If you interact with APIs, you are sure to need a way to parse JSON responses.

[sherinjose]

Thank you..but i have a connection with direct query and this method is actually restrictred for direct query but works weel for import data.how can i use json data from table and use in power bi if my connection is direct query ??

[shammaalderei]

Hi very helpful video 👍
for my case i would like to use life data from world Bank for example how can i make it ? i would like when i do the refresh the power bi page it is having the updated one

[drspoerl]

Great video. My challenge is that my JSON files have list within unique records. Any help?

[Ikilledthebanks]

Can I reliably profile with this if the sample is large enough?

[prasanjitswain]

is there a way we can automate the JSON file INTO bar/graphs in POwer BI