Get & Transform - solving all sorts of Excel issues

Some may see Get & Transform (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 crappy 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 Get & Transform and I salute them all. A big thank you.

Get & Transform can solve all sorts of problems for the Excel user who is wrangling with data. Consolidating, Splitting, Merging, Replacing, Filtering, "UNPIVOTTING!"... the list is comprehensive and seems to be ever growing.

Here's just one small and very unusual example. It's the "unusual-ness" 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 Get & Transform 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).

Get & Transform to the rescue....

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

2. Get & Transform > 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 Get & Transform may well be able to help you ways you never thought were possible.