Well, here's my dilemma. I was working on a deferred income project - got it working perfectly with about 3 sample customers. Now the user wants to use 5 years data with a few hundred customers. The problem is that when I use PQ to generate a day line for every invoice and customer it is way too big for Excel. So my question is this. How can I do the merging in Power Query but feed the result into either an Access database table or a Powerpivot file that I can then use? Have attached a file with sample data - obviously not the full data set...thanks.
In the Starting point sheet I was able to unpivot other columns from Column 1 (J) on wards for 1 year - but if I do 5 years...way too big. Thanks.
Hi Anne,
Create the query as a connection only. If you have Excel 2013 or 2016 you can Close and Load to > Connection Only and check the box to Add to Data Model (Data Model = Power Pivot).
If you have Excel 2010 then you need to get Power Pivot to connect to the Power Query. I cover this in session 5.04 of the Power Query course.
Mynda
Excellent. I am using Excel 2016. Presume I need to create the Powerpivot data model first...and then pull it in...thank you 🙂
Hi Anne,
To be clear, you don't need to create the data model first, just load your query to the data model and it adds a sheet/table for your query.
Mynda
