How to expand a column that cannot be expanded in Power BI and Power Query in Excel

Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. The expand column – arrows will be missing, but some elements still need to be expanded, like here:

But there is an easy way to fix it:

Transform to expandable column

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type list) then _ else {_} }} )

It transforms the “Column1” from table “Source” by checking, if the content of the each row ( _ ) is of type list and if yes, keep that value ( _ ) and if not, transform it to a list (by framing it into curly brackets {_} )

Syntax for tables

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type table) then _ else #table({“Column1”}, {{_}} ) }} )

Syntax for records

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )

File for Subscribers to download: HTExpandColumnThatCannotBeExpanded.zip

This technique should be applied to columns where the expandable elements all have the same structure. If that’s not the case, you should use this technique instead.

Enjoy & stay queryious 😉

Comments (5) Write a comment

  1. Pingback: #Excel Super Links #110 – shared by David Hager | Excel For You

  2. Hi there

    I have tried this for a column containing some rows of records and some rows of text. It didn’t work though and returned the error “We cannot convert a value of type Record to type Table”.

    Has anyone else had this?

    Cheers

    Redmond

    Reply

    • So you’ve probably used the formula for the table expansion instead the one for the record expansion. Try sth like this:
      Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )

      Reply

  3. I tried this using the record expansion and whilst it seems to work the data in the JSON is not beng imported. All the expanded records in Power BI contain null values. The original JSON contains string values. Am I missing something?

    Reply

    • I’ve never experienced such a behaviour. So there might be an issue with how you adapted the code to your specific file.

      Reply

Leave a Reply

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