Skip to content
November 27, 2015 / Erik Svensen

Aligning elements in the Power Bi Desktop Designer – Workaround #powerbi

While we are waiting for the alignment feature of the elements in the Power BI Designer – you can vote for it here, – https://ideas.powerbi.com/forums/265200-power-bi/suggestions/6932400-add-alignment-positioning-and-sizing-controls-for – I have found a workaround that you might find useful.

If you press CTRL while selecting two elements on the page the placeholders for the elements is visible on screen.

After selection, you can change the size of the element you want to resize and use the handles of the other element to align with.

OBS – When you have multi-selected elements you can also move them as a group.

 

November 27, 2015 / Erik Svensen

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.

October 4, 2015 / Erik Svensen

The European Tour Rankings in Power BI Desktop – #PowerBI

Today – Thorbjørn Olesen won the Alfred Dunhill Links Championship on the European Tour (Link) – so why not celebrate this by looking at how by building a Power BI Dashboard to analyze the Race to dubai Rankings for 2015.

I know how hard it is to play golf but building the dashboard was quite easy and by using the data from this page – http://www.europeantour.com/europeantour/racetodubai/rankings/index.html – I build a dataset with these few steps.

let

Source = Web.Page(Web.Contents(“http://www.europeantour.com/europeantour/racetodubai/rankings/index.html”)),

Data0 = Source{0}[Data],

#”Changed Type” = Table.TransformColumnTypes(Data0,{{“”, type text}, {“Rank”, Int64.Type}, {“2”, type text}, {“Notes”, type text}, {“3”, type text}, {“Player name”, type text}, {“4”, type text}, {“Nat”, type text}, {“5”, type text}, {“Tournaments Played”, Int64.Type}, {“6”, type text}, {“Race to Dubai Points”, type text}, {“7”, type text}}),

#”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“”, “2”, “Notes”, “3”, “4”, “5”, “6”, “7”}),

#”Replaced Value” = Table.ReplaceValue(#”Removed Columns”,”,”,””,Replacer.ReplaceText,{“Race to Dubai Points”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value”,{{“Race to Dubai Points”, Int64.Type}})

in

#”Changed Type1″

 

And then a few measures and modelling in Power BI Desktop – I ended up with this dashboard.

 

So a total of 307 players from 31 different have won money on the European tour this year and the average money earned is 281K € – not bad for 2 – 4 days of work in the weekends J

Highlighting Denmark in the Treemap shows us that even though Thorbjørn won today he still isn’t the best dane this year in the earnings.

 

You can download the file from here – Link

Remember you can publish this to your Power BI site and set a refresh every Sunday evening so your dashboard is updated after the weekend tournament.

 

 

 

 

 

 

 

 

 

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.