Skip to content
October 1, 2015 / Erik Svensen

Create your own Help for all the Power query functions help in Power BI Desktop

I have used a lot of time in the Power Query Formula specification help on the web – and several time for the same function – so I ended up switching between browser and Power BI Desktop.

Then I recalled reading this blogpost – http://www.excelguru.ca/blog/2015/05/13/what-power-query-functions-exist/ and decided to put the help directly in as a Query in the “Edit Queries” window.

Now I can filter the functions by name and/or when I click the “Function” in the Value column – I get the help about the function below.

The steps to create your own table is like this

And for you to copy

let

Source = #shared,

#”Converted to Table” = Record.ToTable(Source)

in

#”Converted to Table”

Hope this can help you as well.

July 6, 2015 / Erik Svensen

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

June 19, 2015 / Erik Svensen

US Open leaderboard 2015 in Microsoft Power BI Preview #Powerbi

Here a few hours before the second round of the US Open begins – I decided to try to make a live dashboard in Power BI – and even though the “live” might be a questionable – it was quite easy – but will get back to the “live” later on

Here is the result – Notice that after round 1 no players from England is under par.

 

I was actually quite easy – Start the Power BI designer – (download here)

The leaderboard – http://www.usopen.com/en_US/scoring/index.html is used as a data from the Web.

 

Then a number of transformations, replaces and extra columns – I ended up with a clean table like this

Then I created some visualizations in the Report area

 

Saved the file locally and uploaded it to my Power BI account – via Get Data

Then we can create a new dashboard and start to add elements from the report to the dashboard.

 

The live issue

 

You can set a web datasource in Power BI to do a scheduled refresh

but the schedule can only be daily and you have to specify the credential type – in this case Anonymous

 

So unfortunately it won’t refresh every 5 min as the web leaderboard does L – If you use the Power BI API you can properly get around this but using these tools you have to do it manually via the refresh button for the Dataset

 

And now

 

You can QA the data as well if you activate the feature on the dashboard

 

And voila

 

 

Or

 

Pretty fun to analyze the data now.

You can download a copy of the Power BI Designer here

 

 

 

 

June 12, 2015 / Erik Svensen

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.

May 19, 2015 / Erik Svensen

Power BI Designer tip – easy filtering of Charts – #Powerbidesigner #Powerbi

I am working on a Power BI Dashboard analyzing the Danish population based on public data I noticed a handy way to filter your charts.

Simply drag the legend to the filter box

You can see a video on how to do this – https://youtu.be/_cHqV7oCdZk

I will return shortly with the full dashboard on visualizing the data but here is a small teaser – I think I will start a baby toy shop in Copenhagen – Customer base is rapidly growing.

April 12, 2015 / Erik Svensen

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.

 

 

 

 

March 25, 2015 / Erik Svensen

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