json record to table

[dt]

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

?

[Mariusz · Accepted solution]

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]

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.

[dt]

Thank you! 

But still getting the error.

[dt]

Any ideas?

[Mariusz]

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.

[dt]

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?

[Mariusz]

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.

[dt]

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

[dt]

Hi @Mariusz 

Sorry, my query looks a little different:

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

[Mariusz · Accepted solution]

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 · Accepted solution]

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.

[dt]

Getting an error