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])), Time.Type),
    #"Hour Added" = Table.AddColumn(#"Time Column Added", "Hour", each Time.Hour([Time]), Int64.Type),
    #"Minute Added" = Table.AddColumn(#"Hour Added", "Minute", each Time.Minute([Time]), Int64.Type),
    #"5 Min Band Added" = Table.AddColumn(#"Minute Added", "5 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/5) * 5) + 5, 0)), Time.Type),
    #"15 Min Band Added" = Table.AddColumn(#"5 Min Band Added", "15 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/15) * 15) + 15, 0)), Time.Type),
#"30 Min Band Added" = Table.AddColumn(#"15 Min Band Added", "30 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/30) * 30) + 30, 0)), Time.Type),
#"45 Min Band Added" = Table.AddColumn(#"30 Min Band Added", "45 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/45) * 45) + 45, 0)), Time.Type),
#"60 Min Band Added" = Table.AddColumn(#"45 Min Band Added", "60 Min Band", each Time.From(#datetime(1970,1,1,Time.Hour([Time]),0,0) + #duration(0, 0, (Number.RoundDown(Time.Minute([Time])/60) * 60) + 60, 0)), Time.Type),
    #"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"})
in
    #"Removed Other Columns"
Continue reading “Quick Tips: Time Dimension with Time Bands at Seconds Granularity in Power BI and SSAS Tabular”

Quick Tips: How to Copy Visual Formatting to Multiple Other Visuals in One Go Using Format Painter Tool in Power BI

When you create a report it’s highly likely that you’d like to copy other visuals’ formats from an already formatted visual using “Format Painter” tool in Power BI. Perhaps you already used this awesome tool available in Power BI Desktop.

As you see in the tooltip shown while hovering over the “Format Painter” tool you can simply copy formats from an already formatted visual to another visual. But what if you have a lot of similar visuals to be formatted (painted) like shown in the below screenshot that I have several card visuals on top of my page. One of them is formatted as desired but the rest must still be formatted.

Formatted/Unformatted Card Visuals in Power BI
Formatted/Unformatted Card Visuals

It would be good if I could paint all of them in one go right? So continue reading to see how we can do that.

Continue reading “Quick Tips: How to Copy Visual Formatting to Multiple Other Visuals in One Go Using Format Painter Tool in Power BI”

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”