Forum Discussion
Solved
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=0...
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
Hello Anonymous,
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.
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