Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soldstatic
Resolver I
Resolver I

REST API With Authentication and Dynamic Variables

I spent a good portion of my vacation day yesterday screwing around on a personal project with my home brew beer setup. I am using Fireboard thermometer device to monitor temperatures. Eventually I will also likely use this for control as well, but I haven't gotten there yet. I am having a HELL of a time manipulating the values in power query though, so hoping someone can help me.

 

This is what I am using to get the auth token:

FireboardAuthorizationKey:
= let
   body = "{""username"":""**@**.com"",""password"":""**""}",
   Data= Web.Contents("https://fireboard.io/api/rest-auth/login/",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source

 This produces a record 'key'. I would love to be able to just directly reference this but I can't figure out how to do that, so instead I converted it to a table, merged it with the string " Token " (because I can't figure out how to concatenate the string in powerquery), then converted it to a list and kept only the first item (even though it only returned one). Then I made a parameter based on this list, with the current value copy and pasted out of that. I don't think this is a good way to do it, but apparently it kind of works because now I'm able to get a list of sessions using the following:

Sessions:
= let
   Data= Web.Contents("https://fireboard.io/api/v1/sessions.json",[Headers=[#"Authorization"=AuthString]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source

I convert that to a table, then expand the column so that all columns of session data are exposed and I get something that looks pretty solid:

FBSessions.png

Now I want to get the chart data for the sessions so I can start to plot the data. So I want to do something like this but I don't know how to make it parse through the list of Session IDs.

ChartData:
= let
   Data= Web.Contents("https://fireboard.io/api/v1/sessions/"&SessionIDs&"/chart.json",[Headers=[#"Authorization"=AuthString]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source

 

1 ACCEPTED SOLUTION

OK thank you for your help! I'm finally getting somewhere! So this is what I am now using to authenticate:

= let
   body = "{""username"":""***@***.com"",""password"":""***""}",
   Data= Web.Contents("https://fireboard.io/api/rest-auth/login/",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source

 

And this is what I'm using to parse the session data:

Sessions:
= let
   Data= Web.Contents("https://fireboard.io/api/v1/sessions.json",[Headers=[#"Authorization"=Text.Combine({" Token ",Record.Field(AuthRecord,"key")})]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source
= let 
Prev = #"Removed Other Columns",
Step = Table.AddColumn(Prev,"Chart", each
  Json.Document(
    Web.Contents(Text.Combine({"https://fireboard.io/api/v1/sessions/" , Text.From([id]) , "/chart.json"}),[Headers=[#"Authorization"=Text.Combine({" Token ",Record.Field(AuthRecord,"key")})]]
    )
  )
)
in Step

So this gets a list of objects for each session which is wonderful. After expanding that list column and then expanding the records I get the following which is great:

ChartData.png

 

 

 

Then I followed an example about how to zip the two columns together to then expand them in order to get the x/y pairs in a table:

= let
   Prev = #"Expanded x",
    Add_Zipped = Table.AddColumn(Prev, "Zipped", each List.Zip({[x], [y]}), type list),
    Expand_Zipped = Table.ExpandListColumn(Add_Zipped, "Zipped"),
    Extract_Zipped = Table.TransformColumns(Expand_Zipped, {"Zipped", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split_Column = Table.SplitColumn(Extract_Zipped, "Zipped", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Zipped.1", "Zipped.2"})
in
    Split_Column

 From here I basically just did some type conversions and used this to convert the unix timestamp to a datetime:

Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Zipped.1]))

So from here I'm finally able to graph something! Will post back soon when I have an update! 

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

Hi @soldstatic ,

Maybe the links can help you a little bit.

https://community.powerbi.com/t5/Desktop/RestApi-with-Session-Authentication/td-p/624578

https://stackoverflow.com/questions/9773664/rest-api-token-based-authentication

https://community.powerbi.com/t5/Desktop/Passing-Variables-in-API-Post-Request-JSON-Body/td-p/372827

 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OK thank you for your help! I'm finally getting somewhere! So this is what I am now using to authenticate:

= let
   body = "{""username"":""***@***.com"",""password"":""***""}",
   Data= Web.Contents("https://fireboard.io/api/rest-auth/login/",[Content=Text.ToBinary(body),Headers=[#"Content-Type"="application/json"]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source

 

And this is what I'm using to parse the session data:

Sessions:
= let
   Data= Web.Contents("https://fireboard.io/api/v1/sessions.json",[Headers=[#"Authorization"=Text.Combine({" Token ",Record.Field(AuthRecord,"key")})]]),
   DataRecord = Json.Document(Data),
   Source=DataRecord 
in
   Source
= let 
Prev = #"Removed Other Columns",
Step = Table.AddColumn(Prev,"Chart", each
  Json.Document(
    Web.Contents(Text.Combine({"https://fireboard.io/api/v1/sessions/" , Text.From([id]) , "/chart.json"}),[Headers=[#"Authorization"=Text.Combine({" Token ",Record.Field(AuthRecord,"key")})]]
    )
  )
)
in Step

So this gets a list of objects for each session which is wonderful. After expanding that list column and then expanding the records I get the following which is great:

ChartData.png

 

 

 

Then I followed an example about how to zip the two columns together to then expand them in order to get the x/y pairs in a table:

= let
   Prev = #"Expanded x",
    Add_Zipped = Table.AddColumn(Prev, "Zipped", each List.Zip({[x], [y]}), type list),
    Expand_Zipped = Table.ExpandListColumn(Add_Zipped, "Zipped"),
    Extract_Zipped = Table.TransformColumns(Expand_Zipped, {"Zipped", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split_Column = Table.SplitColumn(Extract_Zipped, "Zipped", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Zipped.1", "Zipped.2"})
in
    Split_Column

 From here I basically just did some type conversions and used this to convert the unix timestamp to a datetime:

Table.AddColumn(#"Changed Type", "Custom", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Zipped.1]))

So from here I'm finally able to graph something! Will post back soon when I have an update! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.