Forum Discussion
Resolver Iconvert from hexadecimal value to number
BIXL - I created 2 functions that allow you to simply pass a hexadecimal value to fnHex2Dec2 in order to convert it to Decimal.
First one is basically the same as before:
let fnHex2Dec = (input) => let values = { {"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} }, Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number}) in Result in fnHex2Dec2nd one is the one that you want to pass in your hex value to:
let
fnHex2Dec2 = (input) =>
let
Reverse = List.Reverse(Text.ToList(input)),
DecimalValues = List.Transform(Reverse, each List.First(fnHex2Dec(_)) * Number.Power(16,List.PositionOf(Reverse,_))),
Return = List.Sum(DecimalValues)
in
Return
in
fnHex2Dec2Just makes sure that your input column is of type Text and then you can add a custom column like:
=fnHex2Dec2([Hex])
And you will get back the decimal value.
Community ChampionWell, I'm sure somebody else has a better method, but this might get you pointed down the right track. First, create a blank query.
let
fnHex2Dec = (input) =>
let
values = {
{"A", 10},
{"B", 11},
{"C", 12},
{"D", 13},
{"E", 14},
{"F", 15}
},
Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number})
in
Result
in
fnHex2DecThen, you can split out your hex number based upon 1 character each and create columns like the following:
try [Hex.1]*Number.Power(16,3) otherwise fnHex2Dec([Hex.1]){0} * Number.Power(16,3)And then just add them all together. Here is the full text of the 2nd query where I used your number and got the right answer:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnexdFSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Hex = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Hex", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Hex",Splitter.SplitTextByRepeatedLengths(1),{"Hex.1", "Hex.2", "Hex.3", "Hex.4"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Hex.1", Int64.Type}, {"Hex.2", type text}, {"Hex.3", Int64.Type}, {"Hex.4", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Dec.1", each try [Hex.1]*Number.Power(16,3) otherwise fnHex2Dec([Hex.1]){0} * Number.Power(16,3)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Dec.2", each try [Hex.2]*Number.Power(16,2) otherwise fnHex2Dec([Hex.2]){0} * Number.Power(16,2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Dec.3", each try [Hex.3]*Number.Power(16,1) otherwise fnHex2Dec([Hex.3]){0} * Number.Power(16,1)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Dec.4", each try [Hex.4]*Number.Power(16,0) otherwise fnHex2Dec([Hex.4]){0} * Number.Power(16,0)),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Decimal", each [Dec.1]+[Dec.2]+[Dec.3]+[Dec.4])
in
#"Added Custom4"I'm sure that you could probably write a single function in M to convert your hex to decimal. If I have some additional time, I'll take a look at doing that all within a single M function. Probably could iterate over the input list and do some calculations to get the right answer.
Community ChampionDon't you wish that external file was Excel? Would save you lot of trouble.
I knew they added added more functions to DAX - so I tried HEX2DEC( ) - Nope. Sorry! Not yet.
- Greg_Deckler
BIXL - I created 2 functions that allow you to simply pass a hexadecimal value to fnHex2Dec2 in order to convert it to Decimal.
First one is basically the same as before:
let fnHex2Dec = (input) => let values = { {"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} }, Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number}) in Result in fnHex2Dec2nd one is the one that you want to pass in your hex value to:
let
fnHex2Dec2 = (input) =>
let
Reverse = List.Reverse(Text.ToList(input)),
DecimalValues = List.Transform(Reverse, each List.First(fnHex2Dec(_)) * Number.Power(16,List.PositionOf(Reverse,_))),
Return = List.Sum(DecimalValues)
in
Return
in
fnHex2Dec2Just makes sure that your input column is of type Text and then you can add a custom column like:
=fnHex2Dec2([Hex])
And you will get back the decimal value.
BIXLHi Smoupre,
Thanks for the detailed solution.
It's seems that the first function is missing the "0" convert. Without it it gives an error when trying to convert values containing zero.
beside that it's all good !
thanks again.
- Greg_Deckler
Good catch. **bleep** details.