In any case, I think we are only scratching the surface with respect to how much our lives will be affected by this tool and I am excited to see it mature and get widely adopted. I know it’s kind of a confusing explanation and I should probably do a new post with screenshots, but the bottom line is that now there is a way to change the type of data that is used for Power Pivot and it will definitely come in handy for me. I just tried to create a Power Query against a web page, used it for Power Pivot, then renamed the connection, created a new connection with the original connection’s name but now against a CSV file and with some hacking around got it to source the same tab in Power Pivot but now effectively pulling data from CSV and not from Web. It could be a pain in the neck because dropping a tab in Power Pivot drops all of the logic (calculations, relationships, etc.) with it. For example if my initial load of data comes from a web page but then I decide to load the data (in the same format) from a CSV file, I have to essentially drop the power pivot data tab and reload it from a new Power Pivot connection. I have a feeling, from now on, I will start using Power Query exclusively as my data connections for Power Pivot as it will give me greater flexibility in case I need to switch out my raw data sources. Now, it seems, I find more and more uses for it. I do a lot of POCs and I started using the tool primarily to massage formatted reports (Income Statements, etc.) into an un-pivoted easily consumed data sets.
Had I known that trick before, I would not have to delete half the files from the folder and my guess is that the load time would have been cut in half as well.Īnother interesting observation, if this Power Query connection is used for a Pivot table, then right clicking on the pivot table and selecting Refresh, executes the Power Query on the data source and refreshes the data in the table. This is a much more elegant solution as it does not require the intermediate load of all the data into an Excel table. Or, one can go to Power Pivot, use Existing Connections option as well and then use that Power Query connection as source. Now, we can either use it to create a regular pivot table by clicking on Existing Connections in the Data section of the Ribbon and then picking the right connection from the listĪnd then selecting Pivot Table or Pivot Chart However, apparently, it is totally fine to have both of them unchecked, in which case (well, I guess rather in any case), the Power Query is saved as a Workbook connection and can be accessed by clicking on Data->Connections
In one of my previous posts I demonstrated how to use a web page as a Power Query data source.Īs I went through my Power Query design, I would always either have Load to worksheet or Load to Data Model checked. Yesterday, I was shown a much better way.
The only work around that I found was to limit the data set to about half of the files, load data to worksheet and then use Linked Table feature of Power Pivot to add it to my model. To my disappointment the Load to Data Model option that I was accustomed to in Excel 2013 was not available in Excel 2010. The unfortunate thing when I started going through the same exercise with a customer was that the client only had a 32-bit version of Excel 2010 (and frankly, I have to say that probably 90% of enterprise customers still fall under this category). I have been using them in Excel 2013 for a while and one of my favorite features was Power Query’s ability to source files out of a folder, concatenate them together and then load them into a Power Pivot bypassing 1M row limit in Excel. Power Query and Power Pivot are the only two Power BI features that are actually made available in Excel 2010 (i.e., they don’t require Excel 2013). There was, however, one little thing that left some bad taste in my mouth when I tried to apply the tool in a real life enterprise scenario.
Even though on one hand sometimes I feel that the tool will only appeal to a very specific type of power users, on the other hand, I can’t help but feel that the tool is so good that I wonder if it has a potential to dramatically redefine where we typically think power users world ends and the world of IT begins. I have to admit, I like Power Query more and more every day.