Power Query: Row Together Part 9

Welcome to our Power Query blog.  This week, I continue to look at how to make my query from Part 2 more robust.

Often when using Power Query to solve a problem, there is more than one way to attack it.  This is true when it comes to combining multiple rows to make one row.  The solution will depend upon the requirements of the data and how the rows are connected.  In Part 1, I started with a simple scenario:

In Part 2, I created two [2] versions of the query, one that outputs the region data to one column and one that outputs the region data to multiple columns.  I called them SalesRegionsRows and SalesRegionsColumns.  I loaded both queries to the Outputs worksheet.

I then changed the data on the Inputs worksheet:

I added a row for Mary.  When I refreshed the queries, SalesRegionsColumns had a row with a space, although all the data was returned:

In Part 4,  I grouped the data in SalesRegionsColumns and ensured that the region data was in alphabetical order:

Last time, I discover that the requirements have changed and there can be more than two [2] region columns:

I created a new Table SalesMoreRegions.  I also created a copy of the query SalesRegionsColumns called SalesRegionsColumns_Copy which used SalesMoreRegions as the source.  I created a duplicate copy, which meant that the steps after the source step were the same in both queries:

There were no errors but there was a problem in the 'Merged Columns' step:

The column names Region 1 and Region 2 appear in this step:

I need a solution which does not hard code the source column names.  Unfortunately, there is not a function that will allow me to 'merge other columns' but there is another approach I can take.  I am going to insert a step after the Source step.  I select the Salesperson column, right-click and choose to 'Unpivot Other Columns':

I receive the standard warning about inserting a step:

I choose to Insert and view the results:

I don’t need to delete the Attribute column, as this will be ignored by the 'Grouped Rows' step.  I could either rename Value to Region now or change the name in the 'Merged Columns' step later.  I choose the latter.  I can reference the name Value without causing any issues because the Attribute and Value columns are always created by an unpivot step.

I delete the 'Merged Columns' step as I have replaced this with the 'Unpivoted Other Columns' step:

I ignore the warning about deleting a step:

As expected, an error occurs as the name of the column has changed:

I need to change the M code that mentions Region:

to use Value instead:

The query now works with any number of region columns:

Since unpivoting also organises my data in the same form as the current 'Filtered Rows' step, I can further simplify the query by removing 'Grouped Rows', 'Split Column by Delimiter', 'Changed Type' and 'Filtered Rows'.  I ensure that the Value column is referenced rather than Region:

The query now comprises of fewer steps and is more robust.

Come back next time for more ways to use Power Query!