Convert JSON strings to tables in Power Query/ Power BI

In today's video I will show you how to convert JSON files in Power Query to a table easily, in basically 2 steps!!

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

ABOUT CURBAL:

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
► Twitter: @curbalen, @ruthpozuelo

#SUBSCRIBE #CURBAL

[Nopealope]

Great solution thank you! This saved me time since I was doing several steps to trim and split at delimiter before. (FYI - Fast forward to 5:00 for steps)

[excelisfun]

Right-click, Transform, Json, Record and Just Amazing!!!

[mukuleshshinde]

Thank you very much!! I was stuck on a dataset having json text from many days. This truly helped!

[aghonaim]

Thanks for the video but I don't know why did you jump right away to the JSON file at min 5:00 and you skipped all the steps you did to prepare the file ?! I missed what actually was the data source and the steps you did to prepare it to the JSON transformation!

[jenniferabell]

Hi, Ruth. I am trying to take a JSON.gz file, decompress it, and transform it using the JSON transform as you've shown. The parse does not complete and ends with the following error: "DataFormat.Error: We reached the end of the buffer."

Data = Binary.Decompress((Source), Compression.GZip),
Data1 = Lines.FromBinary(Data, 1, false, 1252),
#"Converted to Table" = Table.FromColumns({Data1}),
#"Parsed JSON" = to Table", {}, Json.Document), <----Error shows up on this step.

Any advice?
Thanks!

[muzammilkarajgi]

How can I integrate json link in powerbi which is daily refresh with another link

[abdulhakeemoladejo]

Thank you so much Ruth, as always, your videos are always very informative and educative.
This video has helped me solve a potentially big problem at work today.
Thank you once again.

[PranavN]

Thank you .. this saves so much time !!

[kasturi]

Thank you much Ruth for this video. As always your videos are very informative & useful.
I tried to follow your video for one of the json files in my Azure Blob Storage. I am trying to connect to one of the json files in my Azure blob storage. These files are getting data from the IoT Hub via Stream Analytics.
When I downloaded the json file & tried to connect Power BI desktop to it, it gives me following error <<Unable to Connect - We encountered an error while trying to connect. Details: "We found extra characters at the end of JSON input.">>
I cannot change anything in the json file since its automatically generated & stored in Blob Storage.
Have you come across this error before? Do you know what can I do to fix it?

[bc]

I need to import a JSON file from ServiceNow that contains many child records that I'd like to expand on import as well. I'm not sure if it's even realistic to believe this can be done because the child records, once expanded, are structured differently (and named differently) than the parent's, i.e. the field names are in one column and the values in another as opposed to all of the field names being column headers in the parent. That said, have you tried to do this with any success?

[taivos]

But what about transforming Excel table to JSON (for PowerQuery HTTP POST request)?

[jonass]

Har försökt att få till den lösningen på SCB data men får inte fram Transformeringen tyvärr.
Tips på vad man gör fel??

[rajashekarreddypasula]

Hello, is it possible to get json file from power bi?

[josepimentel]

I was expecting you to explain how you did loaded the json file in excel in the first place

[davidcarballasreboredo]

a veces lo más simple es lo más elegante, para que hacerlo complicado!

[chrisberry]

Lmao your comment about 'being a developer' gave me a good laugh

[markbaxter]

That is not a JSON source, it is an Excel source.