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?

Mike Honey · Accepted Answer · 2017-02-06 05:56:15Z

66

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 6, 2017 at 5:56

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JasonPlutext Over a year ago

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 :-(

Josh Gust Over a year ago

How would one script this for any given input file?

Mike Honey Over a year ago

@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. gist.github.com/Mike-Honey/f5f3f708310eb1de7f4a and gist.github.com/Mike-Honey/0a252edf66c3c486b69b

Greg Smalter Over a year ago

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.

Robert K. Bell · Accepted Answer · 2017-02-06 06:08:00Z

26

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 6, 2017 at 6:08

Robert K. Bell

10.5k2 gold badges40 silver badges52 bronze badges

1 Comment

kemis Over a year ago

Thank you for the answer but i accepted the other one because it suited my problem better :)

Florin D · Accepted Answer · 2022-02-06 16:17:44Z

15

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"
     },
   ]
 }

answered Feb 6, 2022 at 16:17

Florin D

1,92019 silver badges21 bronze badges

1 Comment

Florin D Aug 31 at 19:31

BTW, these days you can use AI for this, much easier and works most of the times.

MarcelBeug · Accepted Answer · 2017-02-06 05:54:09Z

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 6, 2017 at 5:54

MarcelBeug

3,0171 gold badge12 silver badges11 bronze badges

Collectives™ on Stack Overflow

Json to excel using power query

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related