Pivot an Unknown Number of Rows into Columns

Sometimes you have a table of data that describes a 1 to many relationship. The number of values on the Many side is unknown and can vary. So writing Power Query code to pivot this can be tricky. Let's look at an example.

The first column is a list of the office locations of my MegaGlobal Corporation. The second column is a list of the departments at each location.

As you can see not every location has every type of department, but each location has one or more departments.

What I want to do is turn my source table into this

Normally I'd be telling you to leave it in a tabular format as it is ideal for further analysis with a pivot table or in Power BI.

But if you want to create the final report in Power Query then keep follow these steps, or watch the video below.

Watch the Video

 

Download Sample Excel Workbook

My data is already in a table so next I load the table into Power Query.

Then I need to group the rows by location.

This gives the following table with a column of tables. The table in each row of the Grouped column contains the location and departments for the location on that row.

Next I need to transpose the Department column in those tables, turning rows into columns. I can use the Table.Transpose function for this.

But as I only want to transpose a single column, not the entire table, I'll use Table.SelectColumns to grab just that Department column for Table.Transpose.

Add a custom column and enter this code

I get another column of tables but this time the tables only contain the transposed Department column.

At this point you might use the GUI by double clicking the double headed arrow on the Custom column header to expand these new tables.

But if I do that, it's only going to create three new columns, so click on Load More.

Power Query checks the whole table (bear in mind it only checks 1000 rows so if your table has more than 1000 rows, it could miss some columns) and tells me it will now create five columns

Clicking OK I get my new columns.

However the problem here is that the M code has hard coded the number of columns it will create.

The Table.ExpandTableColumn function is using four arguments (parameters):

1. The table containing the column to expand
2. The name of the column to expand
3. The names of the columns inside the column to expand
4. The names of the new columns to create by the expansion

As it is, this code will only ever create five columns, so if you have more than that in your source data, they won't appear in your final table.

So how to make the code dynamic so that it will expand however many columns we give it?

Let's start by looking at how Table.ExpandTableColumn works.

The 4th argument is the list of the names to give the new columns (after expanding), but the 4th argument is optional. If you don't supply it, then the new column names will be the same as the old column names.

Let's delete the list of column names that is the 4th argument.

The code still works perfectly. I just need to do something with the other list of column names. This list of names isn't optional though, the function requires it to work.

I can use Table.ColumnNames to get the column names from the tables in the Custom column created in Step 3

However the tables in this column have different numbers of columns (which correspond to departments). Brisbane has three columns because it has three departments, Sydney has one, etc.

To make sure I create the neccessary number of columns I'll use Table.Combine to combine all the tables in that column. Where column names are identical, those columns are merged. I'll end up with a table containing as many columns as are in my source data.

Table.ColumnNames then reads the column names from this merged table.

Table.Combine is merging the tables in the [Custom] column of the #"Added Custom" table. #"Added Custom" is the name of one of the steps but as it produces a table it can be used here as the function argument.

The query is now dynamic and will pivot any number of rows into columns.

Bonus Method

Don't forget to check out the video above to see a 2nd method to solving this problem. It doesn't use Table functions, rather it uses Text and List functions so that approach might be more to your liking.