Embed audio in Power BI report from OneDrive or Sharepoint

[Omega]

Hi, 

I am trying to embed a sound track from OneDrive or Sharepoint to HTML Viewer. I managed to create the HTML code for sounds in different website but I don't know to get the HTML code of the audio stored in OneDrive or Sharepoint. 

Please advise. 

Thanks!

[v-juanli-msft]

Hi @Omega 

In Onedrive or sharepoint, you could open the audio link, paste this link in the html code in Power BI.

Add Audio or Video to a Power BI Report

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omega]

When I open the file, in the URL bar, it doesn't end up with .mp3 😞

[v-juanli-msft]

Hi @Omega 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Omega 

Change the "type" to your audio type.

<source src='******/**.your-type' type='audio/your type'>

Please know the limitation:

1.Audio and video files should meet the standard criteria for the HTML5 <audio> or <video> tag. There are some limitations in terms of file type (mp3, ogg), etc.

2.Audio and video content should be hosted online with a publicly accessible URL.

3.Sound or video will only work in Power BI Service. Controls will display in Power BI Desktop, but no content will load.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omega]

Seems I can't use audios in sharepoint or outlook since they are not public 😞 Thanks a lot for the help 🙂 

[OwenAuger]

@Omega 

Just an extension of the above suggestions:

1. You can share the audio file stored in Sharepoint/Onedrive, either publicly or by giving access to users on your tenant. Then it will be playable within a Power BI report by anyone who is logged in to an account with access to the audio file (or anyone at all if shared publicly).
2. Alternatively, you can convert the audio file binary data to Base64 during data load, and create a URL of this form (assuming MP3):
   

   data:audio/mp3;base64,<BASE64STRING>

   The advantage of this method is that no connection to source file is needed only for data refresh, but not when viewing the report. However the size of audio file will be limited by the maximum possible length of a text string.

I have attached a PBIX illustrating the two methods. Try changing the MP3 URL parameter to your own Sharepoint/OneDrive URL.

As an example, my existing URL is:

https://ozerconsulting-my.sharepoint.com/personal/owenauger_ozerconsulting_onmicrosoft_com/Documents/Power%20BI%20Sample%20Reports/123.mp3

Regards,

Owen

[mnahmany]

Hi Owen,

Thank you for posting this.

I downloaded the file you attached to look at the two samples but only the Base64 seems to be working. Am I missing something?

[OwenAuger]

Hi @mnahmany 

The issue with that example was that the MP3's web URL wasn't publicly accessible (as well as the link no longer being valid due to a change on my tenant).

So the Base64 string was valid but the web URL wasn't (unless you were logged in with an appropriate account).

Here is a version with a valid public URL from my Personal OneDrive as an example.

Regards

[PatChan]

@OwenAuger Thank you for your suggestions. The sharepoint method does not work for me since audio is confidential so the base64 string is the only way. Could you guide us how to generate the HTML2 in your pbix file please? Appreciate it.

[OwenAuger]

Hi @PatChan 

Sure thing 🙂

1. MP3s where entire Base64 & HTML string can be stored as a text value in a table

The method I used in the original example only works if the entire HTML string including the Base64 encoded MP3 can be stored as a text value in a column. This means the entire string must be no more than 32,766 characters (see here).

Assuming this is the case, you can use this method.

The column I originally created contains text following the below this structure where <BASE64_ENCODED_MP3> is replaced with the Base64 encoding of the binary contents of an MP3 file:

<audio controls><source src='data:audio/mp3;base64,<BASE64_ENCODED_MP3>' type = 'audio/mp3'> </audio>

If you have already loaded the binary content from source, here is a function that would create this HTML text is:

( MP3_Binary as binary) =>
let
    Base64 = Binary.ToText(MP3_Binary,BinaryEncoding.Base64),
    HTML = "<audio controls><source src='data:audio/mp3;base64,"& Base64 & "' type = 'audio/mp3'> </audio>"
in
    HTML

2. MP3s where the Base64 & HTML string is too large to be stored as a text value in a table

For longer MP3s, you will need to split the Base64-encoded MP3 data across multiple rows, similarly to how images were split in this post.

Again assuming that you have already loaded the binary content from source, you could use this function to create a table per MP3 containing the Base64 string split across rows:

(MP3_Binary as binary) =>
let
    SplitSize = 30000,
    Base64 = Binary.ToText(MP3_Binary,BinaryEncoding.Base64),
    Base64Split = Splitter.SplitTextByRepeatedLengths(SplitSize)(Base64),
    Positions = List.Positions(Base64Split),
    PartRecords = List.Transform(Positions, each [MP3 Part Index = _+1, MP3 Part Base64  = Base64Split{_}]),
    PartTable = Table.FromRecords(PartRecords, type table[MP3 Part Index = Int64.Type, MP3 Part Base64 = text])
in
    PartTable

Applying this method, you end up with multiple rows per MP3, which must be reassembled by a measure.

In my sample PBIX attached, the measure I created is:

MP3 HTML = 
VAR MP3_Index = SELECTEDVALUE ( MP3[MP3 Index] )
VAR Result =
    IF (
        NOT ISBLANK ( MP3_Index ), -- Require single selection
        VAR MP3_Name =
            SELECTEDVALUE ( MP3[MP3 Name] )
        VAR Base64Concatenated =
            CONCATENATEX (
                MP3,
                MP3[MP3 Part Base64],
                "",
                MP3[MP3 Part Index]
            )
        VAR MP3HTML =
            "<label for='audio-player'>" & MP3_Name & "</label>"
            & "<audio id='audio-player' controls><source src='data:audio/mp3;base64,"
            & Base64Concatenated
            & "' type = 'audio/mp3'> </audio>"
        RETURN
            MP3HTML
    )
RETURN
    Result

As an example, I used the HTML visual to display the HTML per MP3:

Hope that helps!

Regards,

Owen

[PatChan]

Thank you @OwenAuger for responding so quickly! I managed to generate the binary64 string for my 4-sec recording using the python code below. It's surprising that a 4-sec recording can be translated to such a long string. Anyway, the recording got cut off sadly after 1-2 seconds. I am not sure why 😕 Please don't feel obliged to respond as I think this should be a separate thread already!