Power BI Fabric Announcement - Feeling Overwhelmed?

There have been a lot of major announcements this week at Microsoft Build conference for Power BI. Some exciting things are coming soon! Check out the new Fabric blog to keep up to date with the new releases, announcements and private and public previews.

There's a lot of new things to learn, some jargon, and some incentives to learn some of the stuff that's been around for a while but you may not have prioritized. I spend most of my day every day in Power BI and I'm a bit overwhelmed by all the news - it's exciting, but I will need to make a conscious effort to study what's useful and what's not, or I'll get left in the dust!

I'll keep blogging my learnings as I have been doing over the years and will follow the same methodology; anything I think is important, useful, or good to reference for later.

Useful Links

So, let's start with some useful links, things I've found really helpful for deciphering the exciting news. 

Data Goblins Visual Overview of Microsoft Fabric Power BI Announcements: I absolutely love Data Goblins, Kurt Buhler posts some really easy to understand content that is aimed the target audience of the business analyst / end user. So it's a not to technical summary of some technical stuff. I found this visual summary really helpful for creating my Study List below.

What is Microsoft Fabric, and Why it is a Big Deal! Reza Rad has a nice way of providing that big picture overview and this post does really answer the question "Why should I care about Microsoft Fabric?"

Study Later

Now a list of things I want to learn.

TMDL

TMDL - sounds like another language. Okay, it is another language. So that sounds exhausting, difficult, unnecessary. We already need to know M, DAX, sometimes a bit of JSON, and if you want some more functionality or better performance you may use SQL, Python, R or another language to build a Power BI report. If I hadn't seen a demo of TMDL I would have ignored this blog post, but the demo made me really curious. I think TMDL is going to help save me a lot of time with themes, formatting, and other bulk changes. It looks like it won't be a big leap from JSON to TMDL, so I'm hoping I'll pick it up quickly enough for it to save me some time.

Announcing public preview of the Tabular Model Definition Language (TMDL) | Microsoft Power BI Blog | Microsoft Power BI

Data Science Soft Skills

This course claims to be for 'beginners' and it's also called 'data science'. I like to think of myself as a data 'analyst' not 'scientist', as I don't do a lot of the stats, python, etc. So maybe I am a beginner when it comes to data science? 

Even if you know a thing or two about data science, check out the visual sketch notes that Nitya Narasimhan made for the course - it helped me find some of the lessons that I wanted to explore and learn more about.

The course itself has a combination of videos, quizzes, text and hands on examples. The quizzes don't seem to give you the right answers and some of the links to the challenges are broken, but there's still lots of good content in there. It's newly released so I'm hoping they'll iron out the bugs soon!

Data Science for Beginners (microsoft.github.io)

Try Now

Finally the reason I started writing this blog; these are the things I'm already playing with and how they're saving me time.

Users can edit data models in the Power BI Service

aka Data modeling in the web

This preview feature is awesome. You turn it on at the WORKSPACE admin level. This trips me up every time I read 'admin' I think Power BI Admin portal and that I'll need to go to IT to enable it - but that's not the case.

With the announcement of Fabric, the look and feel of 'Workspace settings' has changed a bit, so if you're struggling to figure out how to enable 'Users can edit data models in the Power BI service' or Power BI Data modelling for the web, look under 'Power BI' > General settings. If you don't see it there, try refreshing your browser and check again - for some reason it likes to hide.

I have been using it solely for viewing the DAX code of measures without needing to open or download the pbix file. That alone is worth enabling it, but there's so many other wins.

And don't worry - if you can edit the data model in the web, you can also download the .pbix file, so you won't get stuck making lots of in depth model changes only to find you can't get that file back into Power BI desktop.

Edit your data model in the Power BI Service (Preview) | Microsoft Power BI Blog | Microsoft Power BI

Power BI DimTime table

 Today's post is going to be short and sweet. I'm simply publishing my version of a DimTime table for Power BI.

Thanks to Radacad for posting a great article on why we need a DimTime table, along with their script for creating one in Power BI. 

If you don't already have a DimDate table, you can find my version in my DimDate: What, Why and How blog article. I update the script occasionally with new requests. 

Quickly Create a DimDate Table in Power Query (M code)

Follow the instructions below to create a quick, simple yet effective DimDate table in Power Query:

From the Power Query Editor, in the Home tab, click New Source > Blank Query. 

Rename the Query1 to DimTime in the Query Settings pane on the right (if you don't see this pane tick the box in the View tab in the ribbon to turn it on).

Click Advanced Editor in the Home tab in the ribbon. 

Replace all the code in the Advanced Editor window with the below code: 

let

    Source = List.Times(#time(0,0,0),24*60*60,#duration(0,0,0,1)),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Time"}}),

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Time", type time}}),

    #"Added TimeKey" = Table.AddColumn(#"Changed Type", "TimeKey", each Time.ToText( [Time], "HHmmss"), type text),

    #"Inserted Hour" = Table.AddColumn(#"Added TimeKey", "Hour", each Time.Hour([Time]), Int64.Type),

    #"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Time]), Int64.Type),

    #"Inserted Second" = Table.AddColumn(#"Inserted Minute", "Second", each Time.Second([Time]), type number),

    #"Added AM PM" = Table.AddColumn(#"Inserted Second", "AM/PM", each if [Hour] < 12 then "a.m." else "p.m."),

    #"Added Index" = Table.AddIndexColumn(#"Added AM PM", "Index", 0, 1),

    #"Inserted Hour (12hr)" = Table.AddColumn(#"Added Index", "Hour (12hr)", each Replacer.ReplaceValue( Number.Mod([Hour], 12), 0, 12), type number),

    #"Inserted 8 Hour Sort" = Table.AddColumn(#"Inserted Hour (12hr)", "8 Hour Sort", each Number.IntegerDivide([Hour], 8), Int64.Type),

    #"Added 8 Hour Groups" = Table.AddColumn(#"Inserted 8 Hour Sort", "8 Hour Groups", each if [8 Hour Sort] = 0 then "Midnight to 8am" else if [8 Hour Sort] = 1 then "8am to 4pm" else if [8 Hour Sort] = 2 then "4pm to Midnight" else "Not Specified"),

    #"Inserted 6 Hour Sort" = Table.AddColumn(#"Added 8 Hour Groups", "6 Hour Sort", each Number.IntegerDivide([Hour], 6), Int64.Type),

    #"Added 6 Hour Groups" = Table.AddColumn(#"Inserted 6 Hour Sort", "6 Hour Groups", each if [6 Hour Sort] = 0 then "Midnight to 6am" else if [6 Hour Sort] = 1 then "6am to noon" else if [6 Hour Sort] = 2 then "noon to 6pm" else if [6 Hour Sort] = 3 then "6pm to midnight" else "Not Specified"),

    #"Inserted 4 Hour Sort" = Table.AddColumn(#"Added 6 Hour Groups", "4 Hour Sort", each Number.IntegerDivide([Hour], 4), Int64.Type),

    #"Added 4 Hour Groups" = Table.AddColumn(#"Inserted 4 Hour Sort", "4 Hour Groups", each if [4 Hour Sort] = 0 then "Midnight to 4am" else if [4 Hour Sort] = 1 then "4am to 8am" else if [4 Hour Sort] = 2 then "8am to noon" else if [4 Hour Sort] = 3 then "noon to 4pm" else if [4 Hour Sort] = 4 then "4pm to 8pm" else if [4 Hour Sort] = 5 then "8pm to midnight" else "Not Specified"),

    #"Inserted 3 Hour Sort" = Table.AddColumn(#"Added 4 Hour Groups", "3 Hour Sort", each Number.IntegerDivide([Hour], 3), Int64.Type),

    #"Added 3 Hour Groups" = Table.AddColumn(#"Inserted 3 Hour Sort", "3 Hour Groups", each if [3 Hour Sort] = 0 then "Midnight to 3am" else if [3 Hour Sort] = 1 then "3am to 6am" else if [3 Hour Sort] = 2 then "6am to 9am" else if [3 Hour Sort] = 3 then "9am to noon" else if [3 Hour Sort] = 4 then "noon to 3pm" else if [3 Hour Sort] = 5 then "3pm to 6pm" else if [3 Hour Sort] = 6 then "6pm to 9pm" else if [3 Hour Sort] = 7 then "9pm to Midnight" else "Not Specified"),

    #"Inserted 30 Minute Intervals" = Table.AddColumn(#"Added 3 Hour Groups", "30 Minute Intervals", each Number.IntegerDivide([Minute], 30)*30, Int64.Type),

    #"Inserted 15 Minute Intervals" = Table.AddColumn(#"Inserted 30 Minute Intervals", "15 Minute Intervals", each Number.IntegerDivide([Minute], 15)*15, Int64.Type),

    #"Inserted 10 Minute Intervals" = Table.AddColumn(#"Inserted 15 Minute Intervals", "10 Minute Intervals", each Number.IntegerDivide([Minute], 10)*10, Int64.Type),

    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted 10 Minute Intervals",{{"AM/PM", type text}, {"4 Hour Groups", type text}, {"3 Hour Groups", type text}, {"6 Hour Groups", type text}, {"8 Hour Groups", type text}})

in

    #"Changed Type1"

Click Done to close the Advanced Editor.

Use the options in the Add Column tab in the ribbon to add any additional Time information you might need. 

Close and Apply. 

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.