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?

score 54 · Accepted Answer · answered Feb 06 '17 at 05:56

54

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"

answered Feb 06 '17 at 05:56

Mike Honey

14,523
1
24
40

13

The Excel from JSON "wizard" is amazingly unintuitive. That little expand button in the column heading is the key. This is not the first time I've had to come to this answer to rediscover it :-( – JasonPlutext Mar 26 '19 at 00:37
1

How would one script this for any given input file? – Josh Gust Dec 04 '19 at 20:25
1

@JoshGust I took Chris Webb's function to Expand all table-type columns and made a clone to expand all record-type columns. I can't say I understand exactly how they work, but they might be useful building blocks for you. https://gist.github.com/Mike-Honey/f5f3f708310eb1de7f4a and https://gist.github.com/Mike-Honey/0a252edf66c3c486b69b – Mike Honey Dec 05 '19 at 03:10
1

I have done this before and still had to come searching for how to do it because it's so unintuitive. And I failed to find the "use original column name" feature but at least I got what I needed for now, until next time when I need to re-figure it out. – Greg Smalter Jun 02 '22 at 16:03

score 22 · Answer 2 · answered Feb 06 '17 at 06:08

22

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

answered Feb 06 '17 at 06:08

Robert K. Bell

9,350
2
35
50

1

Thank you for the answer but i accepted the other one because it suited my problem better :) – kemis Feb 06 '17 at 06:54

score 5 · Answer 3 · answered Feb 06 '22 at 16:17

I have Excel Professional 2016 and I don't see the JSON option, but I can get to it via the "Other Sources" query option. Here are all the steps to convert a JSON array to a table.

Data > New Query > From Other Sources > From Web
Enter URL : "file:///C:/temp/document.json", (or a http web url) Press OK
A row is displayed with a "items" property and List type,
Click on "List", the items in list are displayed
Press "To Table" button in upper left corner, Press OK in next dialog
A table with one column named "Column1" is displayed
Press the button next to the column name (has left and right arrows on it)
Properties in the row object are selected
Uncheck "User Original column name as prefix", Press "OK"
Press "Close & Load" button in upper left corner

my file looks like

 {
   "items": [{
       "code": "1",
       "name": "first"
     }, {
       "code": "2",
       "name": "second"
     }, {
       "code": "3",
       "name": "third"
     },
   ]
 }

Your answer is so detailed. Thank you so much <3 – Ashish Deora Feb 22 '23 at 06:09 — Ashish Deora, Feb 22 '23 at 06:09

score 2 · Answer 4 · answered Feb 06 '17 at 05:54

2

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.

answered Feb 06 '17 at 05:54

MarcelBeug

2,872
1
8
10

Json to excel using power query

4 Answers4

Linked