Create a Table from a JSON array in PowerQuery

We've been experimenting with PowerBI for a while in our workplace. I started to receive an interesting task to code in M language, the most unofficial yet officially documented language name in Microsoft Documentation, ever!

Microsoft PowerBI is a very interesting ETL tool that lets you do crazy stuff, BUT here I am gonna show you one specific usecase of it.

Question: How can I turn a JSON array into a table in PowerQuery?


Let's assume that we have a JSON array that consists of two objects (Note that your JSON needs double-quote in M):

Json= "[{""name"":""Amir"", ""surname"": ""Rahnama""},{""name"":""Camilla"", ""surname"": ""Olofsson""}]"

Before any transformation, What you need to is to make your JSON string digestible meaning that to make it a JSON Document:


Basically, you need two main transforms:

  1. Create a table from the list that gets the data and by a splitter function (for a JSON array you can set it to Nothing):

    DataTable = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
  2. Expand the only result column into several columns specifying their names:

    ResultTable= Table.ExpandRecordColumn(DataTable, "Column1", {"name", "surname"}, {"Name", "Surname"})```

Here is the full M function:

   Json= "[{""name"":""Amir"", ""surname"": ""Rahnama""},{""name"":""Camilla"", ""surname"": ""Olofsson""}]",

   // Transform the List Result into a Table
   DataTable= Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

   // Expand the Result Column
   ResultTable= Table.ExpandRecordColumn(DataTable, "Column1", {"name", "surname"}, {"Name", "Surname"})

That's about it, folks! That will solve it!

The Rstats tag of this blog is added to R Bloggers