Automatically expand all fields from a JSON document in Power BI and Power Query

If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all values in column “Value” and additional columns describing where that value came from in a hierarchical form, no matter how deep the nesting goes:

Example

See below what this function does for the following JSON :

Automatically expand JSON: Sample JSON

{"A":"A Record", "B":["ListItem1", {"C":"A nested Record", "D":"Another nested Record"}], "E":{"F":["NestedListItem1","NestedListItem2","NestedListItem3"]}}

Automatically expand JSON: Result

 

  • Value: The respective values
  • Level: Main levels.
  • Sort: Sort column with hierarch to display in report
  • SortBy: Sort your “Sort”-column in the data model by this column: It will be filled up with “0” according to the maximum value within the respective position. This effectively allows sorting by number, although it is a text-field.
  • Name-columns: They display the hierarchical location of the value with regards to JSONs record field names.

The Function

 

How the code works

  • The main elements in JSON are records and lists and they can appear in many different combinations. So to handle them equally, I often convert them into a common type so that they can be further processed equally (row 6, 27 and 32).
  • I use List.Generate (row 10-42) to repetitively check whether the returned values are further expandable or not. If they are not expandable, they go into “Finished” (row 25) and if they need further expanding, they land in “Unfinished” (row 26) and will be expanded further.
  • Row 48-56 contain a dynamic padding that will convert the Sort-entry “2.1” into “02.1” in column “SortBy”, if there is also a “10.x” in the column. This will make sure that the (alphabetical) sort returns “2.1” before “10.1”. (And if there is a 100.x it will convert to “002.1” and so on…)

I’m pretty sure that this code can be further improved, as it has been evolved for quite some time and I didn’t re-engineer it. So if you’re up for it, please don’t hesitate to post an improved version!

For large JSONs, the table can get very long and it could be beneficial to view it in a more compact form (actually a form that would provide tables for a relational model). I will show this in an upcoming article, so if you’re interested in it, make sure to subscribe to my blog not to miss it.

Enjoy & stay queryious 😉

Comments (18) Write a comment

  1. Pingback: Flattening JSON In Power BI – Curated SQL

    • Yes, I’m coding directly in Power Query: Either in the formula bar or the advanced editor.

      Reply

  2. Pingback: Power BI Sudoku, Custom fonts, DAX and more... (June 25, 2018) | Guy in a Cube

  3. Hello,

    long time fan – first time post.

    I am experiencing difficulties using this –

    do i need to replace in the script to reference MY json ?

    when the function is built – it won’t let me select a table to run the function – when I type my table into the function input it isn’t running.

    my json table is not expanded at all.

    the function is created – it just wont let me type in the json in the paramater to invoke the function.

    any insight is greatly appreciated.

    Reply

    • Hi Jeff,
      my function doesn’t expect a table as an input, but a JSON-format instead. That’s basically a text-file/string in JSON-format.
      So if you have transformed your JSON to a table already, you have to delete those steps and feed the raw JSON to my function.
      /Imke

      Reply

  4. I am getting results in rows rather I would prefer to be displayed in columnar structure. Is it possible to get the results like Column – Headers and Rows – values?
    eg:
    Value Name Name1
    1 data id
    aaa data name
    2 data id
    bbb data name

    Expected output:

    Id name
    1 aaa
    2 bbb

    Reply

    • Yes: You can pivot the data to achieve this.
      For this you want to add a column that cuts off the figures after the last . within the Sort-Column.

      But due to the (mostly) nested structure of JSON-files, one table with all the results might not be the ideal solution. So you might want to filter the data according to the different tables you want to create out of the data before.

      Reply

      • Hi,

        Thanks a lot 🙂

        I have tried suggested solution. It is working and able to retrieve data in columnar structure.
        But when it comes to more than one level(if nested structure), not able to achieve solution dynamically.

        eg: Name.1,Name.2,Name.3 etc…

        Any suggestions please?

        Thanks,
        Karthiga

        Reply

  5. Hi Imke Feldmann,

    Any help is much appreciated for the above queries.
    Thanks in advance.

    Regards,
    Karthiga

    Reply

    • Hi Karthiga,
      sorry for late response:
      You need to create a key-column that combines the values from all names-columns dynamically. Just add a columns with this code:
      Text.Combine(List.Transform([Dots], (x) => Text.From(Record.Field(_,x))), ” | “)

      Reply

      • Thanks Imke.
        But I am not quiet understanding that, what do you mean by “Dots”. Is this code to be added inside loop or outside (end)?
        Can you please help?

        Reply

        • Sorry, updated the function code on GitHub. That column is now included – please use that new function code.
          /Imke

          Reply

  6. Hi Imke,

    Yeah it worked finally. Thanks a lot 🙂
    I have added few more steps ‘ToPivot’ table data in a readable format.

    T =Table.AddColumn(#”Added Custom”,”ToPivot”,each Text.BeforeDelimiter([Sort],”.”, {2,RelativePosition.FromStart})),
    #”Removed Columns” = Table.RemoveColumns(T,{“Level”, “Sort”, “SortBy”, “Name”, “Name.1”, “Name.2”, “Name.3”, “Dots”}),
    Final = Table.Pivot(#”Removed Columns”, List.Distinct(#”Removed Columns”[Custom]), “Custom”, “Value”)

    Regards,
    Karthiga

    Reply

  7. Hello,

    After using the function, my data is being organized like this:

    COLUMN 1 – (Names.1) COLUMN 2 – (Values)
    “ID” 1
    “Name” John Doe
    “Rating” 3
    “ID” 2
    “Name” Jane Doe
    “Rating” 4
    (etc.)

    When I pivot COLUMN 1 in Power Query, the values become columns and headers as expected, but only a single row is displayed, and the value for each row cell is a “Error”. When I click on “Error”, I get the following message:

    “Expression.Error: There were too many elements in the enumeration to complete the operation.
    Details:
    List”

    Not sure what I’m doing wrong. Any ideas?

    Thanks.

    Reply

    • If you want to pivot your data, you need to select “Don’t aggregate” in the advanced options of the pivot dialogue.
      But you also need another column that indicates the rowID. Cutting off the last two elements in the column “Sort” might do that trick. – See the conversation with the user “Karthiga” in the comments-section of this post.
      /Imke

      Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz