Get & Transform to the rescue!

Is it a bird? Is it a plane? No it’s way better than that, it’s Excel!

Get & Transform, also known as Power Query, gives Excel users super powers.

Here’s the latest scenario where it has helped out and also is an opportunity for me to demonstrate a method for referring to an item in the next row of data in the Power Query editor.

In Excel if you want to refer to the next row you just do =A2 or =B3 etc. However, you can’t do that in Power Query (sorry Get & Transform). But there is a way…..

This is the Scenario:

Crappy source data, in a terrible structure for analysis…

This is the Aim:

Nice clean data

This is the Method:

In a new workbook

Data > Get & Transform > New Query > From Workbook

Browse for the Excel dump with the crappy data in it, and pull that data into Get & Transform

Filter out the blanks and nulls

Referring to the next row

As you can see, the $ value we want is on the row after each name and address line

I want to pull that value up to be on the same row as the name and address line.

There are many approaches to this, here’s the 2 step process I used:

Step 1: I added an Index Column

Step 2: I added a Custom Column and typed this line of code

#”Added Index”[Column1]{[Index]}

(note [Column1] is just the column name I’m referring to, and #”Added Index” is simply the name of the previous step)

So now we have brought the item from the row below up onto the same row 🙂

From then on it’s plain sailing, just removing every 2nd row using the remove rows > alternate rows button

Splitting out the Totals: $ (using the split column functionality and using $ as a delimiter)

And finally splitting out the Description column, which happens to be separated by Line Feeds (Line Feeds are the sort of things that appear when you type in Excel and use Alt+Enter to start a new line)

Load to Excel and you’re done!

Just update / overwrite the source file with the latest crappy data and simply Right Click Refresh on your green table and awesome clean data is instantly available!

Get & Transform is Super!