Data refresh from Power Query to Power Pivot

Any topic that doesn't fit in elsewhere
Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

Data refresh from Power Query to Power Pivot

Postby Steve_D » Fri Aug 05, 2016 2:59 pm

I'm wondering whether I have built my data model incorrectly. I use Power Query to import a series of excel spreadsheets. I then output the result of that query as a table in the spreadsheet where the Power Query query is written. I then have another spreadsheet where I combine other master tables and the power query into a data model and from there manipulate the data using PowerPivot... it all works.

However, my frustration is when I want to update the pivot table after an edit or adjustment to one of the underlying tables I have to refresh the Query, the model and the pivot table. This doesn't seem right.

A couple of questions, they are;
1. Should I have built the query in the power pivot model and then used the table as a linked table into a model all in the same spreadsheet?
2. if i did, when I press the pivot table refresh key will that refresh all underlying models, queries and tables? and;
3. I have 32 bit excel and the query and model in separate spreadsheets are creating memory issues/crashes. Will consolidating everything into one spreadsheet increase the memory drain?

MattAllington
Posts: 846
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Data refresh from Power Query to Power Pivot

Postby MattAllington » Sun Aug 07, 2016 12:36 pm

I think you are using Excel 2010, right? You definitely have 1 layer too many. You should write your Power Queries in the same workbook as your data model. This eliminates the need to open the interim workbook to refresh it. Set the query to "create connection only" - do not load to a table. Then go into Power 0ivot, existing connections and import the data to power pivot there. But a word of warning - any changes to your Power Query will require a reimport into Power Pivot. So try to get it right the first time. Also consider storing your measures in a disconnected table called myMeasures or similar. If you put your measures in a Power Query connected table and you neede to change the Query, it will be a lot of work and rework.

Putting everything into a single workbooks should not make the memory issue worse.

MS released a memory increase for 32 bit recently https://support.microsoft.com/en-us/kb/3160741

There is also an unsupported memory patch that is reported to work well. http://ntcore.com/4gb_patch.php
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

Re: Data refresh from Power Query to Power Pivot

Postby Steve_D » Sun Aug 07, 2016 5:56 pm

Thanks Matt, I'll give it a try and post back.

Steve_D
Posts: 50
Joined: Sun Jul 31, 2016 2:29 pm

Re: Data refresh from Power Query to Power Pivot

Postby Steve_D » Mon Aug 08, 2016 2:11 pm

Well wasn't that fun!

OK, I copied the queries from the Power Query file to the modal file using simple copy and paste procedure. all good.
Then, I copied each of the calculations back to the model spreadsheet so I at least had a copy. All good. I deleted the old query reference from the model and added the new 'Load connection" query into the model... all good. I then recopied each of the calculations back into Power Pivot calculations tab and then the wheels fell off. For some reason, the new query failed to bring across a field which the calculations relied upon. After much refreshing, retesting of the query to establish what was wrong and nothing worked I just deleted everything and started the process again and this time it worked like a dream... why?

The long and short of it is that it eventually worked. Thanks for the advice on copying the formulas, that would have been a disaster if i hadn't copied them.

MattAllington
Posts: 846
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Data refresh from Power Query to Power Pivot

Postby MattAllington » Mon Aug 08, 2016 8:36 pm

From experience, your missing column was probably caused by you failing to set the data type in Power Query for that column. If you leave a column as "any", then it won't come across. The PQ experience in 2010 is pretty ordinary, because PQ and PP are both plugins. It is significantly better in 2013 and even better in 2016 and PD.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “General”

Who is online

Users browsing this forum: No registered users and 1 guest