The company I work for recently bought a part of a company. Since we were not buying the whole company there were all kinds of complications. One of them was that the historical sales was delivered to us in Excel files as we didn’t get the ERP system. One of my tasks was to read the data into a staging database using SQL Server Integration Services. We got Excel files in many different formats as we got one from each sales region as well as the main company. One such file had a format that caused us some headache. The format was as below
Our procedure was to read the file into SQL Server and then manipulate it. But in this case there was a problem with the empty rows in the Customer and Product Group columns. First instinct was to use native Excel to fill in the blanks. This can easily be done by either copying and pasting or by double clicking on the corner of a cell to copy its content down. The problem with that is that the file was thousands of rows of data and as you can see in this small sample above there are huge amount of cells to fill down. Excel also has something called Flash Fill but that didn’t work as we wanted. The next thing that came to mind was to write some VBA code to do it. This is probably easy for a skilled and experienced VBA developer but there was no such available.
Then it came to me. Power Query / Power BI has a Fill transformation that fills either up or down in a column. So the problem was solved in 2 minutes. As I imagine this might be a common problem I decided to share my solution. My solution is done in Excel 2016 but the same applies for Excel 2013.
First I defined the data as a table by using Insert -> Table. Make sure a cell inside your table is selected and that My table has headers is checked
My data looked like below after I inserted the table.
Then I loaded the data into Power Query using Data -> From Table
Excel then opens the Power Query – Query Editor
As you can see it looks like my table with null where there are empty values. Here is where Power Queries magic comes in. One of many built in transformation is the Fill transformation. You can find it by either right clicking on the header of a column or by placing the focus on the column you want to transform and use the Transform menu were you click the small down arrow next to Fill.
By pressing the Down option on the Fill menu Power Query fills values down to the next value overwriting only nulls.
By repeating this for Product Group I get the following result which is exactly what I’m looking for.
Now I just need to load this into Excel again by using the Close & Load on the Home menu. This automatically writes the results into a new Excel sheet.
I love when there is a simple solution to a seemingly difficult problem and specially when I get to use something from my BI tool belt for something unexpected.
Ásgeir