Forum Discussion

rsantos1's avatar
rsantos1
New Member
7 years ago

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 i...
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
rsantos1
New Member
7 years ago

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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}