Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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 @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.
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
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..
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |