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"
