Skip to content
June 10, 2016 / Erik Svensen

Inserting a picture/logo using the Power BI online designer #PowerBI

When you design reports in PowerBI using the online designer and not the Power BI Desktop designer you will notice that you cannot insert a picture via the interface.

With the development speed of PowerBI we will properly get it soon – but until that you might be find this workaround to do it via the datamodel interesting.

Let us switch to the Power BI Desktop designer and open a new report to create a datamodel we can publish to PowerBI.

Inspired by Chris Webb on how to create a table using M – (link) I have created a list of pictures using the #table statement

And this gives me a table with a number of Power BI celebrities J and a link to their twitter picture.

Now set the column ImageURL Category to Image URL

Now I can save the Desktop file and publish this to Power BI.

And now you can use the table or matrix and insert the ImageURL field on to your canvas.

Remember to use the Visual level filters to pick the image you want.

 

 

 

 

 

 

 

June 9, 2016 / Erik Svensen

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.

 

May 26, 2016 / Erik Svensen

Annual accounts for Microsoft Denmark using #PowerBI and XBRL

052616_2048_Annualaccou20.png

The Danish Central Business Register -https://datacvr.virk.dk/data/?language=en-gb& contains a lot of data including the possibility to find the annual account for all companies in Denmark.

These are available in two formats – pdf and XBRL format – so for instance for Microsoft Denmark ApS we can find the latest Annual Accounts (financial statement) via this search

https://datacvr.virk.dk/data/visenhed?enhedstype=virksomhed&id=13612870&soeg=microsoft&type=Alle&language=en-gb

As you can see, we can get the document via the link XBRL and get the annual account in XML format like this

You can read more about the XBRL format here – https://www.xbrl.org/

So knowing that we can search for companies using their CVR number and find the latest account let’s try to retrieve the data using Power BI Desktop.

Find the company data

Open the Power BI Desktop and choose get data – and the source is Web

This will give you the HTML document

But we want to find the links to the XBRL accounts so we actually don’t want it opened as an HTML document – so click Edit and in the steps pane click the source to change it to open the document as a Text file

And delete the navigation step after Source.

Now we can find all the rows where XBRL is

This gives us the three rows with information about the link to the accounts in XBRL format

Then trim the column and Split the value by ” – gives us a column 1.8 containing the link to the file.

Now we can delete unwanted columns, add an index and filter by 0 in the index column to get the latest account

Now we have a table with a link to the latest XBRL account for a given company.

Retrieve the account information

To get specific data in the accounts we can use this XBRL link to do a new query retrieving the data

So click on Home tab and New source and select Web again

https://datacvr.virk.dk/data/offentliggorelse?dl_ref=ZG9rdW1lbnRsYWdlcjovLzAzLzQwLzg1LzJkL2ZhL2VjZTItNDViMC1iOTFjLWQzOGUzZTIzZTRhYQ

This gives us the navigator to the XML document

If you are interested in specific elements you can tick those – but click for instance Context and click OK and via the Query Settings click source

Click on table in the node – http://xbrl.dcca.dk/fsa

And you get all the account lines

Then expand the Table column and choose all elements

Now in order to get the current years data you can filter by the contextRef c1

Now after some filtering and removing of duplicate rows – we end up with the lines for the profit and loss as seen here

The values are all positive so in order to handle this – we can pivot the data and do multiplication with -1 and unpivot the columns again

Then we can load this data and visualize the Profit and loss in a waterfall chart

By default the waterfall sorts the data by A-Z which isn’t exactly what we want – luckily there is a workaround for this

Choose Edit Queries and add an Index column to the Context query.

Load the data and select the Attribute in the field pane – and in the Modeling select Sort by Column and choose to sort by Index

And after some formatting etc. we can illustrate the Profit & Loss for Microsoft Denmark like this

Now we can wait a few month to the next Account is done and just click refresh.

Using functions and/or parameters you can make this highly flexible – if this post gets more than 10 likes J – I promise to show you how that can be done.

Hope you liked this – if you want to play around with you can download the pbix here.

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.

Follow

Get every new post delivered to your Inbox.

Join 415 other followers