Json to excel using power query

Question

I have some json on a website that i want to convert to excel using the power query option from web. But I ran into a small problem. My json looks like this:

[     {         "id" : 1,         "visitors" : 26,         "some_number" : 1,         "value" : 3500     },     {         "id" : 2,         "visitors" : 21,         "some_number" : 5,         "value" : 2000     } ] 

but when i use from web i get this:

I can drill down into a record,convert it to a table, transpose and use first row as header but then i get just one row. How can i get all of my data to the table and not just one row?

Answer 1

First I would use the List Tools / Transform menu (it should be automatically selected) and click the To Table button. This will give you a single-column table with 2 rows. Then I would click the small Expand button - it will appear in the column headings, just to the right of "Column1". Uncheck the Use original column name ... option and you will get a table of 4 columns and 2 rows.

Here's the full script I generated:

let     Source = Json.Document(File.Contents("C:\Users\Mike.Honey\Downloads\json2.json")),     #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),     #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "visitors", "some_number", "value"}, {"id", "visitors", "some_number", "value"}) in     #"Expanded Column2"  

Answer 2

The Table.FromRecords() function is suitable for that sample data:

let      Source = Json.Document("[{""id"": 1, ""visitors"": 26, ""some_number"": 1, ""value"": 3500}, {""id"": 2, ""visitors"": 21, ""some_number"": 5, ""value"": 2000}]"),     AsTable = Table.FromRecords(Source) in     AsTable