Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors