If you have Excel for Office 365, Excel 2016, or Excel 2019 then you should have the Get & Transform feature available. To see if your version has it, just click on the data tab and see if the ribbon has the words Get & Transform Data. It should look similar to this, although it can differ depending on Excel versions. In Excel 2016, this feature was originally called Power Query.
This is an amazing feature in Excel that you will definitely want to explore. It is a business intelligence tool that allows you to import millions of records from a variety of sources, clean and transform them, or reshape the data as needed. As you perform these steps, Excel records all the steps. This recording code, called M Code, operates behind the scenes, so you do not have to know anything about coding. The interface is easy to understand and use.
So, if you are frequently importing data, and then spending time cleaning, combining or extracting data, adding calculated totals, and using VLOOKUPs, then you are going to be so very excited at how much time this feature is going to save you. Get & Transform also allows you to combine multiple files together, either by appending them, or merging them together into a single table. So, set your query up once, refresh it, and you are done. It will be a huge time-saver for you come month end. Quarterly reports- set them up once and then just refresh. Think of all the time you will have for something a bit more interesting then compiling reports.
Let’s go through a quick example.
To import the data from a saved file:1. Click on the data tab. 2. Select from text. 3. Locate and select your file. 4. Click import. 5. Your data displays and shows the delimiter used. If the file is large, it may not bring in all the data.
If you do not want to clean or transform your data, you can click load, and it will take the data and put it directly into an Excel worksheet. We are going to transform our data first though.6. Select transform data. This takes the data and displays it into Power Query editor.
Power Query editor’s ribbon and right-click menus are very similar to Excel, which is what is so great about the feature. It is taking database features, incorporating them into Excel, a familiar program, and making it easier for people to understand and use.
You have a variety of options to transform your data, as well as the ability to remove errors and duplicates. Take some time and play around with all the choices. Once you have transformed the data, you close and load it into Excel.
What I want to call your attention to, is the right side of the screen, which is called Query Settings, and there you see applied steps. This is the where the genius part of Get and Transform lies.
Power Query editor records every step that you make. The applied steps dialog box shows every action that I took. If you look, you can see that I removed columns, cleaned the data, and then removed additional columns.
Here is the exciting part. I only have to do these steps once and Excel’s Power Query editor will remember all these steps, and apply them the next time you refresh your data.
This could be a huge timesaver at month end. So, next month if the data was updated, all I would need to do is open the file and go to Power Query editor and it would refresh the data, and then go through all these steps, without me having to do anything. The other key feature of the Power Query editor is that you are not impacting the actual data, but rather seeing a reflection of it. So, when you delete a column or data, it is not gone from the original file, just from the display.
Again, a very cool feature that I have just touched upon here. If you have the Get & Transform feature, definitely check it out.