Creating new columns from data stored in JSON format

[johnf]

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.

[Greg_Deckler]

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

[johnf]

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.

[v-yuezhe-msft]

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