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 

 

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.

  • dt's avatar
    dt
    Frequent Visitor
    6 years ago

    Any ideas?

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      Hi dt 

       

      Sorry for the late reply.

      I've used the JSON sample from your original post and produced a solution in the attached file, please can you ensure that the sample is a true reflection of your scenario, if not, please can you adjust.

      Unfortunately, your screenshot does not give enough information to understand the error.

       

       

      Best Regards,
      Mariusz

      Please feel free to connect with me.
      Mariusz Repczynski

       

       

       

      • dt's avatar
        dt
        Frequent Visitor
        6 years ago

        Yes, this fully reflects my problem.

        Your solution in the attached file not work.

        My PowerBI Desktop Version: 2.73.5586.1241 64-bit (September 2019).

        What information is needed?