Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to 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:
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!
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.
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:
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |