Home / Excel / Exploring Excel Power Query

Exploring Excel Power Query

Excel Power Query

Now that I have wrapped up the ACA business, I have moved on to something new - namely some kind of analytical reporting for our owner/management. I have the initial draft done and I am now researching for more ideas - something to improve upon what I have now. One thing I found was something called Power Query. It's not new; it's been around for a couple of years, so I'm behind the ball now! That's kind of disconcerting because I usually try to grab the newest thing, as long as I know about it and have access to it.

A couple of years ago, I heard about Power Pivot but upon research, I understood that in order to access Power Pivot you had to have the right Excel version or some kind of access to the Microsoft Business Intelligence stack. I found I did not have the right Excel and therefore I did not have access. Thereafter, anything with the word Power I ignored because I thought I did not have access. Well, it turns out that Power Query is actually FREE! Maybe that's why Microsoft decided to change the name because too many people mistakenly assumed that they did not have access to the "Power" tools.

Anyway, on Friday I was testing it at work to see if we could make use of it. Power Query comes bundled within Excel 2016 (which I have at work) and is found in the Data tab as "Get and Transform". For Excel 2013 or 2010, you will have to download a free version that will then become a new tab on the ribbon. The purpose of the Power Query is to help you retrieve data, whether from Excel, tables, a data warehouse, the Internet, or wherever, and do some data clean up to make it ready for Pivot tables. One of the big plus of Power Query is that you can convert tables that have the months running horizontally into a vertical column more amenable for use in Pivot Tables. The other big thing about Power Query is that it records your action to clean the data. In other words, it retains a program which sounds like you can repeat those same actions with another set of data.
It took me a while to figure out how I could re-use the "program". My biggest error was not changing the "source". When I first created the actions, the first data was my original source. I made the mistake thinking I could overwrite that data with new data and refresh the pivot tables, thinking it would re-do the program. It doesn't work like that. What you have to do is get into Power Editor and then on the right hand side, you will see a series of actions that the program does. The first one is Source. Click on the gear wheel and it should open up into a box where you can navigate to the new file with new data. Once you have picked your new file, then you click on the next action in the program. You keep clicking on each action until you move to the bottom of the program. At least, that's a version of how I make it work. I have bought a book to see if there are other uses of Power Query and how else to use it. So maybe there is a better way.
One thing I will say, because of the structure of the industry I'm in, the vendors' software aren't quite up to par. In the last industry I came from, we used SAP and it was a very mature software which allowed you to download data into Excel in a nice, clean, usable fashion. In this industry, the downloads are atrocious. The data don't come in a neat table. The vendors' software are not professional quality; they are not even amateur quality. They are just terrible. No wonder our accountants struggle to get things done. So with the data we get, we have to do a lot more manipulations to even use it in Power Query. The Power Query will help, but we will still have to do some gyrations in order to get where we want to go.
Anyway, that's it for tonight. I'm still trying to recover from the crashing after I figured out the ACA thing. I have just been low energy and sick since then.

Leave a Reply

Your email address will not be published. Required fields are marked *