Feb 15 2017
- last edited on
Jul 12 2019
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.
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.
Feb 21 2017 09:57 AM
Jan 06 2018 11:37 AM
Jan 07 2018 04:29 PM
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
Jan 08 2018 02:20 PM - edited Jan 08 2018 02:24 PM
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.