Power Query: Emerging from Merging

Welcome to our Power Query blog. This week we look at extracting from merged Excel cells.

I have some data from John, one of my imaginary salespeople:

John has almost followed the expected format, but he’s decided to merge the date cells instead of using auto fill. I need the date on each row. I begin by extracting the data to Power Query by using the ‘From Table’ option on the ‘Get & Transform’ section of the Data tab.

I accept the defaults.

I can sort out my data by using ‘Fill Down’ which is available from the ‘Transform’ tab or if I right click on a selected column, viz.

I select ‘Fill’ and ‘Down’.

The dates are now populated correctly. However, there are other ways that John likes to merge cells in Excel:

In this case he’s merged ‘Contact 1’ and ‘Contact 2’ for two the dates, as well as merging the rows. I extract this data again into Power Query:

I can see that the Contact 2 column is currently redundant, so I will remove it using the ‘Remove Columns’ option on the Home tab and create a new one.

I can now fill down as before.

I need to split Contact 1, so I use ‘Split Column’ from the ‘Transform’ tab.

I split by delimiter, but I need to create two new columns, not three, so I won’t split for every occurrence of SPACE.

I choose to split at the ‘Right-most delimiter’.

Now I need to remove the ‘and’ from the names in Contact 1.1. There are several ways I can approach this. I can split the column again and delete the column with ‘and’. Another way is to create a custom column. In this example, I will use ‘Add Column by Examples’ on the ‘Add Column’ tab to see which method Power Query applies.

After only one example, Power Query has opted to transform the column so that only the characters before the SPACE delimiter appear:

I remove the column I no longer need and rename the contact information. I also reorder my data.

I now have the data in a standard format so that I can append it to data from other salespeople.

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