Power query extract value of a json record

[rsantos1]

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

Teste	teste2	[Record]
signal	GET	[Record]

Thank you

[v-yuezhe-msft]

@rsantos1,

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


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

Regards,
Lydia

[rsantos1]

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..

[v-yuezhe-msft]

@rsantos1,

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

Regards,
Lydia

[rsantos1]

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"}}]

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

Thank you

[v-yuezhe-msft]

@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])

Regards,
Lydia

[rsantos1]

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

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

[Anonymous]

I wanted to share some thinking around this, that might help someone in this situation find an answer.

In this situation Value is of type list.

Jdoc = Json.Document("file")
Value = Jdoc[#"table"]

or

fieldname = "table"
Jdoc = Json.Document("file")
Value = Record.FieldValues(Record.SelectFields(Json, fieldname)){0}

[v-yuezhe-msft]

@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