How Power BI picks the legend colours

I've just had a wonderful discovery about why Power BI sometimes seems to choose random colors in the legend. 

Typically, the first item in a series will match the first color of your Power BI theme, the second item in the series will match the second color of your Power BI theme, and so on. 

However, this isn't always the case. I have noticed that sometimes when I have text category values for my legend that Power BI can assign random colors, seemingly not even part of my theme. Until recently, I just accepted this as a quirk of Power BI and carried on with my report development. 

While developing a new Power BI theme JSON file and reading up on the nitty gritty details, I learned a few things that have helped me understand what's actually going on. 

Dynamic Series

The first thing I learned about was "dynamic series". You can find the Microsoft Docs definition of dynamic series in small print under the very long theme article in the How Report Theme Colors Stick with Your Reports section. 

What is a dynamic series?

To understand dynamic series, we need to understand what is meant by 'series'. Series is the fancy data viz term for items in the legend. There are two ways in Power BI visualizations to get more than one item in the legend:

1. Add a column to the 'legend' property of a visual.
2. Add more than one column to the values property of a visual (this could be called 'Values', 'X-axis', 'Y-axis', etc depending on which visual you're using).

When you use option 2, the number of items in the series will always equal the number of columns you added to the values property. This is a 'static series' because the number of items is static (meaning it doesn't change). 

A dynamic series occurs when you use option 1. Depending on the filters and slicers in the report, the number of items in the list of values of the column used in the 'legend' property could change. Let's use months of the year as an example. 

In the image above, we have added 'Month name' to the 'legend' property of the visual. So you can see we're using option 1 for creating more than one series in Power BI. This enables 'dynamic series'. We know there are always 12 months in the year, so it may seem like this is a static series. However, depending on the report filters and slicers, you may only see some of those months at a time. 

As you can see in the image above, March is always red, even when February is missing. This helps ensure that color has meaning throughout your report and that even if you're only showing quarter 4 (October, November, December), they'll maintain the mauve, green and charcoal colors. 

Themes have unlimited colors

 The second thing I learned about the seemingly 'random' colors that Power BI applies to text categories / legend items is that a theme has more than the 8 colors you see in the Power BI desktop interface. If you use the JSON file to customize your theme, you can provide as many colors as you want (there's probably a limit but I haven't needed to find it yet). That means when you have a series with more than 8 items, Power BI will continue down the color list in the hidden JSON file until it runs out of colors. Then it will repeat. 

Conclusion

So, if you see random colors popping up in your Power BI report, don't change them at the visual level. Figure out how many potential items are in your series and update the JSON theme to provide more color values if needed. 

Working with Dates Regional Formats

Dates are fundamental to pretty much every report. No matter what industry you work in, at some stage you're going to work with dates in your reporting. This might be in the form of Semesters, Quarters, Seasons, Weeks, or just good old fashioned Dates. 

If you're working with Power Query or Power BI, you should have a Date Table. In this post, I'm going to demonstrate how to work with Dates that can be tricky to format. 

We're going to address a few different challenges:

• Challenge 1A: Use Excel to format dates in the correct (regional) format
• Challenge 1B: Use Power BI to format dates in the correct regional format
• Challenge 2: Use Excel to enter and store dates in the correct regional format

As you can see, most of these challenges stem from regional formatting differences, and all are related to ensuring our dates are stored as dates (in both format and data type where relevant). 

Challenge 1: Format dates in the correct (regional) format

This challenge applies to all of us, whether you struggle with regional formatting or not. It's most applicable when you export data from another system.

For example, you may export data weekly or monthly from your financial software (such as Xero, QuickBooks, MYOB, Sage, Zoho or even PayPal, Stripe, etc), from your time log systems, from your Learning Management System (aka LMS for short), and so on. 

I think you get the idea - this challenge applies to those of us who are exporting data that contains dates from a system into Excel. Sometimes it just works. However, many times we run into one or both of the challenges below:

• The dates are formatted as text, and changing the date format from 'General' to 'Date' doesn't fix it.
• The dates are in the wrong regional format (for example your export data is in US format but your Excel only understands NZ format).

If you're struggling to get dates to format as dates with data exports / extracts, then text to columns might just be your new best friend. 

Solution A) Use Excel's Text to Columns to Fix Date Formatting

In the short video below, I demonstrate how to use Excel's text to columns to format dates as dates - in the correct regional format and not as text.

The Text to Columns feature is a great solution for one-off exports or fixes to Excel data that will continue to live and be updated within Excel.

If you are going to be exporting the same data on a regular basis, this Text to Columns fix will get tiresome quickly. I already got annoyed at having to fix each column individually. If I had to do that each week (or even monthly) I wouldn't have any time to write these blogs or do other stuff I enjoy (or need to get done!). 

Solution B) Use Power Query to Fix Date Formatting

In the short video below, I demonstrate how to use Power Query's 'Using Locale' to change the data type for dates. 

This enables us to define the regional format of our source data dates. Remember that long list of systems I spouted out at the start of this article (it's okay if you skimmed past them - just think of your system now). We need to choose the regional format for our system we're getting the data from. Don't worry about what format you want the dates in - Power Query will figure that out based on your computer and Power Query settings. 

Change Regional Settings in Power BI for date outputs

If you do want to change what format your dates end up in, you can find that in the Power BI options. 

Click File > Options and Settings > Options.

Under 'Current File' select 'Regional Settings' and change your options: 

Challenge 2: Use Excel to enter and store dates in the correct regional format

Both options above work great when we're working with structured data that has a consistent date format for the entire column. Unfortunately, that isn't always the case. It's far too easy to create a new SharePoint site (either from SharePoint or Teams) without considering the consequences. New Teams and SharePoint sites are created using the default regional settings of United States. This means that if we're editing Excel files online or via Teams, any dates we enter will be assumed to be in the US format. If we're editing Excel files on desktop, that's a different story. The result? An Excel file with a mish-mash of date formats and regional settings that can be impossible to sort out. 

This challenge is difficult to overcome for past data. The best advice I have for you is to get it right from the start. 

Words of wisdom:

• Start entering dates in a format that includes a minimum of 3 letter abbreviation for Month. This ensures it's crystal clear which date you've entered - you'll know that you and Excel are on the same page.
• Whenever you set up a new Team or SharePoint site, check the regional settings. You can see how to do this in my video below.