Forum Discussion
Advocate IILost columns on expanding JSON
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.
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.
Community ChampionIt needs to be "#Changed Type", so the hashtag is missing ;-)
Community ChampionSorry, that's a typo: Must be #"Changed Type" !!!
- 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.
SkemazHi ImkeF
Again, many thanks for your patience with my topic. Your support is greatly appreciated.
Although your step now works (thanks), I'm still struggling to get all the way to the point I need to get to.
The screen-shots below should illustrate where I want to get to.
For the life of me I cannot get my head around writing the M code to get there.
Please guide me to the next steps.
(5) below is where I am stuck.
(6), (7) & (8) below is my explanation of the data I need in columns - but including column "Source.Name" - because I need to use it as a key in the table.
Many many thanks, Mark.
- ImkeF
You 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.
- Skemaz
Hi 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
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
