Forum Discussion
Parsing Complex Json
Hi Team,
I have a dataset like this
The Column named "Data" is a Json column.
I am trying convert each record into a separate column with each value from JSON.
I tried to Parse it by right click->transform->JSON
but i got the output like this
Here i want convert each rows containing all the keys into separate columns
and the rows containing partial keys or blank should return blank
is it possible?
Anonymous - unfortunately with JSON the data may not be in an ideal nested format that represents a table when extracted. You will have to expand each list to discern what is in it. You can pull those into separate columns. For example, you might have a column that looks like this:
Those are 5 integers. From there, I can convert those to text with List.Transform, then use Text.Combine. So the full M code for a new column to do this is:
#"Added Custom" = Table.AddColumn( Source, "Combined", each try Text.Combine( List.Transform( [Column1], Text.From ), ", " ) otherwise [Column1] )That will return this:
So you may have to use a combination of if/then/else or try/otherwise (the latter is like IFERROR in Excel) with a number of Text and List functions to tease out the data as you need, but we'd have to know what each list contains, and with JSON, each list could be unique.
8 Replies
Anonymous - unfortunately with JSON the data may not be in an ideal nested format that represents a table when extracted. You will have to expand each list to discern what is in it. You can pull those into separate columns. For example, you might have a column that looks like this:
Those are 5 integers. From there, I can convert those to text with List.Transform, then use Text.Combine. So the full M code for a new column to do this is:
#"Added Custom" = Table.AddColumn( Source, "Combined", each try Text.Combine( List.Transform( [Column1], Text.From ), ", " ) otherwise [Column1] )That will return this:
So you may have to use a combination of if/then/else or try/otherwise (the latter is like IFERROR in Excel) with a number of Text and List functions to tease out the data as you need, but we'd have to know what each list contains, and with JSON, each list could be unique.
Use the Value.Is function.
Create your Table.SelectRows statement using the UI - select anything, doesn't matter.
Then change the code from the 1st line (something like this) to the 2nd line.
each [Column1] = 1 each Value.Is([Column1], List.Type)It will return this:
If you want things that are not a list, change the code to:
each Value.Is([Column1], List.Type) = falseAnonymous , there are different kinds of records in this field. Some of them are not JSON values. That is why you are getting this
Thanks amitchandak for the help,
I managed to filter only the JSON records and parsed
then i am getting a mix of values and list as shown belowHere can i only expand the rows containg list and rest keep as it is?
