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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.