Sending HTML Tables from Power BI

(and some ramblings about Copilot and SVGs)

At times, you may get requests for updates about certain metrics that are important to the organization. Maybe there is a new initiative that executives are tracking and they want to be updated about it regularly. You have already built a semantic model, but this ask is for something that may not required in 3 months for now.

You don’t have a report built for it and in your estimation a whole report for this one thing is overkill. Or maybe, and I have seen this personally myself, executives are on the road a lot and do not want to have to log in to a computer to check something quickly. An email notification would suffice.

Again, building an entire report for this one ask seems like it’s not worth it. They only really need a table with summary measures. You’d pretty much be building a report for one table and then probably adding more than they need to fill in the whitespace.

What to do?

Using Power Automate

Power Automate has a Run a query against a dataset action. With this you can connect to a semantic model in the Power BI service and execute a DAX statement. This is relatively straightforward to do with some help from DAX Studio.

I made a dummy model using the sample dataset that is in Power BI. For this walkthrough, I just made a simple measure of total sales by country. If you are new to DAX Studio and queries, start out with the SUMMARIZE() function. If you want to set up variables, use the DEFINE() statement before EVALUATE()

Easy peasy. I copied that and went back to Power Automate and pasted that in as the query.

Next action is the “Create HTML Table” action, which looks like this:

Just pass in the “First table rows” from the previous step:

Styling the Table

Matt Devaney has a walkthrough on how to use a WYSWIG to create styling for a HTML table and using it in Power Automate. He used this site to help generate the code. I won’t rehash everything he explained, but will note for this Power BI use case I did have to do some cleanup when I added the HTML table after the styling.

Basically, I had to remove square brackets from the header (Power BI provided that) and then added in the class for the table that was mentioned in the CSS. Note: double-quotes end up getting escaped in JSON, so you often have to wrap then in single quotes and concatenate the pieces together or else things get wonky.

Sending the Email

After that, you can send the output from the last Compose step (see Matt’s article) and style your email accordingly. Here is an example table from an email:

It’s pretty simple, but shows what can be done.

What Else Can You Do?

Conceivably, you could send an Adaptive Card to Teams. You could even style with charts and SVGs. I haven’t gotten this just right yet, but there are smarter folks out there that may be able to.

Revisiting the DAX, let’s say you wanted to instead have the total sales be a horizontal bar. The DAX could look like this:

In this example, I scaled the amounts to represent pixels in what would be SVGs.

Once you run this query, you could then use the Parse JSON action to be able to reference the two columns specifically. Next, you loop through each row of the response from the DAX query and add the values into a table row with SVGs:

<tr>
  <td>
    {Country}
  </td>
  <td>
  <svg xmlns="http://www.w3.org/2000/svg" width="125" height="20" viewBox="0 0 125 20" fill="none">
    <rect x="0" y="5" width="{Total Sales}" height="10" style="fill:red;"/>
    </svg>
    </td>
</tr>

Append that to a string variable and you just built a table with SVG bars. Add table tags with another compose step and you just built a table with bars.

I have tried this a little and am still working on it. One hiccup I am having is the JSON escaping with double quotes, which is a pain.

If you get this right, then you could possibly send this in an adaptive card in Teams. I don’t have a license at home to play with for Teams, but Adaptive Cards can be a great way of sending quick notifications without gumming up inboxes. I usually use this site to help me build them.

What About Copilot?

Power Automate does have a step where you can ask Copilot to generate DAX. I tried it and it did a good job for the most part. However, I did have to explain my model succinctly in the action. I haven’t worked out quite yet how it you could query the model to give it a baseline of how it is structured from Power Automate.

Here is a verbose prompt that I wrote:

Act as a Power BI expert on DAX. I have a fact table called financials with a column called Sales. I also have a related dimension table called DimCountry and a column on that table called Country. Write a DAX query to summarize total sales by country. Do not include a name for the query and please do not provide intro text and text after the measure. Lastly make sure to include EVALUATE before.

Here is a test of that prompt in Power Automate and the response:

I didn’t include any sorting or anything, so with some tweaking I could probably have it working just fine.

Why even ask Copilot to write DAX? Well, consider this scenario. You are an executive and you are working in Teams. You want to be able to ask questions of Power BI datasets that you have access to. Maybe you don’t have a Workspace set up that meets Copilot requirements and maybe Power Automate for whatever reason is more cost-effective. Wouldn’t it be nice to ask a bot a question about something in Power BI and get back a visual on the fly?

This is notional functionality that seems possible today, again, I just haven’t worked it out. Probably someone will soon, given the speed at which things are coming out regarding AI.

Wrap-Up

Once you figure out the DAX and CSS parts, sending table notifications are a pretty simple way of avoiding report tech debt. What I mean by that is that oftentimes you may get asked to make a Power BI report, but that isn’t what is needed. Like New Year’s resolutions, analytic dashboards are made with the best intentions, but if you don’t follow through on them (using them) then it can be tech debt.

I often try to ask questions like, “Is this a one-time analysis?” and “What questions do you need answered and how often?” and a lot of probing questions before really diving into making reports. A lot of times people think they want a report but then realize that they don’t have the time to be checking it regularly. Offering an alternative, especially when it is a single measure that is related to a temporary initiative, can save you from making something that no one actually uses in the long-term.