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.

10 responses

  1. Pingback: Pushing Data From Excel To Power BI Using Streaming Datasets – Chris Webb's BI Blog

  2. Pingback: Call an Azure Function from Power BI - Chris Koester

  3. Hello, thanks a lot for the post.

    I have issue with Russian characters. They are displayed as “\u041a\u043e\u0448\u043a\u0430”. I tried different encodings in both FromValue and FromBinary functions.


    Do you know what can help?

  4. Hello, Do you have a solution to be able to automatically export a power Bi table in a sharePoint list with Json?

  5. Hi Chris, thanks for your great input.
    I am struggling where I’d like to ask if you may help me out.
    I’m trying to build a nested JSON structure.
    Specifically a list of objects, where the objects are different.
    “field1”: “value1”,
    “listContact”: [
    { “class:” : “telephone”,
    “telephoneNumber:” “+44111222333”},
    { “class:” : “address”,
    “city:” “Paddington”
    “street:” “Elmstreet”
    “houseNumber:” “13”}

    how could I achieve this ?

    Best regards and thanks in advance

  6. Hi.
    However i was trying to obtain something slightly différent. Instead of having all the data of the three lines in one cell, i want to have a cell for each line. Is there a way to do it? thanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: