Get & Transform to the rescue!

/, Excel 2016, Power Query, Productivity/Get & Transform to the rescue!

Power query get & transform

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…

Source data for get & transform

This is the Aim:

Nice clean data

Clean data with get & transform

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

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

Index column

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

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

Custom column

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

added index from get and transform

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

alternate rows

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)

line feeds

Load to Excel and you’re done!

Excel

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!