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: Record.FieldNames(Record.Combine(PreviousStepName[ExpandColumnName]))
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.
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!!
Great little tip, Adam. The hours I've spent trying to format the contents of a JSON file...! The only issue with what you demo'ed is that if the JSON format is not static, you may end up trying to extract out a field value that doesn't always exist in your specific JSON block. I guess that we could use a try-otherwise block around the line, but that's a bit severe if just one of the fields in the JSON isn't present? What do you think?
Yup, I forgot to call out that part in the video. Imke had a solution on Twitter and i'm going to make a comment here and Pin it so others are aware of it. Thanks for calling it out.
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 ?
Transform>JSON is definitely one of the best kept secrets-- it has definitely saved my life in parsing JSON in PBI. One of the most powerful aspects of the JSON structure is the neigh-unlimited power of nested arrays...but this is also a royal pain to manually expand. E.g. I transform a JSON column into records and expand all columns. Some of these columns contain their own nested arrays of variables and show up as "Records" in the newly expanded column values. Another round of expand columns/expand to rows is needed to unlock the second teir of arrays....and so on and so forth for however many nested variables you have. Is there a way to quickly (and use less lines of code in the query editor, for performance power) expand all columns and nested arrays from a JSON document in Power Query?
Hi Ben, I've published a function to automatically expand all fields from a JSON here: www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/ It will return a large table with one row per value. In an upcoming post I will show a method how to group them together again in a format more suitable for the data model. /Imke
@@thebiccountant Could you please give me the url where you have explained about how to group them together again in a format more suitable for the data model.
Thank you for this. This was very helpful. I now seem to be going down a rabbit hole with my Sharepoint data - Seems there are lists within the JSON data and records within these lists and the list data needs to be pivoted. I'm lost! I've been told to turn the lists into tables, but how do I tie those tables back to the rest of the data in the record and, most importantly, how do I make this repeatable as new data comes in?
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.
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
How do you deal when (after column is transforormed .json) there’s empty columns that transform into an “error” and the other’s transform into “records”?? I’m having trouble being able to expand the Records because of the Errors :/
Having same problem. My JSON field is truncating in the query editor, but it shows in full, in the dashboard view. When I try to transform->JSON in query editor, it only returns 'error'. Still researching a solution, and if I find one, I will LYK.
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.
I have an api that returns a json data how can i handle that it only displays "message", "firstPageUri", "nextPageUri", "lastPageUri" and "page". How to import it from web and display it in table? thanks,
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 ??
The problem arises when there are multiple json files and I want to combine them in power bi desktop. I have 6 different categories of json and each category has 3 or 4 json files to combine. Power BI Desktop doesn't help in this case. It's very cumbersome to combine them. Do you know of a good way to combine json files before we import them into power bi?
Hey! I'd highly encourage you to post your full scenario with sample files on the official Power Query forum on the technet site. The solution it's easier than what you can possibly imagine :)
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.
Hi Folks, How can i use "or " command in API data extraction. I can easily use "&" to filter from multiple columns (for eg: select rows with Value "Canada" in Country_column, & value "Hockey" in Sports_column= api/reporting/request?Country=Canada&Sport=Hockey). But I do not know what syntax will I use for selecting two values in the same column. For eg: if I want data for both USA and Canada in Country Column?? (I tried " ; " , "or" and parenthesis, nothing worked!!)
Can't we just pull the data in from an API as JSON format(where it's not just a single key valued pair, but multiple nested keys & values) and make a report for that?
Hi Adam, Please are we able to get JSON from a web API. I tried getting the latitude and longitude of an address data from the web but the API outputs the data in JSON. What I wanted to do was to write a function in power BI to parse the address as a parameter into the API call so each row gets automatically populated with the Lat and long data from the web. But , after several attempts and research I found out that power bi is not able to interpret this component. It always comes up with an error message but when I loaded the JSON file to my local drive the works using the JSON data connector . This is not what I want cos there's lots of addresses and I just want to use the web data connector to call the API. Please do you have any video that addresses this issue or can you make one. Thanks ever so much!
You definitely can get JSON from a Web API. I have done demos of that with a Star Wars rest api where it converts web.contents into JSON.Document. From there, you can expand the contents. Very similar to what was done here. When I used the Get Data > Web, and put in the REST API URL, Power Query automatically wrapped it in JSON.Document because the response was of the JSON content type. I'm not sure what you are calling, but you could do it manually also.
can we live streamed a data into power bi service using above method..like JSON value will keep on updating in single cell and then we need to interact stream dataset, created in power bi service with value in excel which is being keep on updating on its own
You can mashup data. Reports aren't streamed though, so the data would need to be historical somewhere that the report would pull from. Dashboard tiles can be streaming.
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
Hi, great video! Just one question: is performance going to be an issue with bigger data sets? e.g. when converting json-data in a mysql database with millions of records? Wouldn't it be better to actually parse the jsondata into separate tables in this case?
The more work that has to be done on the Mashup Engine side, the more performance overhead you will have. If you have a way of pre-processing that before you get to Power BI, that may perform better. As with all things, you would need to test it to see what works better.
All right, thank you for your prompt answer! Your video has helped me a lot and I was able to visualize data rather quickly in Power BI! (albeit with a small data set.) I'll look into pre-processing the data first.
This function is no longer working (changed) - instead after I transform it to JSON - it asked me an option to expand to new roles or to extract values - both of which doesn't work anymore :(
Hi Adam, May you make a video about working with sdmx-json in power bi. For example i am try to work with OECD.Stat Developer API to import some data to Power BI but it is not working. Something is going wrong with me. Thanks
It cannot be done for specific types of visuals, or even individual visuals. You can hide the header bar for all visuals within a report in Power BI Desktop by enabling the report setting "Hide the visual header in reading view". When you publish that to the Power BI service, it won't show the bar with the ellipses that has the Show Data option in it. This would affect all visuals within the report.
Not a whole lot of info to go on there. From a general stand point, when I searched for that error, I got a lot of hits. Have you tried going through any of those to see if it points you in the right direction?
Hi Guys ..your videos are wonderful.Can you please guide me how I can extract data from YAML file . I tried using transform via JASON and parse via JASON in power query buit its not working .Can you please guide..
More importantly.. Could you point me in the direction of the Deadpool picture you use as a background on your second monitor? It looks really cool, and would like to use it myself. :D
What I am wrong doing while calling rest API in power Bi ? Because I am getting JOSN Response but To MUCH Column ,each key and Value pair's coming in Columns by , And I know it should be come only one column ?
This. This is the sort of thing where PBI totally defeats Tableau. PBI can handle almost any kind of data you throw at it. The year now is 2020, and Tableau STILL does not have any JSON parser function!!!!
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:
Record.FieldNames(Record.Combine(PreviousStepName[ExpandColumnName]))
Hi, I'm new to power bi. Where exactly I have to add that formula? Thanks.
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.
It is on the list :) ty good sir!
Ditto, any movement on this item? (This was a great video BTW, got my like and subscribe)
Any update on this yet ?
@@GuyInACube yo, did this ever happen? ;)
@@GuyInACube This would be very helpful!
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!!
Thanks man!! I was breaking my head for two days! You are a legend!
Great to hear! 👊
Great little tip, Adam. The hours I've spent trying to format the contents of a JSON file...!
The only issue with what you demo'ed is that if the JSON format is not static, you may end up trying to extract out a field value that doesn't always exist in your specific JSON block. I guess that we could use a try-otherwise block around the line, but that's a bit severe if just one of the fields in the JSON isn't present? What do you think?
Yup, I forgot to call out that part in the video. Imke had a solution on Twitter and i'm going to make a comment here and Pin it so others are aware of it. Thanks for calling it out.
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 ?
Great video. My challenge is that my JSON files have list within unique records. Any help?
can you do a video how to resolve JSON Error "
We found extra characters at the end of JSON input"
Came across a json data source from a customer and was able to use this so thank you! it's sweet.
Woot! very awesome! Glad it helped! 👊
Transform>JSON is definitely one of the best kept secrets-- it has definitely saved my life in parsing JSON in PBI. One of the most powerful aspects of the JSON structure is the neigh-unlimited power of nested arrays...but this is also a royal pain to manually expand. E.g. I transform a JSON column into records and expand all columns. Some of these columns contain their own nested arrays of variables and show up as "Records" in the newly expanded column values. Another round of expand columns/expand to rows is needed to unlock the second teir of arrays....and so on and so forth for however many nested variables you have. Is there a way to quickly (and use less lines of code in the query editor, for performance power) expand all columns and nested arrays from a JSON document in Power Query?
That's good feedback Ben! I'm not aware of an easier way to do that today. I can pass on the feedback though.
Hi Ben, I've published a function to automatically expand all fields from a JSON here: www.thebiccountant.com/2018/06/17/automatically-expand-all-fields-from-a-json-document-in-power-bi-and-power-query/ It will return a large table with one row per value.
In an upcoming post I will show a method how to group them together again in a format more suitable for the data model.
/Imke
@@thebiccountant Could you please give me the url where you have explained about how to group them together again in a format more suitable for the data model.
Thank you for this. This was very helpful.
I now seem to be going down a rabbit hole with my Sharepoint data - Seems there are lists within the JSON data and records within these lists and the list data needs to be pivoted. I'm lost!
I've been told to turn the lists into tables, but how do I tie those tables back to the rest of the data in the record and, most importantly, how do I make this repeatable as new data comes in?
^ This...obviously no good answer from the Cube
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.
Never come across JSON in PBI. If I do, now I know what to do! Easy process.
Super easy!
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
How do you deal when (after column is transforormed .json) there’s empty columns that transform into an “error” and the other’s transform into “records”?? I’m having trouble being able to expand the Records because of the Errors :/
Having same problem. My JSON field is truncating in the query editor, but it shows in full, in the dashboard view. When I try to transform->JSON in query editor, it only returns 'error'. Still researching a solution, and if I find one, I will LYK.
Is the icon over the O in JSON, the Black Sun monument in Seattle?
I'm actually not sure. I tried to find the answer but couldn't find one.
Great tip! If you interact with APIs, you are sure to need a way to parse JSON responses.
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.
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?
Is there any way to fetch data as json from power bi to angular using rest api
I dont see an "expand" button on my JSON column header that appeared when you did it. Is that somewhere else now?
Is there any way to get the JSON of any visuals.
I have an api that returns a json data how can i handle that it only displays "message", "firstPageUri", "nextPageUri", "lastPageUri" and "page". How to import it from web and display it in table?
thanks,
Very useful mate , Thanks
exactly what I needed
Thanks
Thanks a lot ;) Excellent tip ;)
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 ??
The problem arises when there are multiple json files and I want to combine them in power bi desktop. I have 6 different categories of json and each category has 3 or 4 json files to combine. Power BI Desktop doesn't help in this case. It's very cumbersome to combine them. Do you know of a good way to combine json files before we import them into power bi?
Hey! I'd highly encourage you to post your full scenario with sample files on the official Power Query forum on the technet site. The solution it's easier than what you can possibly imagine :)
I agree. It should be doable. You can easily merge/append different queries within Power Query. Not sure if you have tried that route.
Thanks work great in Excel too.
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.
I have json link which refresh every hour and generate data how can I integrate with powerbi and how can I save data ? Please guide
Hi Folks, How can i use "or " command in API data extraction. I can easily use "&" to filter from multiple columns (for eg: select rows with Value "Canada" in Country_column, & value "Hockey" in Sports_column= api/reporting/request?Country=Canada&Sport=Hockey). But I do not know what syntax will I use for selecting two values in the same column. For eg: if I want data for both USA and Canada in Country Column?? (I tried " ; " , "or" and parenthesis, nothing worked!!)
can you go in reverse easily? like if got a couple tables with list/records and properties i want transform into a json format
There is no step called parse json in direct query, I need to do this step in direct query mode. Please help me with it
Very good my friend!!!
Brilliant tutorial, thank you!
Can't we just pull the data in from an API as JSON format(where it's not just a single key valued pair, but multiple nested keys & values) and make a report for that?
is there a way we can automate the JSON file INTO bar/graphs in POwer BI
You are a life saver man wow that was great
Hi Adam,
Please are we able to get JSON from a web API. I tried getting the latitude and longitude of an address data from the web but the API outputs the data in JSON. What I wanted to do was to write a function in power BI to parse the address as a parameter into the API call so each row gets automatically populated with the Lat and long data from the web. But , after several attempts and research I found out that power bi is not able to interpret this component. It always comes up with an error message but when I loaded the JSON file to my local drive the works using the JSON data connector . This is not what I want cos there's lots of addresses and I just want to use the web data connector to call the API. Please do you have any video that addresses this issue or can you make one.
Thanks ever so much!
You definitely can get JSON from a Web API. I have done demos of that with a Star Wars rest api where it converts web.contents into JSON.Document. From there, you can expand the contents. Very similar to what was done here. When I used the Get Data > Web, and put in the REST API URL, Power Query automatically wrapped it in JSON.Document because the response was of the JSON content type. I'm not sure what you are calling, but you could do it manually also.
Guy in a Cube thanks a lot for the prompt response. Please can you send me the link to the video. Thanks ever so much.
can we live streamed a data into power bi service using above method..like JSON value will keep on updating in single cell and then we need to interact stream dataset, created in power bi service with value in excel which is being keep on updating on its own
You can mashup data. Reports aren't streamed though, so the data would need to be historical somewhere that the report would pull from. Dashboard tiles can be streaming.
After I transform it not giving me any data to select what do you think it the problem
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
Hi, great video! Just one question: is performance going to be an issue with bigger data sets? e.g. when converting json-data in a mysql database with millions of records? Wouldn't it be better to actually parse the jsondata into separate tables in this case?
The more work that has to be done on the Mashup Engine side, the more performance overhead you will have. If you have a way of pre-processing that before you get to Power BI, that may perform better. As with all things, you would need to test it to see what works better.
All right, thank you for your prompt answer! Your video has helped me a lot and I was able to visualize data rather quickly in Power BI! (albeit with a small data set.) I'll look into pre-processing the data first.
is there a drill down JSON file for DMA?!?!? I need one bad!!!
Nice one Adam
Thanks Mathew! 👊
This function is no longer working (changed) - instead after I transform it to JSON - it asked me an option to expand to new roles or to extract values - both of which doesn't work anymore :(
Hi Adam, May you make a video about working with sdmx-json in power bi. For example i am try to work with OECD.Stat Developer API to import some data to Power BI but it is not working. Something is going wrong with me. Thanks
This is super easy!
Is there any way to hide the see records option in Matrix visualization of POWER BI?
It cannot be done for specific types of visuals, or even individual visuals. You can hide the header bar for all visuals within a report in Power BI Desktop by enabling the report setting "Hide the visual header in reading view". When you publish that to the Power BI service, it won't show the bar with the ellipses that has the Show Data option in it. This would affect all visuals within the report.
How does one loop a folder of JSON files though and combine?
Hey! How may I get data from .json?
hello I am having error when refreshing my query, "Expression.Error: The column 'Column1' of the table wasn't found.
"
Not a whole lot of info to go on there. From a general stand point, when I searched for that error, I got a lot of hits. Have you tried going through any of those to see if it points you in the right direction?
Hi Guys ..your videos are wonderful.Can you please guide me how I can extract data from YAML file . I tried using transform via JASON and parse via JASON in power query buit its not working .Can you please guide..
Appreciate the kind words Samir! I will put it on the list, but don't know when it would be out.
Great, Adam! But hey, warning! Pieblanc is at risk (3:58). Get him out of the risk haha ;-)
hahaha thanks Karine! He is definitely at risk! ;)
Wow that JSON button was hiding in plain sight!
Freakin' marvellous!
OH MY GOD !!! ITS VERY EASY !!!!
Thanks ! It's saving my connection Power BI/Snowflake :D
How to work with JSON while using live connection
loading blocked by failures with other queries while refreshing API data
Thanks, this solution worked
More importantly.. Could you point me in the direction of the Deadpool picture you use as a background on your second monitor? It looks really cool, and would like to use it myself. :D
haha :) there was some wallpaper site I found that I pulled it off of. That was a while ago and I don't remember what it was.
Awesome 👍👍👍😊
great stuff!
Thank you!
Most welcome! Thanks for watching Karen. 👊
what do I do if my JSON has Lists? 😕
Amazon video. How I do that’s in Power BI web?
Is there a video on how to source Azure Cognitive Services?
We don't have anything on Azure Cognitive Services. What specifically are you looking for?
Peace of cake. Thank you guys...
Awesome! Thanks for watching.
soooooo good!
Iam getting a error while parseing the data
why cannot allow just read ndjson
Hey guys ,, can i join you ? :) you are making the developer life easy
haha love it! We don't have a spot right now though. Keep crushing it though. 👊
Guy in a Cube thanks for replying..#crushingcontinue
Can we get the details of a chart like dimension and measures in json using power bi api?
Awesome
Thanks for watching Teodoro!
awesome
What I am wrong doing while calling rest API in power Bi ?
Because I am getting JOSN Response but To MUCH Column ,each key and Value pair's coming in Columns by ,
And I know it should be come only one column
?
This. This is the sort of thing where PBI totally defeats Tableau. PBI can handle almost any kind of data you throw at it. The year now is 2020, and Tableau STILL does not have any JSON parser function!!!!
Not really helpful unless we can group the new records back to t original record and then make the json data into normalized tables.
Right click to transform to json 😀
..you'll find it when you create a table within PBI..
This is not what my data looks like when i connect to it in power bi.
Awful! Need to show how you input JSON not CSV with JSON inside! ta