Skip to content
May 8, 2017 / Erik Svensen

Speaking at SQL Saturday #588 NYC #SQLSATNYC

I still hasn’t really sunk completely in but I have been selected as a speaker at SQL Saturday #588 in New York.

The session line up is amazing and with a lot of Power BI celebs – both Adam Saxton and Patrick Leblanc are speaking as well – so I fell a little nervous but extremely proud

My session is called “Power Query – Don’t be afraid of the advanced editor” and will be about how you can make your Power Queries do even more and make them more dynamic and flexible. This can be done by knowing the M language a lit bit better and use it in ways you perhaps hadn’t thought of before.

I will show some of the cases and examples where the interface in the query editor wasn’t capable of solving my problems but using the advanced editor could.

So, join me on a ride into the advanced editor and hopefully I can teach you how to

  • Use variables in M
  • Refer to other queries and columns
  • Make a dynamic date table
  • Handle different number of columns in your datafiles
  • Handle different headers in your datafiles
  • Discover some of the hidden parameters in the M commands

 

Read about the event here – http://www.sqlsaturday.com/588/eventhome.aspx

 

 

April 23, 2017 / Erik Svensen

Set size for multiple visualizations in #PowerBI at the same time

When designing your reports in Power BI Desktop you properly spent a lot of time making sure your visualizations is aligned and at least for some of them making sure they have the same size.

So far, we only have the align feature in the Power BI Desktop

To change the size of the visualizations we must use the General properties under Format to resize the elements

But what if you want to resize more than one element at a time – If you select more than one you get the size of the first selection in the general tab

Now here is the trick – modify the width and Height with 1 each

And then back again

 

And your visualizations have the same size.

OBS – This only works when you select the same type of visualizations – if select different types you won’t be able to see General under Format.

Hope this can help you too –

 

 

April 14, 2017 / Erik Svensen

Tip – Create several AutoSum Measures in with one click #Excel Power Pivot – #powerbi

Just wanted to share a quick tip I just stumbled upon when designing a data model in Excel Power Pivot.

For creating a measure in Power Pivot, I place the cursor under the column and then I click the AutoSum button on the Home tab –

And then I get the formula created quick and I can modify the name.

But did you know that if you multiselect several cells in the Calculation Area and click the AutoSum – you get formulas for all the columns you have selected

 

Hope you find this useful as well …

 

 

 

October 5, 2016 / Erik Svensen

Power Query is awesome – dynamic table of dates – #powerbi

Many blogposts has been written about how you can create a generic date table in your Power Pivot / Power BI datamodels.

Here is a few examples

https://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/

http://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

https://www.powerquery.training/portfolio/dynamic-calendar-table/

But my challenge with these has always been that I had to specify from date and to date either in a worksheet, text file or directly in the Query Editor.

Normally your fact table actually contains the max and min date that you actually want your date table to contain – so in the last model I created I decided to see if I could make the from and to date dynamic based on the max and min date in the fact table.

By using a bit of M magic functions it turned out to be very easy.

Let’s begin

In my example I load the FactInternetSales table from the AdventureWorks database

Now as we can see in the query editor we have a column containing the Order date and the min and max of that column should be used to generate the table of dates.

Convert the OrderDate to Date instead of DateTime

Then add a blank query via the New Source button

In the formula bar you enter the following formula

= List.Dates(List.Min(FactInternetSales[OrderDate]),Duration.Days(List.Max(FactInternetSales[OrderDate])-List.Min(FactInternetSales[OrderDate])),#duration(1,0,0,0))

And you get a list of dates – 🙂

Now we can add columns with the date information we want in our date tableby using the add Column Date – From Date & Time

The date table now depends on the Query FactInternetSales – and with the September update of the Power BI Desktop we can see that via the Query dependencies window

Now Power Query apparently knows that it has to update FactInternetSales before it updates the Date table, so when our facttable is updated the Dates get updates afterwards.

You could by modifying the expression to get the list of dates calculate the first day of the min year and last date of the max year etc. – all this depends on how complete you want the datetable.

Power Query is AWESOME J

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

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.