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.
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.
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 ?
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?
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
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 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!!)
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.
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?
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.
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,
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 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.
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 ??
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
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
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 :)
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.
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 :(
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.
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..
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?
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.
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!!!!
