cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amitkumar93
Helper III
Helper III

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

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

Thanks Edhans for your help, it worked perfectly

edhans
Super User
Super User

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

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

 

 

amitkumar93
Helper III
Helper III

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

@amitkumar93 , let us check with experts of power query

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
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 !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
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 !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors