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 am not very clear about your requirment. Could you please share sample data and expected result here?

Regards,
Lydia

rsantos1's avatar
rsantos1
New Member
7 years ago

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

  • 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