Generating JSON In Power BI And Power Query

Often, when calling web services from Power BI or Power Query, you’ll need to generate some JSON inside your query to send to these web services. The M language makes this relatively easy to do with the Json.FromValue() function but there’s only one example of how to use it in the help so I though it might be useful to provide some worked examples of M data types and how Json.FromValue() turns them into JSON.

First, here’s a function – that I’ve called GetJson() for the examples here – that takes a parameter of any data type, passes it to Json.FromValue() and returns the JSON representation of the input as text:

(InputData) =>
    JsonOutput = Json.FromValue(InputData),
    OutputText = Text.FromBinary(JsonOutput)

Now, let’s see what this returns for a variety of different data types.


Take the following table on an Excel worksheet, called “Sales”:


The following query loads the data from this table and calls the GetJson() function defined above:

    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(
	{{"Month", type text}, {"Sales", Int64.Type}}),
    Output = GetJson(#"Changed Type")

It returns the following JSON, an array of objects:



M language lists are represented as JSON arrays, so the following query:

GetJson({"Apples", "Oranges", "Pears"})




GetJson({{1,"Apples"}, {2,"Oranges"}, {3,"Pears"}})




M language records are represented as JSON objects, so:

GetJson([month="January", product="Apples", sales=1])



Nested Data

Finally, in M it’s possible to have nested data structures: lists of tables, records containing lists and so on. These can be converted to JSON too, so in the following example of a record containing a text value, a list and a table (created using #table):

  #table({"Month", "Sales"},
    {"January", 1},
    {"February", 2}

…the JSON output is:


You can download the sample Excel 2016 workbook with these examples in here.

2 thoughts on “Generating JSON In Power BI And Power Query

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s