Short Cut Keys– that every Excel user should know

Here is a list of short cut keys that I almost use every time I work in Excel.

The short cut keys works in the English version of Excel – localized version may use other keys.

CTRL + ‘ – copies the content of the cell above and enter edit state of the cell – very handy when creating almost the same formula as the cell above

F4 – when you are editing a cell reference in the formula bar the key cycles through the absolute and relative for column ref and row ref

CTRL+ _ (underscore) – removes any border around the selected cells

CTRL + ENTER – when a range or multiple cells is selected and you press CTRL + ENTER when you input values or formulas the input will be inserted in your entire selection

CTRL + F1 – Hide the Ribbon

CTRL + 1 – Open the format cells dialog

Hope these can help you too – if you have other favourites please let me know in the comments below.

 

Excel Tip – Easy way to remove all value fields from your pivottable #excel

If you want to remove all your value fields from your pivot table – you should not do this by removing the fields one by one.

Instead of removing each field from the Values area in the PivotTable Fields action pane one by one – simply remove the Values field in the column or rows area.

Instead of removing each field from the Values area in the PivotTable Fields action pane one by one – simply remove the Values field in the column or rows area.

That will remove all your value fields from the pivottable.

Hope this little trick can help you too.

Visualize the danish mobile network history & coverage using #PowerQuery and #PowerMap – #powerbi

A lot of public datasets is available via this site http://datahub.virk.dk/data/search – currently only in Danish.

One of the datasets contains information about the location of all the antennas in Denmark – http://mastedatabasen.dk/Viskort/ContentPages/DataFraDatabasen.aspx?callingapp=mastedb – and it has an excellent API that enables you to extract data about the antennas.

Here is how you can extract the data using Power Query and then visualize it in Power Map using Excel 2016 – you can do the same in Excel 2013 but I was playing around with Excel 2016.

First I select the

And paste in this query – which will return all the antennas in Denmark – it will return about 27.000 but I set the Max to 100.000

http://mastedatabasen.dk/Master/antenner.json?maxantal=100000

Click the To Table in the ribbon – accept the default and then Expand the column – then you have the data

The rest of the steps is deleting unwanted columns and renaming the fields and changing the established column to a date.

All the steps is as follows

let

Source = Json.Document(Web.Contents(“http://mastedatabasen.dk/Master/antenner.json?maxantal=100000”)),

#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“vejnavn”, “husnr”, “postnummer”, “kommune”, “idriftsaettelsesdato”, “forventet_idriftsaettelsesdato”, “etrs89koordinat”, “wgs84koordinat”, “tjenesteart”, “teknologi”, “unik_station_navn”, “radius_i_meter”, “frekvensbaand”}, {“vejnavn”, “husnr”, “postnummer”, “kommune”, “idriftsaettelsesdato”, “forventet_idriftsaettelsesdato”, “etrs89koordinat”, “wgs84koordinat”, “tjenesteart”, “teknologi”, “unik_station_navn”, “radius_i_meter”, “frekvensbaand”}),

#”Removed Columns” = Table.RemoveColumns(#”Expanded Column1″,{“etrs89koordinat”}),

#”Expanded wgs84koordinat” = Table.ExpandRecordColumn(#”Removed Columns”, “wgs84koordinat”, {“bredde”, “laengde”}, {“wgs84koordinat.bredde”, “wgs84koordinat.laengde”}),

#”Expanded tjenesteart” = Table.ExpandRecordColumn(#”Expanded wgs84koordinat”, “tjenesteart”, {“navn”}, {“tjenesteart.navn”}),

#”Expanded teknologi” = Table.ExpandRecordColumn(#”Expanded tjenesteart”, “teknologi”, {“id”, “navn”}, {“id”, “navn”}),

#”Renamed Columns” = Table.RenameColumns(#”Expanded teknologi”,{{“wgs84koordinat.bredde”, “Latitude”}, {“wgs84koordinat.laengde”, “Longitude”}}),

#”Removed Columns1″ = Table.RemoveColumns(#”Renamed Columns”,{“forventet_idriftsaettelsesdato”, “kommune”}),

#”Expanded postnummer” = Table.ExpandRecordColumn(#”Removed Columns1″, “postnummer”, {“nr”, “navn”}, {“nr”, “navn.1”}),

#”Renamed Columns1″ = Table.RenameColumns(#”Expanded postnummer”,{{“nr”, “ZipCode”}, {“navn.1”, “City”}, {“tjenesteart.navn”, “Servicename”}, {“navn”, “Technology”}}),

#”Removed Columns2″ = Table.RemoveColumns(#”Renamed Columns1″,{“unik_station_navn”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns2″,{{“idriftsaettelsesdato”, type date}}),

#”Renamed Columns2″ = Table.RenameColumns(#”Changed Type”,{{“idriftsaettelsesdato”, “Established”}})

in

#”Renamed Columns2″

Then click close and load to – and select to “Only Create Connection” and load the data to the data model in the workbook

Then we can start to visualize the with Power Map

I like the new layer pane in Power Map – it makes is easy to modify the layer – instead of the “old” steps in Power Map in Excel 2013.

In this example I have chosen to plot the antennas by latitude, added the Established as a time and added a filter for LTE – the 4G network.

And now I can click the time line to see the development over time.

I can also see why I need an internet connection in my summerhouse –

Not a lot of antennas nearby – 😦

You can download the file here to

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.

Use Power View report exported from SharePoint in Excel – #excel #powerview

One of the nice features of Power View on SharePoint is the ability to export the report to PowerPoint and maintain the possibility to interact with the report directly in Power Point.

However, one interesting thing is that you can actually copy this functionality to Excel and interact with the Power View report in Excel.

Here is how you do it

Select the report section in the PowerPoint presentation you want in Excel

.

Right click the object and select Copy

 

And switch to Excel and paste the object into to the sheet you want the dashboard.

And the dashboard directly in Excel – 🙂

The object can be manipulated if you switch to the Developer tab and enter Design mode and select the properties of the object

 

Notice the InitParams property – If you change the AllowSectionNavigation from False to True – This will give you the Navigation arrows at the bottom on the object and the user can switch between the sections.

You might have to close and reopen the workbook to see the change.

 

 

 

 

Plotting speed limits in Copenhagen using public data, Power Query and Power Map

My hometown Copenhagen is sharing a lot of public data via – http://data.kk.dk/ – and one of the latest is a dataset with speed limits for the different roads in Copenhagen – and the dataset is shared as a geojson resource – http://data.kk.dk/dataset/trafikhastigheder

So why not try to see how the Power BI tools in Excel 2013 could visualize this information.

First – Power Query

 

Copy the geojson url – http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:parkeringstaelling_i_zoner&outputFormat=json&SRSNAME=EPSG:4326

And activate Power Query and choose to import from Web

Power Query will then show you the information about the json document

And then navigate to the list – features

 

Then convert the list to a table and expand the different elements in the document

 

In order to get the Latitude and Longitude we need to extract the values from the geometry.coordinates field – by adding two new calculated columns – it can be done like this

Then I do some renaming of columns and remove unwanted columns I end up with a relative simple dataset

 

The full query is listed here

let

Source = Json.Document(Web.Contents(“http://wfs-kbhkort.kk.dk/k101/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=k101:trafikhastigheder&outputFormat=json&SRSNAME=EPSG:4326”)),

features = Source[features],

#”Table from List” = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#”Expand Column1″ = Table.ExpandRecordColumn(#”Table from List”, “Column1”, {“type”, “id”, “geometry”, “geometry_name”, “properties”}, {“Column1.type”, “Column1.id”, “Column1.geometry”, “Column1.geometry_name”, “Column1.properties”}),

#”Expand Column1.geometry” = Table.ExpandRecordColumn(#”Expand Column1″, “Column1.geometry”, {“type”, “coordinates”}, {“Column1.geometry.type”, “Column1.geometry.coordinates”}),

#”Expand Column1.geometry.coordinates” = Table.ExpandListColumn(#”Expand Column1.geometry”, “Column1.geometry.coordinates”),

#”Expand Column1.properties” = Table.ExpandRecordColumn(#”Expand Column1.geometry.coordinates”, “Column1.properties”, {“id”, “bydel”, “vejid”, “vejnavn”, “frastation”, “tilstation”, “hastighedsgraense”, “anb_hastighedsgraense”}, {“Column1.properties.id”, “Column1.properties.bydel”, “Column1.properties.vejid”, “Column1.properties.vejnavn”, “Column1.properties.frastation”, “Column1.properties.tilstation”, “Column1.properties.hastighedsgraense”, “Column1.properties.anb_hastighedsgraense”}),

#”Added Custom” = Table.AddColumn(#”Expand Column1.properties”, “Custom”, each [Column1.geometry.coordinates]{1}),

#”Added Custom1″ = Table.AddColumn(#”Added Custom”, “Custom.1”, each [Column1.geometry.coordinates]{0}),

#”Renamed Columns” = Table.RenameColumns(#”Added Custom1″,{{“Custom”, “Latitude”}, {“Custom.1”, “Longitude”}}),

#”Removed Columns” = Table.RemoveColumns(#”Renamed Columns”,{“Column1.properties.anb_hastighedsgraense”, “Column1.type”, “Column1.id”, “Column1.geometry.type”, “Column1.geometry.coordinates”, “Column1.geometry_name”, “Column1.properties.id”, “Column1.properties.vejid”, “Column1.properties.frastation”, “Column1.properties.tilstation”}),

#”Changed Type” = Table.TransformColumnTypes(#”Removed Columns”,{{“Column1.properties.hastighedsgraense”, Int64.Type}, {“Latitude”, type number}, {“Longitude”, type number}}),

#”Renamed Columns1″ = Table.RenameColumns(#”Changed Type”,{{“Column1.properties.hastighedsgraense”, “SpeedLimit”}, {“Column1.properties.vejnavn”, “Roadname”}, {“Column1.properties.bydel”, “CityArea”}})

in

#”Renamed Columns1″

 

I return the data to the datamodel and we are ready to activate Power Map.

 

And by using the filter function I can find the roads where the limit is 60 km/h or higher

 

You can download a copy of the file – here

 

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.”

http://msdn.microsoft.com/en-us/library/office/ff822747(v=office.15).aspx

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.