Forum Discussion

dt's avatar
dt
Frequent Visitor
6 years ago
Solved

json record to table

Hello. I have rest-api result { "name": { "key1": "value1", "key2": "value2", "key3": "value3" } } I am exec this query: let ApiCall = () => let Source = Json.Document(Web.Contents("http...
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi dt 

     

    Try Adding

     

    = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"key1", "key2", "key3"}, {"key1", "key2", "key3"})

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi dt 

     

    Try this, 

    
// output
let
    Source = name,
    #"Converted to Table" = Record.ToTable(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Name]), "Name", "Value")
in
    #"Pivoted Column"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi dt 

 

Please see the below.

Once converted your sample to a record it will look like below.
Step 1 - Source // no error as on the attached.

Step 2 - Convert to table // no error 

Step 3 // Expanded Value 

 

Please can you confirm in witch step do you start getting the error message?

As demonstrated above, all is working fine on my end.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


  

dt's avatar
dt
Frequent Visitor
6 years ago

I upgraded to Version: 2.73.5586.1501 64-bit (September 2019)
Now everything is all right, thanks!

  • dt's avatar
    dt
    Frequent Visitor
    6 years ago

    Hi Mariusz 

    Sorry, my query looks a little different:

    I uploaded json record to table.pbix here: json record to table 

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi dt 

     

    Try this, 

    
// output
let
    Source = name,
    #"Converted to Table" = Record.ToTable(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Value", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Name]), "Name", "Value")
in
    #"Pivoted Column"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski