cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rsantos1
New Member

Power query extract value of a json record

Hi,

 

Hi have a nested json file imported to excel, power query, they successfully tranforme to a table the values, but in the third column for example, there is a [Record] value, I want to write in each row the content of the json instead of the [Record], I don't want to transforme again in tables, I want the value of the record. Anyone can help to accomplish that?

 

Column1.name                   Column2.typebody          Column2.registo

 

Testeteste2[Record]
signalGET[Record]

 

Thank you

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Power query extract value of a json record

@rsantos1,

Click the expand button on the upper right corner of the column header, you are able to extract values from the Record column.
Capture.PNG

There is a similar thread for your reference.
http://www.dutchdatadude.com/loading-multiple-json-files-using-power-query/

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rsantos1
New Member

Re: Power query extract value of a json record

I don't want to expand the json value in collumns, I want the raw json value like {"option", ...} and insert in each row, that's the clhallenge..

Moderator v-yuezhe-msft
Moderator

Re: Power query extract value of a json record

@rsantos1,

I am not very clear about your requirment. Could you please share sample data and expected result here?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rsantos1
New Member

Re: Power query extract value of a json record

Hi,

Source json:
[{"name" : "teste", "request_type": "POST", "url": "www.example.com", "body": {"test1": { "log": { "lang": "", "log2": "", "device": "", "state": "Unknown" }, "form": "simple", "gui": "221307"}, "id222": "341735", "size": "302x170"}}]

 

powerquery.png

In the collumn body, I want all the content of the json text, not split into collumns, just all the value

 

Thank you

Moderator v-yuezhe-msft
Moderator

Re: Power query extract value of a json record

@rsantos1,

I notice your column contains nested Record. Please add a custom column using code below, and check if you can expand values from List column.

Record.FieldValues([Column1.body])

1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
rsantos1
New Member

Re: Power query extract value of a json record

Hello,
Thank you for your reply, doesn't work:

Expression.Error: We cannot convert a value of type Record to type Text.

Moderator v-yuezhe-msft
Moderator

Re: Power query extract value of a json record

@rsantos1,

The issue is caused by that you have nested Record in the column, I can't find any methods/functions that can be used to directly transform the nested Record value to Text.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors