Json to excel using power query
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"
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
You need to convert the list to a table first, then you can expand the record column and proceed from there. If no luck, then you can take a look at this video I created recently for a similar question.