How to Use Unicode Characters in Power BI

A common dashboard requirement is to take a set of text-based values and visually represent them as colorful icons in Table or Card visualizations. With tools like Microsoft Power BI or TIBCO Spotfire, it is fairly simple to map specific data values or data ranges to a set of default icons through conditional formatting. However, if you want to display an icon in a Card visualization in Power BI, then it’s a slightly more complicated process using a combination of DAX (Data Analysis Expressions) measures and Unicode characters to produce the expected result.

The following image illustrates the expected result using two different methods. The “STATUS” column uses the default icons available when conditionally formatting a column in a Table visualization. The “MEASURE STATUS ICON” column displays the Unicode character output from a DAX measure which is then colored using another DAX measure and conditional formatting. I find the DAX method to be more useful since it may also be used to format other visualizations such as the Card visualization as shown in the second image.

I’ll demonstrate both methods, but the DAX method is the primary focus of this example.

Power BI Desktop - Formatted Table Visualization
Formatted Table Visualization Using Conditional Formatting, DAX Measures, and Unicode Characters
Power BI Desktop - Formatted Card Visualization
Formatted Card Visualization Using DAX Measures and Unicode Characters

Step 1 – Create Underlying Data

To demonstrate this functionality, I have a list of Project Names and their associated Status listed in an Excel worksheet. The set of valid Status values include “Not Started”, “On Track”, “At Risk”, “Off Track”, and “Complete”.

Project Name and Status List
Project Name and Status List

Step 2 – Import Data

Starting with a new report in Power BI, the Excel file containing the Project Name and Status list worksheet is imported. Click Import data from Excel and select the appropriate file.

Power BI Desktop - Data Sources
Power BI Desktop – Data Sources

Step 3 – Transform Data

As part of the import process, the data may need to be transformed if the column headings are not recognized. In the screenshot below, the columns are labeled “Column1” and “Column2” instead of “PROJECT NAME” and “STATUS”. Click Transform Data.

Power BI Desktop - Load Data
Power BI Desktop – Load Data

The Power Query editor opens. As part of the Applied Steps, click Use First Row as Headers on the File ribbon to promote the first row as the new column headings.

Power Query - Use First Row as Headers
Power Query – Use First Row as Headers

Step 4 – Convert Text Values to Numbers

In this step, the text-based Status values are mapped to numeric values. This mapping eases both the conditional formatting configuration as well as the DAX measures which are created in later steps. Also, from a maintenance perspective, it reduces the number of locations where hard coded text values need to be modified when someone eventually asks to rename the values.

Switch to the Data view and then click New column on the Table tools ribbon.

Using a Switch function, the text-based Status values are mapped to numeric values. I chose increments of 100, but you may choose any unique numeric integers. The new column is named “STATUS NUMERIC”.

  • Not Started = 100
  • On Track = 200
  • At Risk = 300
  • Off Track = 400
  • Complete = 500
STATUS NUMERIC = SWITCH([STATUS],"Not Started", 100, "On Track", 200, "At Risk", 300, "Off Track", 400, "Complete", 500)
Power BI Desktop - Add New Column to Map Text Values to Numeric
Power BI Desktop – Add New Column to Map Text Values to Numeric

Step 5 – Create DAX Measures

Now that we have numeric values mapped in the “STATUS NUMERIC” column, those values can be used by two new DAX measures to map to Unicode characters as well as hex color codes.

On the Home ribbon, click New measure and add the following code to create the icon measure. Repeat for the process to create the icon color measure.

The numbers used in the UNICHAR function correspond to the equivalent HTML codes for a Figure Dash (8210), Black Large Circle (11044), Black Up-Pointing Triangle (9650), Black Diamond (9670), and White Circle (9675).

MEASURE STATUS ICON = 
    VAR vStatusNumeric = SELECTEDVALUE('PROJECT DATA'[STATUS NUMERIC])
    RETURN
        SWITCH(
            TRUE(),
            vStatusNumeric = 100, UNICHAR(8210),
            vStatusNumeric = 200, UNICHAR(11044),
            vStatusNumeric = 300, UNICHAR(9650),
            vStatusNumeric = 400, UNICHAR(9670),
            vStatusNumeric = 500, UNICHAR(9675)
        )

In simplified terms, the hex color codes correspond to black, green, yellow, and red (although these are not the equivalent of the HTML color names green, yellow, and red). I have also excluded the 500 value since the corresponding Unicode character is, by default, in the color I expect.

MEASURE STATUS ICON COLOR = 
    VAR vStatusNumeric = SELECTEDVALUE('PROJECT DATA'[STATUS NUMERIC])
    RETURN
        SWITCH(
            TRUE(),
            vStatusNumeric = 100, "#000000",
            vStatusNumeric = 200, "#84C28A",
            vStatusNumeric = 300, "#F9D087",
            vStatusNumeric = 400, "#F78272"
        )

Step 6 – Create Table Visualization

Switch to the Report view and add a Table visualization. Add the “PROJECT NAME”, “STATUS”, and “MEASURE STATUS ICON” fields as Values in the Table visualization. In subsequent steps, we’ll add default icons to the “STATUS” column using basic conditional formatting and color to the “MEASURE STATUS ICON” using a DAX measure. As illustrated in the screenshot, the DAX measure mapping numeric status to Unicode character is working.

Power BI Desktop – Unformatted Table Visualization
Power BI Desktop – Unformatted Table Visualization

Step 7 – Conditional Formatting for Default Icons in Table Visualization

Click on the “STATUS” field in the list of Values for the Table visualization. Select Conditional formatting and then Icons. Follow the screenshot below to configure the formatting of the “STATUS” field using the numeric values defined earlier. This configuration formats the column to show icons immediately to the left of the text values in the STATUS column in the Table visualization.

Power BI Desktop – Conditional Formatting for Default Icons
Power BI Desktop – Conditional Formatting for Default Icons

After applying conditional formatting, the Table visualization now displays icons in the STATUS column.

Power BI Desktop – Partially Formatted Table Visualization
Power BI Desktop – Partially Formatted Table Visualization

Step 8 – Conditional Formatting for Unicode Characters in Table Visualization Using DAX Measures

In this step, we’ll add color to the Unicode character icons. Click on the “MEASURE STATUS ICON” field in the list of Values for the Table visualization. Select Conditional formatting and then Font color. Follow the screenshot below to configure the formatting of the “MEASURE STATUS ICON” field using the DAX measure that maps numeric status to a defined color.

Power BI Desktop - Conditional Formatting for Unicode Character
Power BI Desktop – Conditional Formatting for Unicode Character

Now the Table visualization is fully formatted, as expected, using both methods.

Power BI Desktop - Formatted Table Visualization
Power BI Desktop – Formatted Table Visualization

Step 9 – Create Card Visualization

While in Report view, add a Card visualization. Add the “MEASURE STATUS ICON” field to the visualization Values. In subsequent steps, we’ll add default icons to the “STATUS” column using basic conditional formatting and color to the “MEASURE STATUS ICON” using a DAX measure. As illustrated in the screenshot, the DAX measure which maps numeric status values to Unicode characters is working.

Step 10 – Conditional Formatting for Unicode Characters in Card Visualization Using DAX Measures

Click on the added Card visualization and go to its Format options. Find the options for Data label and click the fx button for Color. Configure the options as illustrated below.

Result

Now that everything is configured, both the Table and Card visualizations are working as expected. The Unicode character icons are displayed and colored in both visualizations. If a specific entry in the Table visualization is selected, then the Card visualization displays the corresponding Unicode character with the appropriate color.

While there are other methods of introducing custom icons into a report, using DAX measures to introduce Unicode character icons is a fairly simple and lightweight method with a wide variety of options.

Power BI Desktop – Final Formatted Report (Green Status)
Power BI Desktop – Final Formatted Report (Green Status)
Power BI Desktop – Final Formatted Report (Red Status)
Power BI Desktop – Final Formatted Report (Red Status)

Microsoft Power Query for Excel Tips and Tricks

In this post, I’ve documented a few Microsoft Power Query for Excel tips and tricks that I’ve learned from a recent project. For all of the following examples, I have an Excel table named TEST_DATA containing two columns (Description and Amount). The Power Query add-in for Excel can be downloaded from Microsoft.

Test Data in Microsoft Power Query for Excel
Test Data in Microsoft Power Query for Excel

Improving Performance

Ignore Privacy Level Settings

On the Power Query ribbon, click the Options button. The Query Options dialog box is displayed. Click the Privacy option, select Always ignore Privacy Level settings, and click the OK button.

Unless you have a specific reason to keep privacy settings active, you may gain some performance improvements by ignoring them. I didn’t notice much of an improvement, but it may depend on the volume and complexity of the data set involved.

Privacy Level Settings in Microsoft Power Query for Excel
Privacy Level Settings in Microsoft Power Query for Excel

Retrieve Source Data Once

When I started using Power Query, I created an initial query and then duplicated it each time I needed to start a new query. I would then modify the duplicated query to meet the new reporting requirement. All of these queries used the same source data which was a table on another worksheet. These weren’t complicated queries, but around the 15th query created in this manner, Power Query became sluggish during refreshes. The application would eventually throw Out of Memory exceptions and Excel would crash. The data source was a simple 100+ row worksheet in the same workbook. Again, nothing complicated.

Each of those queries started with a number of common steps, e.g., define the source data, remove some columns, and filter some data. After realizing my sloppiness, I defined a new query that handled all of these common steps. I then modified the existing queries to remove those common steps and instead refer to this base query as the starting point. Let’s assume that I named the base query as BASE_DATA. In each of the other queries, the source data is then established with the following statement: = #”BASE_DATA”

Once these common steps were pulled into a single, standalone connection query, then the Out of Memory exceptions stopped and the refreshes were substantially faster.

The BASE_DATA query is defined as:

let
Source = Excel.CurrentWorkbook(){[Name="TEST_DATA"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Amount", Int64.Type}})
in
#"Changed Type"

Tips and Tricks

I’ve started a new query named EXAMPLE_QUERY to demonstrate the following tips and tricks. Again, it will use the TEST_DATA table data by way of the BASE_DATA query defined above.

Our first step is to establish the source data: = #”BASE_DATA”

Creating Total Rows

I know that a total row can be easily added to an Excel table through the standard Excel table options. However, what if we wanted to add one using Power Query? Through the Table.Combine and List.Sum functions we can combine all of the records from the source data with a calculated total row created using Table.FromRecords.

= Table.Combine({#"BASE_DATA", Table.FromRecords({[Description="Total", Amount=List.Sum(#"BASE_DATA"[Amount])]})})

This step combines all of the rows from #”BASE_DATA” and, by using the Table.FromRecords function, creates an in-line table with one record. This in-line table sets the Description column to the text Total and the Amount column to the sum of all entries in the #”BASE_DATA”[Amount] column. I found this useful when creating a query where the base data was used in a waterfall / bridge chart.

Creating a Total Row Using Microsoft Power Query for Excel
Creating a Total Row Using Microsoft Power Query for Excel

Creating Running Totals

The next example adds a column to calculate a running or accumulating total for each of the rows. The first step is to create an Index column starting from zero. If you create an Index column starting from 1, then you would need to adjust the second step accordingly. The #”Create Total Row” is the name of the previous step.

= Table.AddIndexColumn(#"Create Total Row", "Index", 0, 1)

Next, we add another column to contain the running total. Notice that #”BASE_DATA”[Amount] is used in the List.FirstN function. This is to avoid including the total row in the running total. If you used #”Added Index” (the default step name from adding the Index column), then the running total would incorrectly include the total row. Also, if you set your index to start at 1, then you would need to change the [Index]+1 parameter to [Index].

= Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"BASE_DATA"[Amount],[Index]+1)))
Creating a Running Total Column Using Microsoft Power Query for Excel
Creating a Running Total Column Using Microsoft Power Query for Excel

Using Data from the Previous Row

In this example, I create a new column that adds the value of the Amount column from the previous row to the value of the Amount column in the current row. The if conditional handles the first row (Index equals zero) where there is no previous row. This is for demonstration purposes only so the formula doesn’t particularly make sense to do in real life. The previous Amount column value is obtained with the statement #”Added Running Total”{[Index] – 1}[Amount].

= Table.AddColumn(#"Added Running Total", "Previous Plus Current", each (if [Index] > 0 then #"Added Running Total"{[Index] - 1}[Amount] else 0) + [Amount])
Accessing Previous and Current Row Values in Microsoft Power Query for Excel
Accessing Previous and Current Row Values in Microsoft Power Query for Excel

Filtering Based on a Named Cell Value

Let’s say that you want to filter the data based on the value of a cell somewhere in your workbook. As the first step, you would need to define a named range for that cell. In this example, I’ll define a cell named FILTER_VALUE. To get the value of that cell from within a query, you can use a custom Power Query function which we’ll create called GetNamedCellValue. This query function has one parameter named rangeName and it is created as a connection only query.

= let GetNamedCellValue=(rangeName) => 
  let
    content = Excel.CurrentWorkbook(){[Name=rangeName]}[Content],
    value = content{0}[Column1]
  in
    value
  in GetNamedCellValue

Once defined, you can use this function in another query as follows:

= Table.SelectRows(#"Added Previous Plus Current", each [Amount] > GetNamedCellValue("FILTER_VALUE"))

Handling an Empty Table

Continuing with the same query, I add a filter to intentionally return an empty table to demonstrate this example. The source data doesn’t have any entries with an amount greater than 1,000 so I filter the column to show only entries with a greater amount.

= Table.SelectRows(#"Added Previous Plus Current", each [Amount] > 1000)

If you’re using the custom GetNamedCellValue function defined earlier, you can set the FILTER_VALUE cell to 1,000 and use the following step instead:

= Table.SelectRows(#"Added Previous Plus Current", each [Amount] > GetNamedCellValue("FILTER_VALUE"))

As expected, the filter step returns an empty table. In the next step, I check if I have an empty table using Table.IsEmpty and then combine the empty table with an in-line table where the Description column is set to No entries found. If the table is not empty, I return the results from the previous step with no modification. I normally include this step at the end of most queries so that when a query is loaded to a worksheet, it is clear to users that the table is empty because of the data and not because of an error.

= if Table.IsEmpty(#"Filtered Rows") then Table.Combine({#"Filtered Rows", Table.FromRecords({[Description = "No entries found."]})}) else #"Filtered Rows"
Adding a Message to an Empty Table in Microsoft Power Query for Excel
Adding a Message to an Empty Table in Microsoft Power Query for Excel

Source Code

The full EXAMPLE_QUERY query used in the above examples is:

let
Source = #"BASE_DATA",
#"Create Total Row" = Table.Combine({#"BASE_DATA", Table.FromRecords({[Description="Total", Amount=List.Sum(#"BASE_DATA"[Amount])]})}),
#"Added Index" = Table.AddIndexColumn(#"Create Total Row", "Index", 0, 1),
#"Added Running Total" = Table.AddColumn(#"Added Index", "Running Total", each List.Sum(List.FirstN(#"BASE_DATA"[Amount],[Index]+1))),
#"Added Previous Plus Current" = Table.AddColumn(#"Added Running Total", "Previous Plus Current", each (if [Index] > 0 then #"Added Running Total"{[Index] - 1}[Amount] else 0) + [Amount]),
#"Filtered Rows" = Table.SelectRows(#"Added Previous Plus Current", each [Amount] > GetNamedCellValue("FILTER_VALUE")),
#"Create No Entries Found Message" = if Table.IsEmpty(#"Filtered Rows") then Table.Combine({#"Filtered Rows", Table.FromRecords({[Description = "No entries found."]})}) else #"Filtered Rows"
in
#"Create No Entries Found Message"

Excel Lookup With Multiple Criteria Using SUMPRODUCT or Array Formula

The VLOOKUP function in Excel is very useful until there is a need to identify data satisfying multiple criteria. In the following example, I’ll demonstrate two different methods to perform a lookup against data that must meet multiple conditions. One method uses the SUMPRODUCT function and the other uses an array formula with MATCH and INDEX functions.

This example assumes that the data is structured in a way that will provide a unique result from the lookup. The standard use of the SUMPRODUCT and array formulas have a very different intent so if the result is not unique, you may see unexpected results.

In the sample worksheet below, there is a set of three periods specified. Column A contains the period number which is a simple sequential number representing a unique ID for the period. Column B and Column C define start and end dates for each period. There are no overlapping dates between each of the defined periods. Column E contains a small list of individual dates. I want to automatically identify which period the date in Column E belongs to in Column F. In order to belong to a period, the specific date in Column E should be greater than or equal to the period start date and less than or equal to the period end date.

Excel Multiple Criteria Lookup Sample Worksheet
Excel Multiple Criteria Lookup Sample Worksheet

Either formula should be entered starting in cell F2 (or modified to fit your particular needs).

Method 1 – Lookup using the SUMPRODUCT function

=SUMPRODUCT(--($B$2:$B$4<=E2),--($C$2:$C$4>=E2),$A$2:$A$4)

Method 2 – Lookup using an array formula with MATCH and INDEX functions

=INDEX($A$2:$A$4,MATCH(1,(($B$2:$B$4<=E2)*($C$2:$C$4>=E2)),0))

Since Method 2 uses an array formula, hit CTRL+SHIFT+ENTER after entering the formula in cell F2 otherwise Excel will show “#N/A” instead of the correct result.