Power Query to use a PowerPivot Data Model as a source to bring in tables from an .xlsx or .pbix file
See image http://1drv.ms/1P4aUPn
This has come up a few times when working with clients. Where I have a .XLSX file with the PowerPivot Data Model inside. However I need to use that as a data source for Power Query to build a separate model. Since that is currently not possible - you can pull in Excel Sheets but not connect to the data model within the file - I have to resort to jump through a few ungainly hoops to do this. Having the ability in Power Query to simply connect to either the Sheets or the Data Model tables within a .XLSX file would be really helpful.
This is not a one-time import. This is a regular query which I need to run, where I source the data from a PowerPivot model (delivered by someone else), shape it, transform it and then bring it into a separate model on a routine basis.
It is a pity not to take advantage of the data model in Power Query
i'm looking for this feature in Power query though out Google, so it's not yet available. 1 up vote!
Andrey Minakov commented
Absolutely needed! Why we have to do workarounds while all the data are in the Excel file???
In regard to Andrews question, "Do you mean "query" the model by creating a pivot from it and then using that pivot as a source." This is what I want. I currently use a complex Power Pivot Table as my source for other reports by creating named ranges in the Pivot table and using those to create my table which works perfectly well but takes considerable work to set up. If Power Query could do this then you could use this feature to get anything you need out of the source tables by creating a Pivot table that is just a one table flat file.
D. Pearce commented
+1 and a bump if this has fallen off radar.
I developed a fairly complex PowerPivot data model that for a long time I published on a SharePoint site to use with a .bism file as a data source. Basically, the PPDM looks just like an SSAS cube (which I also integrate with).
The complexities of my corporate network made this model unworkable long term, as well as disallowing PowerPivot Automated Refreshes (so, yes, I manually update daily still). So I moved onto Power Query.
For my latest project I had to write entirely new SQL queries to summarize the data that is essentially in that existing PowerPivot data model.... It would have made my life so much easier to simply have referenced that other model than to have to rewrite in SQL, do all the validation necessary to have all the re-sliced data tie out to the existing PPivot reports, and be updated with any maintenance updates on the existing model.
Jonas Halldén commented
Looking forward to this one!
Great idea. Thanks.
Bruno Crotman commented
MUST HAVE! In my opinion, his is the most useful tip in this list.
Alex Woodrow commented
Yes absolutely must have. If we want to use PowerBI for client solutions we need to be able to run a query on a data model that is created from another set of data.
Luis Fonseca commented
I why this isn't a possibility yet? Let's imagine we have two different .xlsx files (with PowerPivot Data Model inside) that we need to compare. With this feature I wouldn't need to create a pivot table on each .xls with ID on both sources, copy it to a new spreadsheet and then do the comparison on the new spreadsheet.
It would be simply have PowerQuery to connect to the .xlsx powerpivot's data model, create a query for each .xlsx, load it, and compare. No copy & paste, only one .xlsx open instead of 3, a lot easier to work with 1m records dataset on each file in my case...
I download to Power Query spreadsheet containing the levels of hierarchy in one column and the name of the levels in the other. I get through the PowerPivot real hierarchy and need to download it back to Power Query. Function of download data from Power Query to the PowerPivot greatly help in such procedures.
Andrew Ashurst commented
I've voted so I think this would be a useful feature. I just wanted to ask Tim to clarify his comment that "In Excel you can query the model and create a table in the Workbook". Do you mean "query" the model by creating a pivot from it and then using that pivot as a source. Or, do you mean use Power Query to query the model and put the result on a worksheet in the workbook (which seems to be the functionality being requested). O, something else?
Fábio Ávila commented
This would be really useful! I don't understand why this idea doesn't have more votes.
Imke Feldmann commented
This would be awesome. We could create staging tables or repositories with cleaned data outside a SQL-server that could centrally be accessed.
This would not only speed up the subsequent queries but also reduce the requests/stress on the operational source databases.
Tom Allen commented
This would be a good feature to help with unioning >1m row tables that each have different database refresh frequencies.
If you try to union them directly in the same workbook, they get automatically refreshed.
A way to prevent this is to use temporary workbooks to stop the auto-refresh, but that only works for <1m rows, since you currently can only connect to worksheets and tables not the Data Model.