Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
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..
@rsantos1,
I am not very clear about your requirment. Could you please share sample data and expected result here?
Regards,
Lydia
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
@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
Hello,
Thank you for your reply, doesn't work:
Expression.Error: We cannot convert a value of type Record to type Text.
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}
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.