another user cannot filter pivot from power query

[cheesywotsit]

i'm new to power query and the forum so forgive me for my noob question. i did a search but couldn't find anything.
im using windows 10 64bit, excel 2016 business edition.

so here's my problem...

• i have a "database" workbook which stores all the raw data that i want power query to aggregate together. it's about 20mb.
• in another workbook called "dashboard", the power query calling data from "database" is connected.
• the "dashboard" file has a pivot table with slicers, etc for a management overview. this file is light - only 1mb which is suitable for sending in an e-mail.

I noticed that external users (not connected to the "database" file) can't use the pivot table filters in the "dashboard" file. i already went to the options to turn off automatic updating but it didn't help.

is there a way to fix this or does the other users have to be connected to the database file?

thanks!

 

[Matt Allington]

Well it depends on how you have it all set up. It is possible though. Read this blog post I wrote recently and it may help.

Traditional Pivot Tables using Power Query - Can Do -

Matt

 

[cheesywotsit]

Hi Matt. Thanks for getting back to me. Cool blog.

Actually, I did setup my pivot table the same way as you described in your link. Another user can't refresh the data which is expected but he also can't filter the pivot table I use pivot slicers as the filters. Do you think it has something to do with the power query not being installed on their computer?

Thanks.

 

[Matt Allington]

I would be surprised if it is a lack of Power Query installed on the other PC. but I have never tested it so I can't be 100% sure. You could turn off Power Query on your PC and see what happens.

 

[cheesywotsit]

stupid question but how do i turn off power query on my pc?

 

[Matt Allington]

You need to go to com addins and disable it. If you have the developer tab, there is a com addin button there. If not, I have a video that explains it.
http://youtu.be/uWAaUOAt2XI

 

[cheesywotsit]

i can't seem to deactivate it the weird thing is... power query is integrated in the Data tab. i'm not sure if it's just an integrated feature for excel 2016?

i'm trying out this method at the moment: Share and Refresh Power Query with Local Links - PowerPivotPro
but having a bit of difficulty understanding it... not sure how to add a parameter. do you know how i can add a parameter in my power query?

thanks.

 

[cheesywotsit]

sorry i meant function, not parameter

 

[cheesywotsit]

found it! sorry to post so much... couldn't find an edit post button.

 

[Matt Allington]

Well that other blog post you are looking at was written by me too. But it specifically refers to times when he other user has the source files and needs to refresh them. That is not what I understood your problem to be.

Yes, in Excel 2016, you probably can't disable Power Query.