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("https://contoso.com/api")),
#"Converted to Table" = Record.ToTable(Source[name])
in
#"Converted to Table"
in
ApiCall

and get two-column table:

How-to convert this table to

?

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

     

11 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    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

     

    • dt's avatar
      dt
      Frequent Visitor
      6 years ago

      Getting an error

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi dt 

     

    I've converted your sample JSON to a flat-file and used it as a base for my query, I've attached the result with JSON converted to records for the reference.
    name = JSON translated to a record 

    output = transformed query

     

    let me know if "name" is in wright structure.

     

    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

      Thank you! 

      But still getting the error.