21 November 2018

  • Merge queries (Power Query) Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 When you merge, you typically join two queries that are either within Excel or from an external data source. In addition, the Merge feature has an intuitive user interface to.
  • A question came in from someone who wanted to know how to merge rows of data into a single cell. Imagine you have a person's name in one column and then deta.
  • Learn how to combine files in a folder when the data in the files has inconsistent column headings and table names. 👇Check out my full courses and ebooks he.
  • Power query is a data shaping tool where we can bring the data through a connection from various sources, do some shaping such as removing rows, columns, changing data types, trimming the data etc. And get it ready, finally for the analysis in our main software such as microsoft excel or microsoft power bi. For the introduction of power query.

Welcome to our Power Query blog. This week, I look at merging columns to show that not all ‘Merge Columns’ options are born equal.

NumberColumn gives a list of the values in the NumberColumn column in this new table. The List.Transform part turns the numbers into text values, and Text.Combine joins those numbers together, with a comma separating each value. If you need the surrounding quotes as well, you can do this.

There are currently some problems that can occur when merging columns. To demonstrate, I will use the following subset of my tent data:

I want to create a new column which will contain all of my dimension data.

In the ‘Transform’ data tab, I can choose to merge columns. First, I must select them, which I can do by holding down the CTRL key as I click on them.

I have selected all the columns containing dimensional data, then I choose to merge:

I choose to separate my data by commas, and I call my new column Dimensions.

I have several problems here:

  1. My original columns have disappeared
  2. The data is not in the order I would have expected
  3. There are extra commas.

The first problem could be resolved by copying my columns before I merge them:

In the ‘Add Column’ tab, I can create a duplicate of the selected column. I have to do this one column at a time, but having created the duplicates I can select them instead of the original columns.

Surprisingly, there is an easier way – I can merge columns from the ‘Add Column’ tab instead:

I can merge columns here too, but the process behaves slightly differently.

This part looks the same, I pick the separator and the name of my new column.

However, this time I get to keep my original columns. Confusing, but useful when I know that ‘Merge Columns’ on the ‘Transform’ tab deletes the original columns, whereas ‘Merge Columns’ from the ‘Add Column’ tab keeps the originals.

They are still in the wrong order though, and now I can see my original columns, I can work out the order. The merge is constructed in the order in which I select my columns. I selected colour first and worked backwards, so my merge does too. I can rectify this by selecting my columns in the order I want them to merge.

Power

Having selected Length, I can select my remaining columns in the right order by holding down the SHIFT key and then selecting colour. This is quicker and will ensure the order is right.

Having created my merge (from the ‘Add Column’ tab), I check the order again.

The order is correct, and looking more closely, my last problem (extra commas) has also been resolved! This is because there are more differences to the way the ‘Merge Columns’ option on the ‘Add Column’ tab works compared to the ‘Transform’ tab. To show the difference in the results, I will merge the columns from the ‘Transform’ tab:

I give the column a different name so I can see what is happening.

Combine

The ‘Merge Column’ from the ‘Transform’ tab does not cope with the nulls, but the ‘Merge Column’ from the ‘Add Column’ tab does!

The answer to this discrepancy is in the Advanced Editor, where I can see what each ‘Merge Columns’ has done in M.

Firstly, consider the ‘Transform’ tab version:

#'Merged Columns' = Table.CombineColumns(#'Inserted Merged Column',{'Length', 'width', 'height', 'volume', 'temperature', 'colour'}, Combiner.CombineTextByDelimiter(',', QuoteStyle.None),'Dimensions (from Transform)')

This version of the merge has created a new column using the M function Combiner.CombineTextByDelimiter. Clearly, this function does not cope with nulls.

The ‘Add Column’ tab version has used a different M function:

#'Inserted Merged Column' = Table.AddColumn(#'Changed Type', 'Dimensions', each Text.Combine({[Length], [width], [height], [volume],[temperature], [colour]}, ','), type text)

This time, the M function used is Text.Combine, which does cope with the nulls.

This does leave the question of whether the ‘Add Column’ merge column feature will cope equally well with numerical columns…

The answer is yes, the price is included too, and looking at the M code, I can see why:

= Table.AddColumn(#'Changed Type', 'Details from Add Column', each Text.Combine({[Length], [width], [height], [volume], [temperature], Text.From([Price], 'en-GB'), [colour]}, ','), type text)

The price is converted to text so that it can be combined with the other columns.

In summary, if merging columns, it’s currently best to do so from the ‘Add Column’ tab. It keeps the original columns and copes with nulls. This is correct at the time of writing, but I wouldn’t be surprised to find that the ‘Transform’ tab is soon updated to use the same functionality for the ‘Merge Columns’ option. I still have to remember to add the columns in the order I want to merge them, but allowing that functionality means that I don’t need to reorder my columns if I do need them to be merged in a different order.

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

I had to tackle an issue this week that occurred when I refreshed one of the queries contained in a quarterly report. Here’s the scenario..

The source data for the query was simple and had only a few columns:

  • customer
  • some customer attributes
  • index with associated percentages

The Index column was created by numbering rows after grouping by customer (Shout out to this Excel Guru blog post for helping me get to this point.)

A clean version of my source data:

I needed to manipulate this data before landing to the workbook. My first objective was to pivot the Index column and fill with the values from the Percentage column.

Multiple

This worked with no issue. Then I needed to merge all of the Index columns to create a column containing a comma separated list of all the percentages.

The first step to achieve this was to change the column types to text. Notice how all four of the column names were hard coded in this step.

Columns

After I changed the type, I selected the columns to merge by a comma separator. Again, notice how the names of the columns to merge were hard coded.

After some additional clean up, this landed the desired outcome to my workbook.

This was all well and good, until it came to the next quarter..

Every quarter, the source data for my query is refreshed with new customers. The most important change in the data to highlight is that the maximum number in the Index column is now 3. Last quarter, the maximum value was 4.

This caused the following error to occur in my query when I tried to refresh.

The #”Changed Type” step was looking for column 4, which no longer exists! This was easy to fix, as I could simply delete the {“4”, type text} clause from the list of columns to change.

But it errors again!

Once again, column 4 was hard coded in the combine columns function. My entire query is outlined below:

I knew I needed to modify the query to dynamically define the columns to change type and merge. I came up with the following solution.

Dynamic(ish) Solution

My first attempt at fixing the initial query was definitely more dynamic than what I started with. I was happy with it for about six hours until I changed it again. I outline the fully complete query, that I’m happy with (for the time being..), in the third section below. But for full transparency, I wanted to include this intermediate step.

The key step to fixing the query was inserting a step immediately after pivoting the index column, named #”Column List”. This step removed the customer and customer attribute columns, leaving only the index columns.

As I explicitly removed the customer columns, this returned all of the index columns. It will do so regardless of how many there are quarter to quarter.

The key assumption here being that we will forever and always only need to remove the three columns hard coded into the remove columns step

.

.

.

(Spoiler: this won’t be the case)

My next step was to change the type of all of the columns defined in the previous step. I found a neat solution in this blog post from the BI Accountant.

The important points that allowed me to do this are:

  1. Table.ColumnNames() – this function returns all the column names in a table as a list. I called this on the table from my previous step which listed the names of all of the index columns.
  2. List.Transform() – this function has two arguments: a list and a transformation function. It takes each item in the list and transforms it with the function in the second argument.

Multiple Columns Into Single Column Excel

I nested these two functions together within the #”Changed Type Dynamic” step. The data for this step is actually from the #”Pivoted Column” applied step (one before I defined the #”Column List”).

Power Query Combine Columns Into Date

I called Table.ColumnNames() on #”Column List” to create the list of columns I wanted to transform. This was the first argument in the List.Transform() function.

The second argument in List.Transform() was to change each column to type text.

Excel Power Query Concat Text

Query

Very nice!! Now I had to merge all of the index columns. I achieved this by once again using the Table.ColumnNames() function on the
#”Column List” step.

Power Query Combine Columns If Null

Again, very nice! This worked well and refreshed correctly with the new data.

However.. My query would not work properly if additional customer attributes were added to the source table:

Power Query Combine Columns To Create Date

This would result in incorrect data being included in the #”Column List” step. It was particularly problematic with a numeric column like “Number of Orders” being included, as it is hard to distinguish from the percentages that fill the index columns.

Dynamic Solution

Power Query Concat

To make my solution fully dynamic, I had to change how I was defining my list of index columns to change and merge. To achieve this, my first step was to demote headers after the initial pivot of the index columns.

Then I removed all of the data except for the top rows. This left me with a table with one row: Column1 to ColumnN, containing column names in the first row.

My next step was to transpose the table. This resulted in a table with one column containing a row for every column name.

Then I changed the type of the column from any to whole number. I knew that the index column names will always be a number. I also assumed that none of the other column names would be able to be parsed as a number.

My next step was to remove the errors and transpose the table back.

Now I had a fully dynamic way to define the #”Column List” step, no matter what the other columns in the source data were! My final query is outlined below:

Coments are closed

Most Viewed Posts

  • Ssl_get_cipher_list
  • Openssl Source
  • Drive File Stream Does Not Open

Scroll to top