Power Query: Row Together Part 2

Welcome to our Power Query blog.  This week, I look at alternative ways of displaying the combined rows.

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.  Last week, I started with a simple scenario.

The task was to create a row for each Salesperson containing all the regions.  The end result looked like this:

Before I move on to testing the solution, I would like to point out how easy it is to change the format of the solution to this: 

If I go back to my original query SalesRegions:

I can use the cog icon next to the 'Split Column by Delimiter' step to change the settings for this step:

If I open the 'Advanced options' I see more settings:

Notice that I have the option to 'Split into' Columns or Rows.  If I change this setting to Rows, I have all the region data in one column:

This solution has another advantage.  To show this, I will create 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 will call them SalesRegionsRows and SalesRegionsColumns.  I load both queries to the Outputs worksheet:

Now, I will change the data on the Inputs worksheet:

I have added a row for Mary.  Let's see what happens when I refresh the queries.  SalesRegionsColumns has a row with a space, but all the data has been returned:

From this testing, I know I need to filter out spaces from the Region column.  Now, let's look at SalesRegionsRows:

There are still only seven [7] columns, which means that the new data is missing.  Next time, I will look at why this happened and how to fix it.

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