How to convert Hex to text and Hex to Datetime

[fabioRond]

Hi,

I have a data source from a DB that stores most of the data as a binary that were firstly converted into hex.

I am trying to find a formula to convert hex to text (ASCII) but I do not get what passages are required.

the format of the data is text in hex numbers and I would need to bring them to text ASCII ("4551303237" => "EQ027").

I tried to convert them to decimal and then to text but it does not work.

I tried this method from @Greg_Deckler 

https://community.powerbi.com/t5/Desktop/convert-from-hexadecimal-value-to-number/td-p/28435

then this

https://xxlbi.com/blog/converting-hexadecimal-to-decimal-numbers-in-power-query/

then this 

https://community.powerbi.com/t5/Desktop/convert-from-hexadecimal-value-to-number/td-p/28435

no luck so far even if with this page I get the conversion on the first attempt (http://www.unit-conversion.info/texttools/hexadecimal/)

I also tried using Text from or BinaryFormat.Text(2, TextEncoding.Ascii)

I was able to get a date out of the hex data using this formula but the year is not correct, probably because it should be DateTime. (40468AE5D5C2E02C =>13/02/1900)

Date.From( BinaryFormat.ByteOrder(BinaryFormat.Double, ByteOrder.BigEndian)( Binary.FromText([#"HEX(fom.Value)"], BinaryEncoding.Hex))) )  

Seems a trivial thing to do but I was not able to understand why Power Query is not covered

[Greg_Deckler]

@fabioRond So you are starting with a hex value of 4551303237 and then you want to convert it to "text" of "EQ027"? I'm not sure how one equates to the other.

[fabioRond]

@Greg_Deckler  That is done easily in MatLab with a simple conversion Base 16 (hexadecimal) and to char. so the 45 is translated to 69 and then converted to Ascii char "E". I am not so expert to reconstruct the same approach with a function in power query

[fabioRond · Accepted solution]

Found the solution probably could help someone else. @Greg_Deckler not sure if is the correct approach, hope make more sense to expert users .

= (textHEX as text , Loop as number , optional Value as text) =>
let
totalLoops = Text.Length(textHEX) ,
CurrentLoop = Loop + 2,
CurrentValue = Value & Character.FromNumber(Expression.Evaluate("0x" & Text.Range(textHEX, Loop, 2))),
output =
if CurrentLoop >= totalLoops
then CurrentValue
else @TestFunction(textHEX,CurrentLoop, CurrentValue)

in
output

[mh2587]

referring this article may be you get some idea
https://dax.tips/2019/10/02/dax-base-conversions/

[fabioRond]

unfortunately is not helping

my data are already as hex which then is converted in MatLab directly into text using ASCII.

I am not using DAX but power query.

was trying to replicate the same step used with python using the BinaryFormat

https://www.adamsmith.haus/python/answers/how-to-convert-a-string-from-hex-to-ascii-in-python