cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Creating new columns from data stored in JSON format

Hi.

 

I'm getting data from a REST feed in to Power BI.

 

One of the columns contains information stored in JSON Object format.

 

e.g.

{"String A":"Value A","String B":"Value B","String C":"Value C"}
{"String B":"Value B","String C":"Value C"}
{"String D":"Value D"}
{"sTRING A":"vALUE A","string B":"value B","String C":"Value C","String D":"Value D"}

If I use the inbuilt JSON parse feature it changes the column into a Record for each row. I can then expand the column and it will create columns for each of the strings with associated values stored in the appropriate rows.

 

This is the functionality that I am after but there are 2 problems I have discovered that I can't find a resolution for:

 

1. Where case for the strings is different ("String A" vs "sTRING A") the column expansion views these as different and creates two separate columns. I require it to be case insensitive as the data coming in can be in any case. Is there a way to do this?

 

2. The column before expansion can have a variable number of strings in it. As Power BI only imports 1000 rows for manipulation, it doesn't actually parse all the rows so it doesn't create columns for some required strings that aren't brought in in the first 100 records. Is there a way to fix this?

 

Thanks for any help.

 

John.

 

 

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Well, for the first one, you should be able to use Text.Lower, Text.Upper or Text.Proper to normalize the text.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Thanks @Greg_Deckler.

 

Sorry didn't explain myself too well on that.

 

I had tried using a transformation with text.proper on the column before the JSON parse and it appears to process everything correctly into proper case but after the JSON parse and column expansion the transformation didn't hold. All the strings are column titles in their original case.

 

 

Highlighted

Hi @johnf,

Would you mind sharing me the PBIX file? We need to know  that how you import data from the source to Power BI Desktop and how you normalize text using Power Query. For the second question, you can write M code to automatically expand all the expandable columns in a table, which is described in this similar blog: https://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/ .

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors