Get data in custom column with a variable link

[OmarTalk2IoT]

Hello,

I am currently trying to have a variable URL with data.

An example of a link where my data will be retrieved from is: 

http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=0200058209273a7dd786000000000000

I have the payloads loaded into powerBI from my sensor and then converted into a custom column named "Hex value".

The idea is to automatically load the data from the website by using a new "variable" link:

http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=[Hex value]

The problem is whenever I make the link variable the program says access to the resource is forbidden, with the option to edit my references, yet nothing works.

The code:

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"}),
    #"Aangepaste kolom toegevoegd2" = Table.AddColumn(#"Volgorde van kolommen gewijzigd2", "Aangepast", each Web.Contents("http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=[Hex value]")),
    #"JSON-bestand geparseerd" = Table.TransformColumns(#"Aangepaste kolom toegevoegd2",{{"Aangepast", Json.Document}}),
    #"Aangepast uitgevouwen" = Table.ExpandRecordColumn(#"JSON-bestand geparseerd", "Aangepast", {"MsgID", "BaromBar", "Temp", "Humidity", "LevelX", "LevelY", "LevelZ", "VibAmp", "VibFreq"}, {"Aangepast.MsgID", "Aangepast.BaromBar", "Aangepast.Temp", "Aangepast.Humidity", "Aangepast.LevelX", "Aangepast.LevelY", "Aangepast.LevelZ", "Aangepast.VibAmp", "Aangepast.VibFreq"})
in
    #"Aangepast uitgevouwen"

I hope someone can help me out!

[Anonymous · Accepted solution]

@OmarTalk2IoT,

Could you please post he M code when you directly get data from http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=0200058209273a7dd786000000000000?

In Power BI Desktop, you can create a function by creating a PL parameter based on the above code, then invoke the function in the payloads table. There is a similar blog for your reference.

http://blogs.adatis.co.uk/callumgreen/post/Loop-Through-Webpages-with-Multiple-Parameters-in-Power-Q...

Regards,
Lydia

[OmarTalk2IoT]

@Anonymous, 

"In Power BI Desktop, you can create a function by creating a PL parameter based on the above code, then invoke the function in the payloads table. There is a similar blog for your reference.

http://blogs.adatis.co.uk/callumgreen/post/Loop-Through-Webpages-with-Multiple-Parameters-in-Power-Q..."

Following this was the solution, thank you!

[OmarTalk2IoT]

@Anonymous,

let
    Bron = Json.Document(Web.Contents("http://1m2m.eu/services/GETPAYLOAD?Human=0&PL=0200058209273a7dd786000000000000"))
in
    Bron

Do you mean like this?

Thank you for responding.