Blog

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:

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:

Image

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:

Image

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’:

Image

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

Image

Et viola! Your formatted text directly in your report:

Image

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.