Be aware of this feature when publishing Excel power pivot models to SharePoint

When creating Power Pivot models in Excel and you hide all the columns in your fact table/-s – Excel will show a ∑ next to the table name and show these first in the PivotTable field list in Excel.

This is a good way to help the users of your models to navigate in your model – but if you plan to browse the models via SharePoint – Excel Services has a very bad side effect…

The fact table cannot be accessed via the PivotTable Fields selector…..

If you unhide a field in the fact table

The table will now show as table in the field list in Excel.

And when you browse your model in SharePoint the Facts can now be added to the pivot table in the browser.

So if you plan to publish your models on SharePoint – do not hide all the columns in the fact tables unless you do not want the users to be able to modify the values in the pivot tables.

Setting selected item in a slicer connected to a Power Pivot table via VBA

Yesterday a colleague asked me whether it was possible to select an item in a slicer via VBA and I replied “Of course” – but that was before I studied the documentation about the “Selected” property of the SlicerItem object –

“Returns or sets whether the slicer item is selected. Read/write for slicers connected to non-OLAP data sources. Read-only for slicers connected to OLAP data sources.”

Now that was a big surprise – and I thought why not try to work around this.

First idea was to add a page field (Filter) because changing a page field will set the selected slicer items to the items selected in the page field (Filters)


However, writing a macro to do this seemed a bit over kill – so I continued reading about slicers and their properties

Moreover, it turned out that if we look at the SlicerCache it has a property called “VisibleSlicerItemsList”

That is not read/only – but Read/Write


Therefore, we can use this property to set the selected item in a slicer connected to an OLAP source – and here is the code to set a selected item

Sub SetSelection()

Dim sc As SlicerCache

Dim s As Slicer

Set sc = ActiveWorkbook.SlicerCaches(“Slicer_DimDate.CalendarYear”) ‘ Name of slicer

Set s = sc.Slicers(1)

sc.VisibleSlicerItemsList = “[Query1].[DimDate.CalendarYear].&[2005]” ‘Name of item you want selected

End Sub


Run the code (or modify to your need) to select the item.

You can download an example file here.

Fun with Power Query and public data from Copenhagen Open data

Copenhagen municipality is exposing open data via this site – . There are some interesting datasets but also some quite strange ones – for example a dataset containing all the bollards in the city.

Even though the data seems irrelevant at least for me – it is fun to use Power Query to extract the data and then use Power View and Power Map in Excel to visualize the data.

During the process, I learned a nice feature in Power Query that you might find useful as well.

Retrieving the data

The data can retrieved as a CSV file via this link

This data set contains the point for each bollard as a text

I could do a lot of transformation to extract the data but then I noticed that the url contained an parameter called Output format –

And by changing it to JSON – I could retrieve the data as a JSON document.

I then clicked the list of records to expand all records

Converted it into a table

Expanded the Column1

Expanded the to get all meta for the bollards

And then expanded the Column1.geometry to get the point information

This gave me a challenge because the coordinates was a list of values containing the Latitude and longitude and when expanding the column the list values will create two rows per bollard.


The useful Power Query tip


I didn’t want that as I wanted a separate column with longitude and latitude – but this can actually easily be done in Power Query.

Add a Custom Column and add the following formula

Where 1 refers to the row number in the list – Remember its zero based to 1 will be row 2.

And then I have to columns

The rest is renaming columns, deleting unwanted columns and one very important thing specify the Longitude and Latitude columns as decimal numbers otherwise Excel will import them into the datamodel as text values with no option of plotting them in Power View or Power Map.

And then we can start visualizing the data using Power View and/or Power Map.

One import thing about plotting the bollards in Power View is that it doesn’t handle many unique points (locations) – so instead I added the Road Name as location and then Power View will group them on the roadname – then add the Bollard ID in location as well and the user has a drill down option by double clicking the bubble.

In this case I double clicked the Dag Hammerskjölds Alle – with 239 bollards and can see all is placed on the pavement (fortov)

Another options is of course to use Power Map


In this case, I modified some settings in order to view the point better.

You can download the file from here –




#PowerPivot Tip – Set the default number format for a measure in your Power Pivot model

When you are creating measures in your Excel Power Pivot model you can do yourself a great favor if you first set the number format on the column in the data table.

In this example I have a SalesUnits column that is a decimal number and has set the format to General – which is the default.

When I click the Autosum button in the task pane to create a measure or enter the formula manually the number format will also be set to general.

But I want the number format to include a thousand separator and no decimals which means I have to format both measures manually.

If I set the format on the column before I start creating measures – in this case format Sales Units

OBS!!! This only works if you use the AutoSum button.

So if you want to create measures with CALCULATE etc. start by clicking the AutoSum – and then modify the measure and you have the number format in place.

Hope this can help you.







Format the week number in your Power Pivot date table

When creating your date table in Power Pivot the users typically want a Week label containing the week number and the year – 2014 and week 14



If you create a calculated column in your table and use the format code ” 00″ in the TEXT function second parameter

You can do the same in Power Pivot by adding a calculated column


But in Power Pivot you have to use the FORMAT function instead of the TEXT function.

Analyzing POS data using the new Q&A function in Power BI

The Q&A feature in Power BI has so far been limited to the samples files provided by Microsoft – but now you can use your own models.

I have played around with the feature and here is the result. Click the picture to see a video of a demo.

I think it’s pretty awesome and will open up new possibilities for the BI users – It will be very exciting to try to see how the users will feel about this new tool.

Update only selected tables in your Excel Power Pivot Model

Here is a quick tip to update selected tables in your Power Pivot model instead of doing it one by one or via the “Refresh All” option.

If you switch to Diagram View and select the tables you want to update while holding the CTRL key – and then click Refresh – This will only update the selected tables.

In this case I have selected the Sales, Chain and Product table

And voila – 3 out of 6 tables updated


Hope this can save you some update time.