Tag Archives

3 Articles

The most awesome Power BI soundtrack

The most awesome Power BI soundtrack is of course I’ve Got The Power by Snap! 🙂
If you have a better idea, just let me know. Enjoy!

Using Power BI Q&A to talk to your music collection

In an earlier post I used Power BI (specifically Power Query, Power View and Power Pivot) to get insight into my music collection. In this post I have a conversation with my music collection using Power BI Q&A.

Q&A enables you to “talk” to your datasets and ask questions in natural language. Q&A gets the data, proposes a visualization and displays it. You can then change the question, get immediate feedback. This invites the user to do data exploration.

What I did to make this work is this: I uploaded the Excel sheet to SharePoint and enabled it for Power BI. Then I enabled the same sheet for Q&A. Next step was to use Q&A and ask questions.

The question I started with was: “number of albums by release date”. Q&A responds like this:

As you can see, it selected my Excel sheet and chose a line graph to display the number of albums by year. What is interesting is the spike to the left. It turns out this is a data quality issue: a lot of dates have been reported as being 31/12/1899 when there was no date in the tags in the first place.

Let’s say I would like to focus more on recent data, such as albums since 1960. I refined my question to “number of albums by release date after 1960”, which gives me this result:

Then I decided to look at one decade: “number of albums per artist where decade is 1980 – 1989”. This is the result I got:

As you can see in this decade Queen is on the top. Let’s dive a bit deeper into in which decades Queen released albums that I have in my collection: “number of albums by decade where artist is Queen”. This is the result:


As you can see I have got quite a few Queen albums spread over multiple decades. Now let’s see if Queen is indeed as popular in my collection as it might look. I decided not to look at albums but at number of tracks. I refined my question to: “top artist by number of tracks”. This is the result.


Interestingly Queen is only in 10th place here (even after the choir which my mom is part of J). Based on the number of tracks Herman Brood is the most popular artist in my collection.

This got my wondering about the actual duration of the music (track length). Finally, I changed the question to: “top artist by total length”. This gave me the following result:

When looking at the total track length of all songs Queen is back at place two and my all-time favorite band is on top (Supertramp).

Hope you enjoyed the tour of my music collection using Q&A. Looking forward to feedback and your examples!


Using Power BI to visualize your music collection

As some of you might recall, I have been talking about visualizing digital music collections using Microsoft tools. It all started back in the days of Pivot Viewer (At some point I was even part of a lustrous duo called ‘the Pivot Brothers’).
Now, with Power BI being just around the corner I started thinking about taking on this old habit again. Here it goes…

If you are like me you have a digital music collection all nice and tidy complete with ID3 tags. A lot of tools exist that enable you to work with these tags and fix them. I particularly like Mp3tag (http://www.mp3tag.de/en/) which is free and is extensible. It also comes with an export function to CSV, although it does not export enough to my taste. I created my own export file and I suggest you do too. Keep reading if you want to know how and maybe rip mine.

Of course you can use any other tool that has an export function, as long as you end up with something like a CSV. If you want to follow along in my step-by-step scenario I suggest installing Mp3tag.


Creating the export

Again, please note that you do not have to follow these steps exactly. If you use another tool (not Mp3tag) or already have an export file you would like to use skip to the next step.

To create the export with Mp3tag, open the program and navigate to where you music is stored (my collection sits on \\\Music, which is a folder on my server). Once Mp3tag has listed all your files (might take a while if you have a large collection), select all files (EditĂ Select all files or CTRL+A) and then choose File Ă  Export (CTRL+E). You will get the Export dialog. Now, hit the first button on the right (the little page with a star to create a new export configuration and give it a name (I named mine “My CSV Export”) and click OK. Notepad will open and allow you to edit the export configuration.

At this point you can copy and paste the code below into Notepad and hit save.

Then, return to Mp3tag, select your export configuration, set up the export file name and click OK. Wait for a while and then click No to not display the export file.


Loading the file

If you followed along you will have a CSV file that contains the export of your music collection delimited by semicolons. Your delimiter may be different. As a consequence the steps you need to take to get the data loaded may vary.

Open up Excel 2013, click on Power Query in the ribbon and select From File Ă  From CSV. Select the CSV file you just created and click OK. Now Power Query will do its best to give you what you want but as you can see it is not very successful (with the build available at the time of this writing).


All is not lost however, because now we get to work with the magic of Power Query!

First off, we need to get rid of all the errors we get in the data rows. Click open the steps fly-out on the right and click the first step (Source) and click the icon right next to it. Then in the ‘Open file as’ drop down box select ‘Text File’ instead of ‘CSV Document’ and click OK. This already looks a lot better, doesn’t it?

Now we need to split the column in by the semicolon delimiter. Make sure the ‘Source’ step is still selected and then right click on the ‘Column1’ header and choose ‘Split Column’ Ă  ‘By Delimiter…’

Make sure to select the correct delimiter (semicolon in my case) and make sure that ‘At each occurrence of the delimiter’ under ‘Split’ and click OK.

Then you screen should look like this:

Now select the ‘FirstRowAsHeader’ step and check the output.

We now need to delete the last step (we will redo it ourselves later on). To do this mouse over and click on the little cross icon in front ‘ChangedType’.


Transforming the data

  1. Change data types

Now we need to change some data types but right clicking on the header of the column and choosing ‘Change Type’ and choosing the type you want to change to.

Change these columns to the indicated types:

Column Change to type
Last Modified Date
BPM Number (select ‘Using locale’ first and then select ‘Number’ and ‘English (United States)’ if your country does not use a . as decimal separator.
Bitrate Number
Year Number
  1. Fix the Size column

Now, split the Size column by right clicking on the ‘Size’ column header and selecting ‘Split Column’ and then ‘By Delimiter…’. Select ‘Space’ as delimiter and select ‘At the left-most delimiter’ as split option and click OK.

You will now get two size columns: Size.1 which contains the numerical value and Size.2 which contains MB or KB.

First, change Size.1 to a number (right click on header, Change Type à Number). Then insert a new column (right click on a column header, Insert Column à Custom…) and enter the following custom column formula:

Rename the Custom column by right-clicking the header and choosing ‘Rename…’. Enter ‘Size’ as your column name and hit enter. You now have got a single column that reports the size of the song in Kb.

To wrap things up right click ‘Size.1’ and click ‘Remove’. Do the same for ‘Size.2’.

  1. Add cover art

One of the nicest things of albums is the cover art. Luckily Windows stores the cover art in the same directory as your music files as a hidden file named ‘Folder.jpg. Let’s add this by adding another column (right click on a column header, choose Insert Column Ă  Custom…) and entering the following custom formula:

Rename the column to Cover.


Now we’re done transforming the data. The steps you performed form a script, which you can see by clicking on the title scroll icon at the formula bar. Here is my script:

As you can see every little step we took is represented in the script.

Now click ‘Done’ to get the transformed data in Excel, where we will start on the visualization.


Fixing the covers using PowerPivot and adding release date

Now we need to tell Power BI to read the cover column as a reference to an image. This is something we cannot do in Power Query at the moment, but we can in PowerPivot. In Excel, click PowerPivot Ă  Manage to open up the PowerPivot window. In here, select your Cover column, go to Advanced and set the Data Category to be Image URL.

Also, add a new column (by clicking on the empty column at the right of your table) and enter the following formula:



Hit enter and name this column ReleaseDate.

Building the visualization

Back in Excel, click Insert Ă  Power View and start building your visualizations. Here are some examples I built:

  1. Number of tracks per release year

    Count of track by year in a line graph


    Apparently most the tracks in my collection were released around 2000, while the oldest track I have has been released in 1930.



  2. Number of tracks per genre over time

    This clearly shows that in 1978 the rock genre was most popular (at least in the albums in my collection). However, the Disco songs were the longest on average.

    The play axis at the bottom allows me to play through my collection over time.


  3. Number of albums per genre per year

    In 1983 the most popular artist was Kajagoogoo and you also get an idea of what the popular genre was


  4. Albums by genre and artist of time

    This shows the progressive rock genre and when it was popular. Also it shows which artists released albums categorized as progressive rock and which albums I have of that artist (I selected Yes).


That’s it

I will revisit visualizing my music collection when I have access to a BI Site, so I can show the latest visualization live. For now, this it it. Enjoy!

%d bloggers like this: