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.
Sucessfully accessed our data in statuscake https://www.statuscake.com/
First created a function that gets the uptime intervals data for each test
let GetTestPeriods = (TestID as text) =>
let
Source = Web.Contents("https://www.statuscake.com/API/Tests/Periods", [Headers=[#"API"="xxxxxxxxxx",#"Username"="xxxxxx"], Query=[#"TestID"=TestID]]),
#"Imported JSON" = Json.Document(Source),
#"Converted to Table" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Status", "StatusID", "Start", "End", "Period", "Additional"}, {"Status", "StatusID", "Start", "End", "Period", "Additional"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Start", type datetime}, {"End", type datetime}})
in
#"Changed Type"
in
GetTestPeriods
And then a query that gets a list of all the tests and then iterates through them using the GetData function to get the data from each website.
let
Source = Json.Document(Web.Contents("https://www.statuscake.com/API/Tests/", [Headers=[#"API"="xxxxxxxxxx",#"Username"="xxxxx"]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"TestID", "Paused", "TestType", "WebsiteName", "ContactGroup", "ContactID", "Public", "Status", "NormalisedResponse", "Uptime"}, {"TestID", "Paused", "TestType", "WebsiteName", "ContactGroup", "ContactID", "Public", "Status", "NormalisedResponse", "Uptime"}),
#"Filtered Removed Paused" = Table.SelectRows(#"Expanded Column1", each ([Paused] = false)),
#"Sorted Rows" = Table.Sort(#"Filtered Removed Paused",{{"Uptime", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"TestID", type text}}),
#"Added Interval Data" = Table.AddColumn(#"Changed Type", "Custom", each GetTestPeriods([TestID])),
#"Expanded Interval Data" = Table.ExpandTableColumn(#"Added Interval Data", "Custom", {"Status", "StatusID", "Start", "End", "Period", "Additional"}, {"Status.1", "StatusID", "Start", "End", "Period", "Additional"}),
#"Changed to DateTime" = Table.TransformColumnTypes(#"Expanded Interval Data",{{"Start", type datetime}, {"End", type datetime}}),
#"Added Invterval Column" = Table.AddColumn(#"Changed to DateTime", "Interval", each [End]-[Start])
in
#"Added Invterval Column"
Hi,
I know it has been some time since you posted this, but would you mind describing exactly how you set this up?
Kind regards
Nick
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.