共用方式為

Did my dad influence me?

  • 發行項

I always love it when I can combine things I really love and in this case I was thinking about music and BI.  When Data Explorer was first released on Azure Labs I played around with it a couple of times, but I have to admit at that point I failed to see just how impressive it actually is.  When we announced the first Data Explorer for Excel Preview I was completely hooked.  The power and beauty of the tool and the expression language was just brilliant to see, and I started wondering how far I could take it.  As a good exercise I asked myself the following question: "Did my dad influence me when it comes to my music choice?" .  Since I am interested in music I obviously joined Last.FM (in 2007) which tracks the music you listen to on your computer.  Now I'm lucky because my dad is also pretty modern when it comes to computers, and he soon joined me on Last.FM.  With this information I had everything I needed to start my journey.

First step in this exercise was finding out how I could get data from Last.FM to start my analysis.  I turns out Last.FM has quite a nice API which could give me all the information I needed to do my analysis.  There are 2 outputs possible, JSON and XML.  In my first attempt I started using the XML output because it is more readable in the browser, this is important if you don't know the output yet.  However, it also has more overhead and that's why I decided to go for JSON instead.  Data Explorer supports JSON very well so it was easy to move to the JSON output.

There was a specific call I was very interested in, user.getRecentTracks which gives the tracks you listened too with some attributes like the time and - interesting for Power View - a URL to the artist image on Last.FM.  One challenge however is the fact that it also accepts a parameter called limit which defined the number of rows per page (which has a max limit of 200) and a parameter page.  You can probably guess what I needed next, loop over the API with a different page number so I can get my entire history.  Turns out this isn't to difficult in the Data Explorer language, just just need to define a function that takes a parameter.  The easiest way to do this, is to build the query from within the UI and then take that as a starting point for your function. 

My query started with the following statement:

let
    Source = Json.Document(Web.Contents("https://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&api_key=myapikey&format=json&limit=200&page=1&extended=1&user=dis4ea"))
    ...

I changed it to

(page as number) =>

let
    Source = Json.Document(Web.Contents("https://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&api_key=myapikey&format=json&limit=200&page=" & Number.ToText(page) & "&extended=1&user=dis4ea"))
    ...

After that you can call the 'function' by starting a new query with the following:

let
Source = Table.Combine(List.Transform({1..10}, GetLastFMTracksByPage))
in
Source

*(GetLastFMTracksByPage is the name of the previous query)

You can learn more about this trick on Kasper De Jonge's blog.

Now this was a good start but remember the original question was to compare it with my dad's listening habits.  So I actually needed a second parameter which is the user name and that's when it became interesting, I was trying to get that working but couldn't find a good solution immediately.  So I reached out to my friends in the product team and they came up with a couple of interesting answers (many thanks to Faisal Mohamood and Clemens Szyperski for their help!).  Adding a second parameter wasn't too difficult but you need to have the right syntax to loop over it. 

First I added the second parameter user name to the definition:

(page as number, username as text) =>

let
    Source = Json.Document(Web.Contents("https://ws.audioscrobbler.com/2.0/?method=user.getrecenttracks&api_key=myapikey&format=json&limit=200&page=" & Number.ToText(page) & "&extended=1&user=" & username))
    ...

Now it's a matter of finding the right syntax for the Table.Combine statement:

let
    Source = Table.Combine(List.Transform({1..99}, each GetLastFMTracksByPage(_ , "dis4ea")))
in
    Source

That solved the first problem of having a dynamic parameter, but it would be even better to have the list for my dad and myself merged into a single table.  So how can we do this?  Faisal came up with the great idea to use a table and a custom column to solve this one.  I created a table in Excel with the following layout:

Username, Page
dis4ea, 1
dis4ea, 2
master_cobra, 1
...

Then I created the following query starting from this table:

let
   Source = Excel.CurrentWorkbook(){[Name="ParameterTable"]}[Content],
   InsertedCustom = Table.AddColumn(Source, "Custom", each GetLastFMTracksByPage([Page], [UserName]))
in
   InsertedCustom

This made it possible to have all the data in one table (with an extra column UserName) and it also makes it easy to extend it with other user names.  Now it was just a matter of visualizing the information in Power View and get the answer to my question, did my dad influence my music choice?  If you look at the screenshot below I think I can safely conclude that he did (for the record, my user name is dis4ea).

Comments

  • Anonymous
    July 01, 2013
    What, your dad doesn't listen to system of a down? Shame! ;)

  • Anonymous
    July 04, 2013
    I got him to listen to Metallica & Rammstein, although not reflected in his stats ;-)