Data Sonification in Power BI

Reading Time: 5 minutes

Data sonification uses variations in audio to hear differences in data values. From an accessibility standpoint, data sonification offers another potential avenue to enrich your reports beyond methods pertaining to data visualization.

There are a few pieces that need to be assembled to enable data sonification that works in both Power BI Desktop and Service. While, audio has been used in the Power BI Service for awhile, Power BI Desktop has been silent until now. This post attempts to show how to produce audio tones in Power BI for greater accessibility. It also demonstrates how to blend data with a standard range of audio pitches.

An embedded sample appears below. Note that audio in Publish to Web reports embedded into a page may not work, so it would be best to use this direct link to try the sample: https://app.powerbi.com/view?r=eyJrIjoiMGFkYjVjNjYtOWZlZi00Y2E5LTk2YWQtYmZlMzk1MWRkY2Y5IiwidCI6ImFjYzhhYWE1LWYxOTEtNDgyZi05MjFiLWNmNmMzM2E1ODgzMiIsImMiOjF9

 

In addition, most browsers now limit Autoplay for sound, but this setting can be allowed in both Chrome and Firefox using an icon near the address bar. Try changing the setting to allow sound and then refresh the page.

Audio Frequencies

In this sample, low pitch corresponds to low data values while higher pitch corresponds to higher values. The sample represents a range of frequencies from 130.81Hz to 2093Hz, which aligns with musical notes C3 to C7.

I generated a series of Wav files at the appropriate tones and stored them in a local folder for later import into Power BI. I’ve placed my core set of files from this solution into a GitHub repository here: https://github.com/deldersveld/datasonification

HTMLViewer with HTML5 Audio

As with past audio solutions in Power BI, attempting data sonification in Power BI relies upon the use of the HTMLViewer custom visual along with the <audio> HTML tag. The format is fairly basic and only requires a src and autoplay specified.

<audio autoplay src='[...]'></audio>

Autoplay is important in this case because users would otherwise need to click “play” on visible audio controls for every data point. Using autoplay may have an impact in Publish to Web reports on Google Chrome, but it seems to work fine with reports published to the Service otherwise in Chrome. Firefox and the Chromium version of Microsoft Edge worked fine for me when tested.

Imported Base64 Audio in Power BI

The method imports audio into the data model rather than relying on hosted files. This allows the audio to play in Power BI Desktop as well as Service. Desktop never traditionally played audio from hosted wav or mp3 files well.

The technique is the same one used for Base64 images. It relies on converting wav audio files to Base64 and prefixing the converted audio with a data URI.

data:audio/wav;base64,
data:audio/mp3;base64,

In the Power Query Editor, the key steps are to get data from a folder containing Wav files (or MP3) and then concatenate the HTML audio tag text around the Base64-converted binary.

let
    Source = Folder.Files("C:\Data\Wav"),
    #"Added Custom" = Table.AddColumn(Source, "HTMLAudioBase64", each "<audio autoplay src='data:audio/wav;base64," & Binary.ToText([Content],BinaryEncoding.Base64) & "'></audio>")
in
    #"Added Custom"

As with Base64 images, however, there is a tradeoff. The column containing audio cannot exceed the character limit of a Power BI text field, which is roughly 32,000 characters. This limitation means that imported audio is limited to short durations and small sample rates. You will not be playing music with this method. The sine tones used in my sample are only 8kHz and .25 seconds in duration.

My final imported table contains the audio column along with Frequency, a label for the associated note at that frequency, and an index column used for sorting and a table join that is independent of frequency.




Joining Another Table to an Audio Table

Once I have a table containing the audio, I join the audio to the data I want to sonify. I intentionally deviate from two “best practices”: the use of calculated columns and cross filter direction. Since this is a special case, there is no regret of course.

How can numeric data with a wide variation of values align with audio frequencies? The DAX formula for the calculated column maps data values proportionally to available audio values. Wrapping CEILING around the formula rounds the number to an integer so that it can be joined to the index column in the audio table.

Audio Join =
VAR __minAudio = MIN(Wav[Sort])
VAR __maxAudio = MAX(Wav[Sort])
RETURN CEILING(DIVIDE(Superstore[Sales],MAX(Superstore[Sales]) - MIN(Superstore[Sales])) * (__maxAudio - __minAudio),1)

Since this relies on the numeric measure field coded into the calculated column, it reduces flexibility. Visuals that rely on additional filtering in explicit measures will not “play” appropriately. Data sonification is indeed a special case. You need to sacrifice some interactivity by structuring the table to correspond to values in the final visual. For example, perhaps you have a transaction table with orders and sales at the grain of Order ID. To apply data sonification to a Sales by Order Date, you would need to create a summary table with SUM(Sales) grouped by Order Date.

The audio is on the 1 side of a 1:* relationship, and the audio column needs to filter along with the visual data. To facilitate the appropriate filtering to the correct frequency, the cross filter direction on the relationship needs to be Both.

Visualization Setup

With the data model in place, the next step is to obtain the HTML Viewer custom visual from the Marketplace and add it to the report canvas. Add the column that contains HTML audio to the visual.

The “Bottom 1” visual level filter on the HTML Viewer visual ends up being important. Power BI (really the HTML Viewer) plays all audio in the column whenever there is no selection. The visual level filter set to Bottom 1 prevents 30 tones from playing at once on report load or when the data tones finish playing. I chose Bottom 1 instead of Top 1 because the lowest pitch is less noticeable and more tolerable to my ears than the highest pitch.

Since I did not specify controls in the <audio> tag, nothing appears visually on the report. In general with audio, it would be a good practice to show, but I did not include it since the audio sequence is going to be controlled separately.

Obtain the Play Axis custom visual from the Marketplace and add it to the report canvas. Add the column that corresponds to the axis on the sonified visual. In my case, the main visual consists of Sales by Order Date, so the Play Axis visual needs Order Date.

Turn on the volume and hit play!

Sample PBIX

Download the PBIX file from GitHub or the Power BI Data Stories Gallery:

https://github.com/deldersveld/PowerBISamples/blob/master/Data%20Sonification.pbix

https://community.powerbi.com/t5/Data-Stories-Gallery/Data-Sonification/td-p/756842








One thought on “Data Sonification in Power BI

  1. Pingback: Hearing Differences in Power BI – Curated SQL

Leave a Reply

Your email address will not be published. Required fields are marked *