0

I unioned two tables (AccelerateData and Adjustments) in PowerQuery and loaded them into the data model (table name in the data model is AccelerateData). Afterwards I created a pivot table from the data model to analyze it. I want to make the data model accessible to other users as well and therefore I'm doing some tidying up.

In PowerPivot there is the option to "Hide from Client Tools". I have done this for the non relevant PowerPivot for columns and tables. However, I always see the source data tables from PowerQuery in the field list of the pivot table and can't find out how to hide them.

Anybody knows how to not show them in the field list?

enter image description here

Thank you very much!

Stephan
  • 650
  • 7
  • 16

3 Answers3

1

I don't see a way to do exactly what you are asking under the All tab, but you can easily hide (or unhide) any sources from the Active tab with just a right-click and Remove from (or Show in) Active Tab.

In my opinion, All really should show all dependencies rather than some arbitrary user defined selection with the Active tab being used for customization and this seems to be how Microsoft built it.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thank you. I decided this to be the best way for me too. Anyhow, I think a feature to hide the source table in general would be still valuable, since I'm supplying the model as a ready to use tool to quite unexperienced users and the less options they have, the better. (They could still decide to just try whats hidden behind the All-Tab and don't switch back because it offers them more options, without understanding that the customer field in the source table is not the same as the customer field in the final table). But as I said: Probably this is the best way to do it know. – Stephan Jul 29 '16 at 06:09
  • I can sympathize, but it seems like if they're inexperienced, they shouldn't be messing with the fields in the first place. Wouldn't you already have the layout, filters, and slicers needed already set up? – Alexis Olson Jul 29 '16 at 06:12
  • No, I predefined multiple KPIs they want to use (up to 20 KPIs) plus the data has a high dimensionality and they want to look at it from different angles. They are used to work with standard pivot tables, but not with PowerPivot. Therefore I tried to build a very stable model and provided them with one easily adjustable pivot table they can modify in the given parameters. (a.k.a the ones I didn't hide) It's also kind of an experiment. If they won't manage I'll switch back to a classical dashboard. – Stephan Jul 29 '16 at 06:20
0

After hiding them using the "Hide from Client Tools" option in Powerpivot, you also have to right-click and refresh your pivot table. That should hide the fields from the pivot table fields list.

Jeremy Firth
  • 41
  • 4
  • Thank you but this is not the answer. The tables I marked as hidden are successfully hidden. My issue is with the source tables from PowerQuery, which I can't see in the PowerPivot window/model and therefore can't hide. – Stephan Jul 27 '16 at 17:32
0

I found a better solution than using the "Active" / "All" tabs.

In Excel, go the table you want to hide, then go to the Power Pivot tab, and select "Add to Data Model". This will make the table directly visible in Power Pivot, where you can now "Hide from Client Tools".

I agree that the PivotTable Fields view shouldn't include tables outside of your data model, but with this solution you at least gain control of hiding them, and as long as you don't create any relationships you should be ok in terms of performance.

David Monroy
  • 11