Parsing Complex Json

[Anonymous]

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?

[edhans]

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) = false

[Anonymous]

Thanks Edhans for your help, it worked perfectly

[edhans · Accepted solution]

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

[Anonymous]

Hi Edhans,

One more question,

In this table below how can i only filter out values except Lists ( if i am trying to filter by clicking the button on the right of value, i am not finding the Lists to filter out)

[Anonymous]

Is it Possible to only convert JSON records and rest all to blank values??

[amitchandak]

@Anonymous , let us check with experts of power query

@ImkeF , @edhans , can you help on this one

[Anonymous]

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 below

Here can i only expand the rows containg list and rest keep as it is?

[amitchandak]

@Anonymous , there are different kinds of records in this field. Some of them are not JSON values. That is why you are getting this