Forum Discussion
Advocate IILost columns on expanding JSON
Community ChampionYou could continue with a variant of my list-suggestion, now applied to a record, like you also find in my blogpost.
But I would do it differently: Instead of expanding the JSON in one query, I would expand it in multiple queries and then merge them back together after their parts have been expanded.
It would be easiest for me to explain, if you could share some sample data.
Advocate IIHi ImkeF
Creating multiple queries then merging sounds like a great idea !
Below are links to two of the JSON files that I have.
As mentioned above, I combine the files by "Getting" a folder.
The idea is that as new JSON files arrive we'll copy them into the folder and refresh to update the reports.
Again, many thanks for your time and patience.
Best regards, Mark.
- ImkeF
Hi sunildatalytyx ,
if the fields are not included in the first 1000 items of the expanded content, then they will not show up.
In that case you have to add them to the M-code manually.
Imagine the automatic expansion had just found "PRN", then you'd add SNR like shown below:
Simply add them to the list with commas as separators.
- ImkeF
Please check out this file: https://www.dropbox.com/s/bram6131s01nv1t/PBI_ExpandJson.pbix?dl=0
You need to edit the query "Download" with a reference to your local drive
You just have to decide whether to join the tables in the query-editor, which would create one big monster-table, or create a data model, which would be the more professional approach.
- ImkeF
For (8): Do you only want the 1st element like you highlighted or all of them?
- SkemazAll of them please. I intend to create visualizers that show horse speeds and locations relative to one another over time. I hope that makes sense. Thanks.
- SkemazI'm away from my computer at the moment (in Sydney, Australia).
I'll check that out as soon as I get home.
Many thanks indeed. I know I'll get this working - all thanks to your help.
Best regards, Mark. - Skemaz
Hi ImkeF
Thank you so very much :-)
Before you came along I was pretty much clueless on how to figure this out.
Here's a "High-Five" to you !!!
I have learned a valuable lesson from your excellent instructions and cannot THANK YOU enough.
I can't imagine a way to reciprocate right now but if I ever can I certainly will.
Many many thanks and all the best, Mark.
Hi,
I am facing similar issue, however my JSON file is only a record and when i transform it using the JSON option, everything is expanded as expected except for 1 column. I can see this value being in the JSON but this not flowing as expected after the expand. I have gone through your conversation and also few other blogs but not able to resolve it.
Below is a screeshot of the problem:List of all the columns, 10155 not seen in the expand tables options and customfield 10155 available when i copy paste the fields onto sublime text.
Thanks in advance!
Thank you Imke, thank you for the response, appreciate it. I've realised over time the below response and added the fields manually. Thanks
