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”

Power BI Governance, Good Practices, Part 2: Version Control with OneDrive, Teams and SharePoint Online

Power BI Governance, Version Control with OneDrive for Business, Microsoft Teams and SharePoint Online

One of the most important aspects of the software development life cycle is to have control over different versions of a solution, especially in a project where there is more than one developer involved in the implementation. Just like when you normally create a project in visual studio and you commit the changes back to a source control system like GitHub or Azure DevOps, it’s advised to keep the history of different versions of your Power BI reports. What we expect from a source control solution is to keep tracking of all changes happening in the source code while developing a project. So you can easily roll back to a previous state if you like to. 

The other benefit of having a source control process in place is when multiple developers are working on a single project. Every single one of them makes changes in the source code then they commit all the changes into the source control server without overwriting each others’ work. 

With Power BI things are a bit different though. Power BI report files are PBIX files which are stored in binary format (well, PBIX is basically a zip file isn’t it?) which at the time of writing this post, there is no official way to enforce Power BI source control in any source control solutions like GitHub or Azure DevOps (YET). 

Microsoft announced a fantastic feature last week (6/05/2020) named “Deployment Pipelines” which does exactly what we’re after, but it is currently a preview feature which is only available only to organisations with Power BI Premium. So it is out of the game for the majority of us.

Having said that, there is still a way to keep history of changes in the shape of different versions of PBIX files. This is called Version Control.

There are several ways you can enable version control over your PBIX files while developing the report. Regardless of the version control platform you need to think about having multiple environments and who can access them for doing what.

EnvironmentAccessible toDescription
DevelopmentDevelopersData modellers and report writers access this environment for development purposes.  
User Acceptance Test (UAT)Developers, SMEs, Technical Leads, Power BI AdminsAfter the development is finished the developers deploy the solution to the UAT environment. The solution will then be tested by SMEs (Subject Matter Experts) to make sure the business requirements are met.
Pre-prod (Optional but recommended)Technical Leads, Power BI AdminsAfter the solution passed all UAT testing scenarios Technical Leads or Power BI Admins will deploy it to Pre-prod for final checks to make sure all data sources are correctly pointing to production data sources and all reports and dashboards are working as expected.  
ProductionTechnical Leads, Power BI Admins, End UsersAfter pre-prod checks completed Technical Leads or Power BI Admins deploy the solution to the Production environment which is then available to the end users.

Version Control Options

If your organisation does not have a Premium capacity then “Deployment Pipelines” feature is not available to you. So you need to come up with a solution though. In this section I name some Version Control options available to you

  • OneDrive for Business
  • Microsoft Teams/SharePoint Online
Continue reading “Power BI Governance, Good Practices, Part 2: Version Control with OneDrive, Teams and SharePoint Online”

Introducing Power BI Governance and Adoption Good Practices Series

Introducing Power BI Governance Series

In my experience working in IT industry from early 2000 one aspect of dealing with data that is always taken very seriously is data governance.

For many years the industry learnt how important it is to have a data governance process in place to keep your data safe and healthy so you get the most out of your data. Power BI is no different to any other data platforms, so data governance is an important part of it. However, the concept of self-service data management which offers all the beauties and power an agile approach caused some organisations not to think about their governance approach from the outset.

On the other hand, some people find governance as a burden which slows down the adoption. Some people think even worse, they think governance is a stopper which puts a lot of unnecessary restrictions in place which decreases efficiency.

But in reality if you start data governance planning sooner than later you can protect your organisation from a lot of risks down the road which can get really nasty and costly like:

  • Risk of law suits due to data leakage and privacy issues
  • Untrusted data analysis and reporting due to poor quality of data
  • Poor performing solution due to lack of auditing
  • Inefficient development outcomes due to undefined environments
Risks of lack of governance
Continue reading “Introducing Power BI Governance and Adoption Good Practices Series”