Getting formatted text into your Power BI reports

By Alan Harman-Box

Power BI in Practice Power BI Resources

Hi I’m Alan, a new name here at Burningsuit and this is the first of what I hope will be many Blog posts from me.

Recently I was talking with a client who wanted Narrative Text on their Report, so I started looking at the options. Of course there’s always the trusty Text Box option straight from Power BI’s Insert menu – there are plenty of formatting options in there and that may well suit your needs. But what if you already have some text in another source? What if that text has some nice formatting that you would like to preserve as you load it onto your report? Even better, perhaps you’d like the resulting text on your report to be refreshable and to update if the source has updated? There is a fairly handy way of achieving this, and it involves saving your formatted text as a ‘Filtered Webpage’ in Microsoft Word, and then using the ‘HTML Content’ custom visual to get it onto your report in Power BI. Let’s look at this in some more detail:

Next upcoming course

online2%20%281%29.jpg

9

Dec

Power BI Fundamentals (Online)

Online

Thursday, 9 December 2021 - Friday, 10 December 2021

10:00 AM - 04:30 PM (2 days)

  • Online
  • £595.00 excl. VAT

Thu 9 Dec 2021 - Fri 10 Dec 2021

10:00 AM - 04:30 PM (2 days)

Delivered live online over Zoom, our two-day Microsoft Power BI Fundamentals training course is the perfect introduction to using Power BI

More Information

The Source

First things first, we’re going to need some formatted text. I’m going to use Word to generate a test document with some fairly simple formatting:

The next step is the key secret – we’re going to save this document as a ‘Web Page, Filtered’ using the filetype dropdown in Word’s Save As menu:

This is going to convert our Word document into HTML and save it into a .htm file. It will also strip out any Office-specific HTML tags that might otherwise be generated – which we need to do as these do not play nicely with Power BI. It is this .htm file that we then want to use the ‘HTML Content’ custom visual to render on our report. So how do we do that?

Getting the text into Power BI

To get our .htm file into Power BI so that it exists in a single field without having Power Query try and do anything clever (like try and generate fields from any tables in our .htm file), we’re going to have to build our query from scratch. Don’t run away! I promise we’ll only have to generate two lines of M.

To get started, open the Report that you want to import your formatted text into, and hit ‘Transform Data’ to open the Power Query editor. From here, we then go ‘New Source’ -> ‘Blank Query’, and once we have that, click the ‘Advanced Editor’. We now have a beautiful blank canvas on which to build our query.

Fundamentally, what we want to do is build a single column table whose single row contains just the content of our .htm file. There are probably a dozen or more ways to do this, but my approach is as follows:

let
Source = Text.FromBinary(File.Contents(“C:\Users\AlanHarman-Box\Desktop\MyFormattedText.htm”)),
#”Convert to table” = Table.FromRecords({[FormattedText = Source]})
in
#”Convert to table”

What we’ve done here is:

  1. Get the contents of our .htm file as some Binary data with File.Contents
  2. Convert that Binary data into Text data with Text.FromBinary
  3. Squeeze that data into a one-column, one-row table using Table.FromRecords (whatever you use on the left of the equals sign inside the square brackets becomes your column name (and thus field name when you’re back at the Report) so be sure to make it sensible!)

All that’s left to do get the data ready for placing onto our Report now is to click Done in the Advanced Editor, and then Close and Apply.

Getting the text onto your Report

To be able to render the HTML inside our .htm file properly, we’re going to need to use a custom visual. Any custom visual that renders HTML should work, but I haven’t tested all of them. The one we’re going to use right now is ‘HTML Content’:

Add that visual to your report, and drop your formatted text field into the visuals:

Et viola! Your formatted text directly in your report:

Addenda

?

Alright, so the observant amongst you will have noticed that glaring sat exactly where my apostrophe should be on my first numbered bullet point. This is the ‘replacement character’ and is the result of the Text.FromBinary function deciding that it has no idea what should go there. Thankfully the Text.FromBinary function takes an optional ‘encoding’ parameter, and so if we tell the function that our file was encoded by Windows, it will suddenly realise that it should in fact be an apostrophe. We do this by making the following edit to our M:

let
Source = Text.FromBinary(File.Contents(“C:\Users\AlanHarman-Box\Desktop\MyFormattedText.htm”), TextEncoding.Windows),
#”Convert to table” = Table.FromRecords({[FormattedText = Source]})
in
#”Convert to table”

If you find yourself faced by the dreaded and this doesn’t fix it, try some of the other TextEncoding options (such as TextEncoding.UTF8) and see if that resolves them.

Funky filenames

Finally, something about this process does not like .htm files that have spaces in their filenames. It will likely work initially, but if you try to refresh, ‘HTML Content’ will pull a blank and show you nothing but whitespace. I haven’t nailed down exactly why it does this and how we can fix it, so for now avoid spaces in your filenames if you can, or avoid refreshing the data if you can’t. But stay tuned – I’ll endeavour to figure this hiccup out and will update the blog if it is possible.

Next upcoming course

online2%20%281%29.jpg

9

Dec

Power BI Fundamentals (Online)

Online

Thursday, 9 December 2021 - Friday, 10 December 2021

10:00 AM - 04:30 PM (2 days)

  • Online
  • £595.00 excl. VAT

Thu 9 Dec 2021 - Fri 10 Dec 2021

10:00 AM - 04:30 PM (2 days)

Delivered live online over Zoom, our two-day Microsoft Power BI Fundamentals training course is the perfect introduction to using Power BI

More Information

6 Comments

  1. This worked great. Just note that when I cut and pasted your code into PBI desktop, I had to change the double quotes (they looked like two commas instead of the usual symmetric pattern).

  2. Do you know if there’s a size limit for this? I wanted to include a list of survey questions in a tab for a dashboard. The HTML Word doc includes the whole file, but the HTML content visual only displays the first 80 lines and cuts off.

    • Ah think I understand. Your approach stuffs the HTML into a single cell, but it looks like Power BI limits a single cell to 32,767 characters – that’s right where my file cuts off. I guess I could stack them, but that’s kind of a pain. Any other ideas?

      • Hi Mike,

        There’s no easy way of overcoming the 32,767 character limit for a single cell, but it’s worth noting that that limit is only on data once it has been loaded into PowerBI – you still have access to the full data in PowerQuery.
        This means that you can split the content across multiple cells in Power Query – and then use DAX to concatenate it all back together in PowerBI for use with the HTML Viewer.

        For example, here’s how to split some content into two cells:


        let
        Source = Text.FromBinary(File.Contents("C:\Users\AlanHarman-Box\Desktop\test2.htm")),
        #"create first half" = Text.Range(Source,0, Number.RoundUp(Text.Length(Source)/2)),
        #"create second half" = Text.Range(Source, Number.RoundUp(Text.Length(Source)/2), Number.RoundDown(Text.Length(Source)/2)),
        #"Convert to table" = Table.FromRecords({[FormattedText = #"create first half"],[FormattedText = #"create second half"]})
        in
        #"Convert to table"

        Once you have the data split how you need across however many rows of the field (in the example above the field is called FormattedText) and loaded into PowerBI, you can create a DAX Measure that concatenates it all back together. You can then use that measure in the ‘Values’ bucket of the HTML Viewer visual. My DAX measure looked like:

        ConCat = CONCATENATEX('Get Formatted Text','Get Formatted Text'[FormattedText])

        Your upper limit now becomes CONCATENATEX’s upper limit on text size, which is somewhere around 2.1 million characters. Hopefully an improvement from 32k!

  3. Thanks, this worked great, I’m using the HTML Viewer visual – I’d like to split the source text up, say by a delimiter but having trouble because the data comes through in HTML

Leave a Reply

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