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
[/sourcecode]
I’m sure this will come in handy somewhere…
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?
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
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).
Thanks, Chris! I knew that I archived this post with good reason. Do you know to similarly convert a decimal number to binary?
No easy way, I think – but plenty of solutions if you Google for it for example https://community.powerbi.com/t5/Desktop/Converting-Decimal-number-into-Binary/td-p/287541
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
)
=>
let
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 )
in
number_tobinarytext_function
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
And here’s a function:
let
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.Upper(
Text.PadStart( Number.ToText(R,”x”), 2, “0”) &
Text.PadStart( Number.ToText(G,”x”), 2, “0”) &
Text.PadStart( Number.ToText(B,”x”), 2, “0”)
)
in
Result
in
Source