As you might know, Python is now available as a preview feature in Power BI. It can be used to import, transform and visualize data. It has roughly the same workflow as R: you need to point to the Python interpreter on your local system, which will do all the magic for you.
Because the Python functionality is still in preview, it cannot yet be used in the online service. I’m assuming this will change soon (just like it did with R). Nevertheless, let’s get to the issue and then to the solution that Python provided.
So, my boss Frederik Vandeputte, designed a very nice Power BI report that displays `De Tijdloze 100` (it’s a radio show that is broadcasted yearly on December 31 and is a playlist of the 100 most timeless songs – it’s based on input from the listeners, so it changes over the years). Read his updated blogpost on this topic. The data was mostly coming from a web-page dedicated to `De Tijdloze 100`: https://tijdloze.stijnshome.be/. But in the latest edition all data was not available, at least not for Power BI. Let’s have a look at the page/list for 2018:
Power BI can get the first 10 songs for each year, but not the complete list, as it’s not yet loaded into the DOM. Clicking on the text `Toon de hele lijst` will add the other 90 songs. But Power BI is not able to perform the click event before getting the data (it’s not a hyperlink). Therefore, we need another approach: Python to the rescue.
When looking at the html code, it became clear that all the data is available in the webpage, not just for one year, but for all years. Hooray! But it needed some serious tweaking. The data was completely translated into JavaScript objects, but this JavaScript framework added a translation step in between. Basically, it looked like this (this is just a small part of the JavaScript):
So, I was able to locate the data and mark some key-points, but not yet to extract it in a proper way (the above is still far from a suitable data model). Let’s just start with the beginning and extract the full HTML code. We can do it with this short piece of Python (the import statements are libraries that we’ll use in the script):
Not so difficult is it. Just don’t forget the utf-8 decoding, as you want special characters to display properly. So far, so good. In a second step it’s somewhat more difficult, because we’re using regular expressions to extract the <script> tag we’re looking for:
Use the `r` in front of the string, otherwise your regular expression will become even harder to read (you would have to backslash special characters).
As mentioned, there is a translation in the JavaScript framework. This means that instead of using the real values of the songs/artists/… they have been replaced by other (at random?) values. Strangely enough not all values have been replaced. But we’ll handle that matter with Power BI conditional columns. First let’s get two important arrays: one that holds the translations (they are the parameters of the JavaScript function) and one that holds the real values (these are the values passed to this function). One appears in the beginning of the JavaScript, the other one at the end.
Both lists should be equal in length, otherwise it would not be much of a key-value thing. And don’t sort the lists yet, as we need them in the order they arrived. So now we have our mapping `table` with proper values. When using Spyder (that comes with Anaconda) you can easily see the content of the lists in the variable explorer:
After some extra clean-up wit regular expressions (removing things like unwanted quotes and backslashes) we seem to be good. We’ll stitch both lists together further on.
Next is the data itself. This is presented in JSON, but the JavaScript variant, not proper JSON: JSON needs double quotes for every key. The JavaScript function JSON.stringify takes care of this translation in JavaScript, but for Python I was not able to find exactly the same functionality (json.dumps does not do the trick in this case). I’m still learning Python, so feel free to comment on this post if you found a better way to do this. Anyhow, I took care of it with – again – regular expressions and some find/replace actions (you can find the full Python code within the Power BI file that is available for download beneath this blogpost):
This was for the songs. After doing the same for the artists we have a valid JSON string. As simple as that 😊. Don’t be intimidated by regular expressions: check out this tutuorial.
In the end, we just need to return the dataset as Pandas data frames (that’s what Power BI expects). Hence, we use functions that come with Pandas to convert the JSON and lists to the proper data frames.
Once we run the code in Power BI (with `Python script` as our source), we should see our three queries’: Songs, Artists and KeyValue.
The KeyValue query is only used to get meaningful data in the other queries (these can be rankings, titles, years…):
This KeyValue query will not be loaded in the data-model, it’s only used to lookup information (in Power BI called a merge).
Each song contains the position for every year it made it to the top 100. The positions are wrapped in their own JSON object, so we can easily parse it with built-in Power BI functionality:
At the end, we’re able to present a neat model that does not resemble any of the Python, JavaScript, RegEx, M adventure we encountered.
But most of all, it reflects the Power in Power BI, which is now even more than before a versatile and convincing BI tool with this newly added Python integration.
Cookie | Duration | Description |
---|---|---|
ARRAffinity | session | ARRAffinity cookie is set by Azure app service, and allows the service to choose the right instance established by a user to deliver subsequent requests made by that user. |
ARRAffinitySameSite | session | This cookie is set by Windows Azure cloud, and is used for load balancing to make sure the visitor page requests are routed to the same server in any browsing session. |
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie records the user consent for the cookies in the "Advertisement" category. |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | CookieYes sets this cookie to record the default button state of the corresponding category and the status of CCPA. It works only in coordination with the primary cookie. |
elementor | never | The website's WordPress theme uses this cookie. It allows the website owner to implement or change the website's content in real-time. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
__cf_bm | 30 minutes | Cloudflare set the cookie to support Cloudflare Bot Management. |
pll_language | 1 year | Polylang sets this cookie to remember the language the user selects when returning to the website and get the language information when unavailable in another way. |
Cookie | Duration | Description |
---|---|---|
_ga | 1 year 1 month 4 days | Google Analytics sets this cookie to calculate visitor, session and campaign data and track site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognise unique visitors. |
_ga_* | 1 year 1 month 4 days | Google Analytics sets this cookie to store and count page views. |
_gat_gtag_UA_* | 1 minute | Google Analytics sets this cookie to store a unique user ID. |
_gid | 1 day | Google Analytics sets this cookie to store information on how visitors use a website while also creating an analytics report of the website's performance. Some of the collected data includes the number of visitors, their source, and the pages they visit anonymously. |
ai_session | 30 minutes | This is a unique anonymous session identifier cookie set by Microsoft Application Insights software to gather statistical usage and telemetry data for apps built on the Azure cloud platform. |
CONSENT | 2 years | YouTube sets this cookie via embedded YouTube videos and registers anonymous statistical data. |
vuid | 1 year 1 month 4 days | Vimeo installs this cookie to collect tracking information by setting a unique ID to embed videos on the website. |
Cookie | Duration | Description |
---|---|---|
ai_user | 1 year | Microsoft Azure sets this cookie as a unique user identifier cookie, enabling counting of the number of users accessing the application over time. |
VISITOR_INFO1_LIVE | 5 months 27 days | YouTube sets this cookie to measure bandwidth, determining whether the user gets the new or old player interface. |
YSC | session | Youtube sets this cookie to track the views of embedded videos on Youtube pages. |
yt-remote-connected-devices | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt-remote-device-id | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt.innertube::nextId | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
yt.innertube::requests | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
Cookie | Duration | Description |
---|---|---|
WFESessionId | session | No description available. |