Hello,I am looking for a way to get an hexadecimal value from external file and in Power Query to convert it to a number.can't find the proper function for it... for example the file value is : 7D9A and after converting it from hex to number I'm expecting to get : 32154 Thanks

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) =>letReverse = List.Reverse(Text.ToList(input)),DecimalValues = List.Transform(Reverse, each List.First(fnHex2Dec(_)) * Number.Power(16,List.PositionOf(Reverse,_))),Return = List.Sum(DecimalValues)inReturnin 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.

convert from hexadecimal value to number | Microsoft Fabric Community

11 Replies

Greg_Deckler

Community Champion

10 years ago

Well, 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
    fnHex2Dec

Then, 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.

Sean
Community Champion
10 years ago
Don'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
  Community Champion
  10 years ago
  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 fnHex2Dec
  2nd 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
  fnHex2Dec2
  Just 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.

brent_railey

New Member

6 years ago

I took the above and got it all into one function and validated:

Hex2Dec = (input as text) =>
let
    convertHexDigit = (digit) =>
    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}
        },
        Result = Value.ReplaceType(
            {List.First(List.Select(values, each _{0} = digit)){1}}, 
            type {number}
        )
    in
        Result,
    Reverse = List.Reverse(Text.ToList(input)) // In greatest to smallest,
    noDigits = List.Numbers(0, List.Count(Reverse) - 1) // Order of magnitude,
    DecimalValues = List.Transform(noDigits, each List.First(convertHexDigit(Reverse{_})) * Number.Power(16, _)),
    Return = List.Sum(DecimalValues)
in 
    Return

Forum Discussion

convert from hexadecimal value to number