Quick Tips: Converting Hexadecimal, Oct and Binary to Decimal in a Single Power Query Function

A Power Query Function to Convert HEX, OCT and BIN values to DEC

A while ago I wrote a blogpost on how to use Unicode characters in Power BI. In that blogpost I used a recursive Power Query function to convert Hex values to Dec values. A few weeks back one of my site visitors kindly shared his non-recursive version of Power Query function which beautifully does the job. A big shout out to Rocco Lupoi for sharing his code. So, I decided to share it with everyone so more people can leverage his nice Power Query function. I have touched his code a bit though, but it was more of a cosmetic change, so all credits of this blogpost goes to Rocco. The benefits of his code is not limited to being non-recursive. The code below converts numbers of any base when the base is smaller than 16 like Binary and Oct, so it is not limited to Hex values only. The other benefit of the below code is that it is not case sensitive (note to the digits step on the code below).

Here is the fnHex2Dec function for Power Query:

(input as text, optional base as number) as number =>
let
        values = [
                0=0,
                1=1,
                2=2,
                3=3,
                4=4,
                5=5,
                6=6,
                7=7,
                8=8,
                9=9,
                A=10,
                B=11,
                C=12,
                D=13,
                E=14,
                F=15
        ],
        digits = Text.ToList(Text.Upper(input)),
        dim = List.Count(digits)-1,
        exp = if base=null then 16 else base,
        Result = List.Sum(
                        List.Transform(
                                {0..dim}
                                , each Record.Field(values, digits{_}) * Number.Power(exp, dim - _)
                                )
                        )
in
        Result

As you see in the code above, the base parameter is optional, so if not provided base 16 would be the default.

This is how we can invoke the above function:

fnHex2Dec("AbCdEf", null)
Invoking fnHex2Dec function to convert numbers of any base to decimal
Continue reading “Quick Tips: Converting Hexadecimal, Oct and Binary to Decimal in a Single Power Query Function”

Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular

I wrote some other posts on this topic in the past, you can find them here and here. In the first post I explain how to create “Time” dimension with time bands at minutes granularity. Then one of my customers required the “Time” dimension at seconds granularity which encouraged me to write the second blogpost. In the second blogpost though I didn’t do time bands, so here I am, writing the third post which is a variation of the second post supporting time bands of 5 min, 15 min, 30 min, 45 min and 60 min while the grain of the “Time” dimension is down to second. in this quick post I jump directly to the point and show you how to generate the “Time” dimension in three different ways, using T-SQL in SQL Server, using Power Query (M) and DAX. Here it is then:

Time Dimension at Second Grain with Power Query (M) Supporting Time Bands:

Copy/paste the code below in Query Editor’s Advanced Editor to generate Time dimension in Power Query:

let
Source = Table.FromList({1..86400}, Splitter.SplitByNothing()),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "ID"}}),
    #"Time Column Added" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0,0,0,[ID]))),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time])),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time])),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/5) * 5, 0))  +  #duration(0, 0, 5, 0)),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/15) * 15, 0))  +  #duration(0, 0, 15, 0)),
    #"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/30) * 30, 0))  +  #duration(0, 0, 30, 0)),
    #"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/45) * 45, 0))  +  #duration(0, 0, 45, 0)),
    #"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,0,0,0) + #duration(0, 0, Number.RoundDown(Time.Minute([Time])/60) * 60, 0))  +  #duration(0, 0, 60, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"60 Min Band Added",{"Time", "Hour", "Minute", "5 Min Band", "15 Min Band", "30 Min Band", "45 Min Band", "60 Min Band"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Time", type time}, {"Hour", Int64.Type}, {"Minute", Int64.Type}, {"5 Min Band", type time}, {"15 Min Band", type time}, {"30 Min Band", type time}, {"45 Min Band", type time}, {"60 Min Band", type time}})
in
#"Changed Type"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular”

Quick Tips: How to Sort Matrix by Column in Descending Order

How to Sort Matrix by Column in Descending Order

Today Microsoft released Power BI Desktop March 2020 which I was hoping that it includes a simple feature on Matrix visual to be able to sort the Martix by column in descending order, but, it doesn’t. So, in this post I quickly show you how to sort Matrix by column in descending order.

Here is the scenario. One of my customers is building a report in Power BI showing sales by Year, Month and Day of Week in a Matrix as below.

Sorting Matrix Visual in Power BI
Sorting Matrix Visual in Power BI

Everything looks fine! But looking at the Matrix sorting quickly reveals that such feature is NOT available (YET). But the customer would like to see the Matrix sorted by Year in descending order, something like this.

Sorting Matrix by Column Headers in Descending Order
Sorting Matrix by Column Headers in Descending Order

Here is the solution which is super simple.

Continue reading “Quick Tips: How to Sort Matrix by Column in Descending Order”

Empower Your Story Telling Data Visualisation in Power BI with Colour Coding

Colour Coding in Power BI

This post has been waiting in my blogging list for a while and now this is my last post in 2019. I wish you all have a wonderful year ahead.

In this post I discuss a very important aspect of data visualisation; Colour Coding. I believe, colour coding is one the most powerful and efficient ways to provide proper information to the users. We learnt as human being that the colour can tell a lot about things. For instance, we look at green grass, if it is light green we immediately understand that the grass is quite fresh and healthy. When she gets a bit yellowish, we know that she’s perhaps thirsty. When it gets brown it is probably too late.

Another perfect example is traffic lights. When it is green, everyone is happy, when it is yellow, everyone is racing to pass the junction, well, I’m just kidding, some people tend to pass the yellow light while everyone knows they have to stop when traffic light is yellow right?? And… when it is red, we have to stop. Enough saying about colour coding and its affects on our lives on a day to day basis. Let’s talk about colour coding in Power BI and quickly get to more exciting stuff.

So… colour coding in Power BI, well, we could colour code from the day first that Power BI born, but, perhaps not in a way that I’m going to explain in this post. Conditional formatting is also around for a while now. In this post I show a technique that we can implement in Power BI to use a consistent colour coding across the whole report.

Here is a report without colour coding:

  Power BI Report without Colour Coding
Power BI Report without Colour Coding

And now look the same report that is colour coded:

 Colour Coded Power BI Report
Colour Coded Power BI Report

Let’s get into it.

Getting Started

In this technique we’ll follow the steps below:

  • We jump online using some awesome free colour palette websites to generate the colours we’d like to use in our reports
  • We copy the HEX values and paste into Power BI (via Enter Data)
  • We define a range of numbers to identify the ranges that our values will fall into. I personally use percentage, but it might be something else in your case
  • We then define some measures to pick a specific colour for the measures we want to colour code
Continue reading “Empower Your Story Telling Data Visualisation in Power BI with Colour Coding”