Power Query and JSON arrays

When you are working with JSON arrays in Power Query it is not always obvious how to convert the data into a table.

The problem

Let say you try to fetch data from a web service, and it returns the result like this:

JSON

[
  {
    "PlayerID": 1,
    "Name": "Ada"
  },
  {
    "PlayerID": 2,
    "Name": "Bob"
  },
  {
    "PlayerID": 3,
    "Name": "Carol"
  }
]

In the Power Query editor, it will look like this:

Since you want to create a table the natural thing to select is Transform > To table.

This is the settings you get when you want to split a string into smaller parts. So clearly, we are on the wrong track.

The solution

Actually, this it the way to do it. Just keep None as delimiter and just click OK! I do not remember how many times this had confused me.

Next, you could just expand the column:

And then you have your table:

Summary

If the array is in a property instead:

JSON

{
    "Data":
    [
        {
            "PlayerID": 1,
            "Name": "Ada"
        },
        {
            "PlayerID": 2,
            "Name": "Bob"
        },
        {
            "PlayerID": 3,
            "Name": "Carol"
        }
    ]
}

Then, when you convert it into a table the columns are expanded automatically. Way more natural, I think. In fact, the first time I had a pure array to convert I got stuck. So, I used the advanced editor to convert the JSON. First it looked like this:

Advanced editor

let
    Source = Json.Document(Web.Contents("https://rsptournament/api/players"))
in
    Source

And after my modification:

Advanced editor

let
    Source = Json.Document("{ ""Data"": " & Text.FromBinary(Web.Contents("https://rsptournament/api/players")) & "}")
in
    Source

But as you already know this is not necessary. I just wish I had discovered this earlier.