Get & Transform - solving all sorts of Excel issues

MVP

 

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.

6 Replies

Looks nice, good example based on real life case study

Great example, Wyn! We should start posting it as a quiz before providing the solution :) Maybe I'll start doing it soon myself.
Thanks for the example. I was wondering whether anyone have dealt with the Alt Enter situation when importing a large csv file? I was looking to see whether there is a way for the Get and Transform procedure to replace the end of line values prior to importing the file.

Hi @Ricardo Figueroa

 

Similar to the Split Column by LineFeed you can use Transform > Replace Values and again choose LineFeed

 

Under advanced options choose Line Feed and then put a space in the replace with box

 

Line Feed.PNG

Wyn,

 

Thanks so much for your prompt response. This works well when the information is already in Excel... but I have not been able to find a solution for the case when importing the information from a csv file without having to load it into a worksheet. 

 

In the following example, I am trying to specify at the source level how to manage the line breaks but it does not give the option to replace values within a specific field. Since this is a large file, I would like to avoid the need to import the information into Excel and then replace values (this is doable through the procedure you explained).

 

I will keep looking for a solution with Get and Transform.

 

Update. I checked  the other option for line breaks the one that reads to ignore quoted line breaks. It worked... it fixed the issue without the need to replace the line breaks with any other value.