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.
You must be logged in to post a comment.