Power Query Data Cleansing – solving all sorts of Excel issues

by Wyn Hopkins

 

Some may see Power Query simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power Pivot or Power BI desktop).

Having spent 25 years using Excel / Lotus 123 functions and macros to clean up messy data and automate manual tasks I find it is so much more.

It is a breath of fresh air.

There are lots of great people out there giving great advice on how to get the most out of Power Query and I salute them all. A big thank you.

Power Query data cleansing can solve all sorts of problems for the Excel user who is wrangling with data. Consolidating, Splitting, Merging, Replacing, Filtering, “UNPIVOTING!”… the list is comprehensive and seems to be ever growing.

Here’s just one small and very unusual example. It’s the “unusualness” that prompted this article. It hopefully goes to show that if there’s a problem that you don’t know how to solve using “normal” Excel then Power Query data cleansing may well be the answer.

The Problem

A table of data had been entered in Excel but rather than a unique row being used for each value the user has entered data using the Alt+Enter trick to transfer data onto the next line in the same cell.

This makes it impossible to create totals (OK not impossible but you need an array formula and some “out-there” thinking to get it done).

Power Query Data Cleansing to the rescue….

1. Turn the original set of data into a Table (highlight and Ctrl+T)

2. Power Query > From Table

3. Then click on the Split Column > Split by Delimiter

And select the Custom Option followed by ticking the box Split using special characters and pick Line Feed (this picks up the Alt+Enter separator)

(note I’ve put 100 columns to split, this allows for 100 items to be entered in each cell, there are better but more complex ways of doing this)

4. Right Click on Types of Uniform and Unpivot Other Columns

5. Right Click on and Remove the Attribute Column

6. Right Click on the Types of Uniform and select Group By…

7. New Column name = “Total Sold” Operation = Sum Column = Value

8. Click Home > Close and Load To…. then select table and load the data into Excel

Done! Simply Right Click and Refresh the green table whenever required

Ideally, we’d avoid all this and explain to the user why using Alt+Enter, in this case, is not a particularly good idea.

The aim of this unusual example is just to get you thinking that Power Query may well be able to help you ways you never thought were possible.