Easy unstacking of columns in Power BI and Power Query (and Excel)

The demand to unstacking a column into a table is not rare (see here for example: PowerBIForum  ) . Also if you copy a table from a post in the Power BI community forum  to the enter-data-section in Power BI, it will show up as such a one-column-table.

Task

Unstack column to table

 

Solution

Table.FromRows( List.Split( Table1[Column1], 3) )

..provided that your table is named “Table1”

How it works

You have to read a formula like this like an Excel formula: From inside out.

  1. Transform a table to a list-format by selecting the relevant column (here: [Column1])

Transform table to list by adding a column reference

2. Chunk up the list into a nested list with one list per each row

List.Split chunks up list into one list for each row

The number in the last argument of the function determines the number of columns of the desired table.

3. Create table from that list

Create table from list of nested lists using Table.FromRows

This formula creates a table from a list of rows. Those rows have to be in the format of a list as well, so the desired input matches exactly the format from step 2. 

4. Optionally: Integrate column names for the new table

Integrate new column names in formula

Benefits

I prefer this formula-solution to other methods that use the UI and add index- and modulo columns before finally pivoting. Mainly, because I don’t have to create a function to apply this transformation within an add-column-step where I want to apply it to all rows of a table. 

Enjoy & stay queryious 😉

Comments (23) Write a comment

  1. Hey Imke,

    That’s very cool. Definitely like the simplicity of the single line of code. I like the UI approach better for newer users (M code can be intimidating), but that is pretty awesome once you get comfortable. Would be interesting to compare the two approaches for larger data sets. 🙂

    Reply

    • Thank Ken 🙂
      Haven’t compared performance on this specific case, but used the Table.FromXXX – functions fairly often for other use cases where they have been extremely fast.

      Reply

  2. Hi Imke 🙂
    This is exactly the same code i have been using for years
    Good to know that we think alike :-))

    sq

    Reply

  3. Pingback: Converting One Column Into Multiple With Power Query – Curated SQL

  6. Great solution!

    One issue I have is that when I tried to include the data type I want in the Table.FromRows function it doesn’t convert the data to the type I identified. It’s identified properly in the column headers, but the data in the columns does not convert. Can anyone else replicate this? And if so do you have any idea why it won’t convert the data to the type identified for the column?

    Here’s the formula I used:
    Table.FromRows(List.Split(Source[Column1],3), type table [ID = Int64.Type, Name = text, Age = Int64.Type])

    Reply

    • Hi John,
      you’re just ascribing a type with that transaction. That will NOT convert the values in the columns. (See this blogpost for details: https://www.poweredsolutions.co/2018/03/12/data-types-data-conversion-ascribed-data-types-power-query-power-bi/) .

      Like this for example:
      Table.TransformColumnTypes(Table.FromRows(List.Split(Source[Column1],3), type table [ID = Int64.Type, Name = text, Age = Int64.Type]),{{“ID”, type number}, {“Name”, type text}, {“Age”, type number}})

      A shortened code would then be this:

      Table.TransformColumnTypes(Table.FromRows(List.Split(Source[Column1],3), {“ID”, “Name”, “Age”}),{{“ID”, type number}, {“Name”, type text}, {“Age”, type number}})

      Reply

  8. That’s a great solution! Thanks!

    I was facing the unstacking task just recently and couldn’t find something like the List.Split function. It turns out that Microsoft’s online documentation did not include the “List.Split” function in the function index on the left hand side, but did mention it in the “List functions overview” page. Is there a way to let Microsoft know about this issue and re-index that documentation?

    Reply

    • Hi Ben,
      There is a feedback-button down to the right (“Did you find the page helpful?”) where you can paste that comment.
      I will try my contacts as well
      /Imke

      Reply

  9. @Imke
    The below should work as well
    = #table(3,List.Split(D[Column1],3)) – Default column headers
    or
    = #table({“Id”,”Name”,”Age”},List.Split(D[Column1],3)) – Specified column headers
    or
    = #table(type table [Id=number,Name=text,Age=number],List.Split(D[Column1],3)) – specified column headers and data types

    Reply

  10. Thanks for the code. How can I split the tables if I have 3 tables stacked together instead of 1 column stacked per your example. Many thanks

    Reply

    • Not sure if I understood your request right, but you might check out this code:

      let
      Source = #table({"StackedTables"}, {{#table({"Column1", "Column2"}, {{"A", "C"}, {"B", "D"}})}, {#table({"Column1", "Column2"}, {{"E", "G"}, {"G", "H"}})}}),
      Custom1 = Source[StackedTables],
      TransformTablesToColumns = List.Transform(Custom1, Table.ToColumns),
      UnionListOfColumns = List.Union(TransformTablesToColumns),
      TableFromColumns = Table.FromColumns(UnionListOfColumns)
      in
      TableFromColumns

      Otherwise, please specify your case more clearly please.
      Thanks and cheers,
      Imke

      Reply

  11. Nice solution, but there is a problem.

    Let us assume we have the alphabet in the column, means 26 values, and we want to create columns with 10 chars in each:

    = Table.FromRows(List.Split(Table1[Column1],10))
    returns a table full of errors.

    The problem seems to be that the last list has only 6 items. Is there a simple workaround for that?

    Thanks, Andreas.

    Reply

Leave a Reply