M · Power BI · Power Query

Converting Decimal Numbers To Hexadecimal In Power Query M

This is a very short post! A lot of people have blogged about how to convert numbers between different bases in M (see for example Maxim Zelensky’s very elegant solution for converting from binary to decimal), but today I noticed there was a very easy way to convert a decimal number to hexadecimal using the Number.ToText() function: you just need to use “x” in the second parameter. For example:

[sourcecode language=”text” padlinenumbers=”true”]
Number.ToText(12, "x") //returns c
Number.ToText(123, "x") //returns 7b

I’m sure this will come in handy somewhere…

9 thoughts on “Converting Decimal Numbers To Hexadecimal In Power Query M

  1. This is excellent, Chris!

    What’s your preferred approach for converting hexadecimal numbers into decimal? The shortest approach I’ve found so far was by Igor Cotruta:

    Expression.Evaluate ( “0x” & HexString )

    In your opinion, is Expression.Evaluate always safe to use?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Good question – I don’t really know whether it is safe to use or not, but I do remember the comment that Curt Hagenlocher of the dev team left on this (quite old) post: https://blog.crossjoin.co.uk/2014/02/04/loading-power-query-m-code-from-text-files/ regarding the fact that it might cause problems in some cases

      1. The Power BI service still relies on static analysis for data source discovery, so doing data access via Expression.Evaluate can be problematic in that context. There’s no other specific concern I can think of; the use of #shared is probably a bigger “future risk” than Expression.Evaluate (though of course the two are often used together).

      1. Hi Chris

        I’ve been meaning to reply to this. This is the one I that wrote up with for my work scenario, it’s a lot simpler than the one in your link. I can’t remember where I got the recursive algorithm from, it was something that I found on Google.

        1. Create a blank query, name it “number_tobinarytext”, and paste in this function below.

        number_parameter as number,
        optional text_from_parameter as nullable text


        text_from = if text_from_parameter = null then “” else text_from_parameter,
        number_tobinarytext_function = if number_parameter <= 1 then Text.From(number_parameter) & text_from else @number_tobinarytext( Number.RoundDown( number_parameter / 2 ), Text.From( Number.Mod( number_parameter, 2 ) ) & text_from )



  2. I am trying to adapt your code to work binary to hex , I have an example of where I have 110000000101100111011110 and want it converting to it’s hex code which is C059DE

  3. And here’s a function:

    Source = (RGB as text) => let
    // RGB formatted “00, 00, 00”

    R = Number.FromText( Text.BeforeDelimiter(RGB, “,”)),
    G = Number.FromText(Text.BetweenDelimiters(RGB, ” “, “,”)),
    B = Number.FromText(Text.AfterDelimiter(RGB, ” “, 1)),
    Result =
    “#” &
    Text.PadStart( Number.ToText(R,”x”), 2, “0”) &
    Text.PadStart( Number.ToText(G,”x”), 2, “0”) &
    Text.PadStart( Number.ToText(B,”x”), 2, “0”)

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.