



May 2, 2014

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.


July 16, 2010

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
1 Guest(s)
