cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Parsing Complex Json

Hi Team,

I have a dataset like this

amitkumar93_0-1611834025971.png


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

amitkumar93_1-1611834223521.png

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?

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

edhans_0-1611850096643.png

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:

edhans_1-1611850603501.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

8 REPLIES 8
edhans
Super User
Super User

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)

 

edhans_0-1611937051823.png

It will return this:

edhans_1-1611937154143.png

If you want things that are not a list, change the code to:

each Value.Is([Column1], List.Type) = false

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thanks Edhans for your help, it worked perfectly

edhans
Super User
Super User

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

edhans_0-1611850096643.png

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:

edhans_1-1611850603501.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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)

amitkumar93_1-1611913122682.png

 

 

Anonymous
Not applicable

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

@Anonymous , let us check with experts of power query

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



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!
Anonymous
Not applicable

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

amitkumar93_0-1611836158462.png

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


 

amitchandak
Super User
Super User

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



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.