I have loaded a big report (5MM rows) into Excel's data model using PowerQuery. Now, with this data on the data model, i added some DAX formulas on powerpivot. With that said, i apply some filters on the formulas and trim down the number of rows to 300k.
The question is - is there an easy way to "export" this view into another excel file, or even, is it possible to use this table easily?
Copy and paste won't work, and i can't filter using PowerQuery because the rules are actually on the new calculated columns, so they won't show there. For example, if i could create another table from a view in PowerPivot, then i could use the Excel's Data>Get External Data>From Existing connections Right now, if i do it, it will (try to) pull the 5MM rows and i still can't filter for the ones i want.
In the meantime, i'm copying and pasting in batches of 100k rows - works but i wanted a more elegant solution if possible :)
Thanks a lot
You may not experience any issues at all with 5m records. It depends on the data and your machine resources. If you do there are a number of options:
1) pull only the source fields that you need for your calculations into your workbook. Create your calculated columns and create a flattened pivot table with your data. Create a new Power Pivot model and use Power Query to pull the bulk of your data from your original source and merge it with your flattened pivot table.
2) write out your 300k rows to csv with VBA and re-import it into a new workbook. See here.
3) deploy the large workbook to SharePoint (if you have it). Then you will be able to use it as a data source for another Power Pivot model.
4) restore the large workbook to a tabular instance of SSAS (if you have it). Then you will be able to use it as a data source for another Power Pivot model.