Forum Discussion
Converting Base64 payload to Hex value in Power Query M
Hello,
I receive my payload in Base64, but I need to convert it into a Hexadecimal value so I can retrieve the readable sensor data from a website: http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=0200057b094a3a00e57d000000000000. Which will make the hex value a variable http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=[hex value] so the data will load into my table automatically whenever a new base64 payload from my sensor comes in.
Thanks in advance!
Hi OmarTalk2IoT,
After play with your data, I find you direct replace base64 string with your column name:
#"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Volgorde van kolommen gewijzigd1", "Hex value", each Binary.ToText(Binary.FromText("[Base64 payload]", BinaryEncoding.Base64),BinaryEncoding.Hex))Each keyword with [Base64 payload] means each column value, "[Base64 payload]" means static text with who contains "[]".
Modified query formula:
let Bron = let Source = Json.Document(Web.Contents("https://1608.data.thethingsnetwork.org/api/v2/query/1608-2?last=1d", [Headers=[Accept="application/json", Authorization="key ttn-account-v2.iSPdLPG1f6VbmWskFDqZ0X91rRss16x3psldNj9XD40"]])), messages = Source[messages] in Source, #"Geconverteerd naar tabel1" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Headers met verhoogd niveau" = Table.PromoteHeaders(#"Geconverteerd naar tabel1", [PromoteAllScalars=true]), #"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Headers met verhoogd niveau", "Column1", {"device_id", "raw", "time"}, {"device_id", "raw", "time"}), #"Type gewijzigd" = Table.TransformColumnTypes(#"Column1 uitgevouwen",{{"device_id", type text}, {"raw", type text}, {"time", type datetime}}), #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Type gewijzigd",{{"raw", "Base64 payload"}}), #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Namen van kolommen gewijzigd", "Melding", each if[Base64 payload]="gIQFAAEIBAABAAY=" then "Test Mode" else if[Base64 payload]="gIQFAAEIAAABAAY=" then "Smoke Alarm Triggered!" else "Low Battery Alarm Triggered!"), #"Volgorde van kolommen gewijzigd" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd",{"device_id", "Melding", "time", "Base64 payload"}), #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Volgorde van kolommen gewijzigd",{{"time", "Tijd"}, {"device_id", "Device_ID"}}), #"Kolommen verwijderd" = Table.RemoveColumns(#"Namen van kolommen gewijzigd1",{"Melding"}), #"Volgorde van kolommen gewijzigd1" = Table.ReorderColumns(#"Kolommen verwijderd",{"Device_ID", "Base64 payload", "Tijd"}), #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Volgorde van kolommen gewijzigd1", "Hex value", each Binary.ToText(Binary.FromText([Base64 payload], BinaryEncoding.Base64),BinaryEncoding.Hex)), #"Volgorde van kolommen gewijzigd2" = Table.ReorderColumns(#"Aangepaste kolom toegevoegd1",{"Device_ID", "Base64 payload", "Hex value", "Tijd"}) in #"Volgorde van kolommen gewijzigd2"Regards,
Xiaoxin Sheng
7 Replies
Hi OmarTalk2IoT,
I think binary functions will suitable for your requirement, you can try to use below power query functions encode and decode your characters.
Reference:
Binary.ToText Encodes binary data into a text form. Binary.FromText Decodes data from a text form into binary. Sample:
Custom = Binary.ToText(Binary.FromText("0200057b094a3a00e57d000000000000", BinaryEncoding.Base64),BinaryEncoding.Hex)Regards,
Xiaoxin Sheng
Hello Anonymous,
My Base64 needs to be converted:
[64] AgAFewlKOgDlfQAAAAAAAA== --> [16] 0200057b094a3a00e57d000000000000
When I use your function it doesn't seem to work because the converted value is not what it is supposed to be.
Hi OmarTalk2IoT,
My formula is m query, you need to use it in query editor side.
Full query:
let Source = Binary.ToText(Binary.FromText("AgAFewlKOgDlfQAAAAAAAA==", BinaryEncoding.Base64),BinaryEncoding.Hex) in SourceRegards,
Xiaoxin Sheng
