Converting Base64 payload to Hex value in Power Query M

[OmarTalk2IoT]

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!

[v-shex-msft]

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

[OmarTalk2IoT]

Hello @v-shex-msft,

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.

[v-shex-msft]

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
    Source

Regards,

Xiaoxin Sheng

[OmarTalk2IoT]

Hi @v-shex-msft,

It worked!! It converted the base64 into a hex value. Now I am facing the problem that when I substitute the AgAFewlKOgDlfQAAAAAAAA== with [Base64 payload] (which is the title of the column where my base64 gets put), it says:

Expression.Error: Ongeldige binaire codering. //invalid binair coding
Details:
[Base64 payload]

It refers to a valid base64 value (the one I mentioned earlier) since it's a variable. I don't get it..

[v-shex-msft]

Hi @OmarTalk2IoT,

These functions only works on base64 characters, I think you need to extract specific text from url string before convert them.

let
    Source = {"http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=AgAFewlKOgDlfQAAAAAAAA=="},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Base64", each Text.End([Column1],Text.Length([Column1])-Text.PositionOf([Column1],"PL=")-3)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Hex URL", each Text.Start([Column1],Text.PositionOf([Column1],"PL=")+3)
& Binary.ToText(Binary.FromText([Base64], BinaryEncoding.Base64),BinaryEncoding.Hex))
in
    #"Added Custom1"

Regards,

Xiaoxin Sheng

[OmarTalk2IoT]

Hello @v-shex-msft,

I'm sorry about all the questions. I am new to Power Query M and Power BI as a program.

My current code looks like this:

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"

When the column "Hex value" is no longer an error, I will make another column which will get the information from http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=[Hex value]. Then the column will show as "Record" which I can click to open the data I actually want. I don't know if you could make any changes to the code which will fix it?

Thank you so much already, you have helped me a lot.

[v-shex-msft · Accepted solution]

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