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?
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.