Controlling Columns in Refreshable Table - Interaction with PQ

Cool blue

Board Regular
Joined
Dec 1, 2013
Messages
199
I have a PQ query which I load to a table and add to the data model in excel Power Pivot. I also have a collection of pivot tables that I project the data model on to.

In Power Pivot, I set some of the columns to be hidden from the client.
In excel, I change the order of the table that the query is loaded to and I delete some of the columns.
I also change the Table Properties to control what happens on refresh.
In this way, I can successfully control the projection of the query on to the spreadsheet surface.
If I later add or change columns in the query, or if I add back a column I deleted in the spreadsheet table, the changes are not reflected in the refresh. And more often than not, power pivot throws an error and disappears from the ribbon.

How is this supposed to work?
The only way I can get it to work is to delete the table and start again.

It's 2018 right? Why is excel still so unstable?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top