Not Monitored
Tag not monitored by Microsoft.
38,692 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
let
Source = Json.Document(Web.Contents("https://api.keyedinprojects.co.uk/V3/api",[RelativePath="report?key=771"])),
BaseUrl = "https://api.keyedinprojects.co.uk/V3/api/report?key=771",
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetTotalEntities = (Page) =>
let Total = Page[TotalPages]
in Total,
GetUrl = (Index) =>
let Url = BaseUrl & "&pageNumber=" & Text.From(Index)
in Url, //used in urls
GetPage = (Index) =>
let Url = GetUrl(Index),
Json = GetJson(Url)
in Json, //used in pages
FirstPage = GetPage(1), //this is where we see our first json file
EntityCount = GetTotalEntities(FirstPage), //1 page only
PageIndices = { 1 .. EntityCount }, //gives a list {} returns a list
URLs = List.Transform(PageIndices, each GetUrl(_)),
Pages = List.Transform(PageIndices, each GetPage(_)[Data]),
Data = List.Union(Pages),
TblFromList = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedCols = Table.ExpandRecordColumn(TblFromList, "Column1", {"Project Code", "Project Name", "Project Owner", "Business Unit name", "Project Type", "PMO status", "FA number (General tab)", "Date Standstill Period Ends", "PMO closed down date", "PSC Action - Date MGT records set up (TA Relevant)", "ITN/RfP Submission Deadline", "PQQ Submission Deadline", "Closure", "Closure "}, {"Project Code", "Project Name", "Project Owner", "Business Unit name", "Project Type", "PMO status", "FA number (General tab)", "Date Standstill Period Ends", "PMO closed down date", "PSC Action - Date MGT records set up (TA Relevant)", "ITN/RfP Submission Deadline", "PQQ Submission Deadline", "Closure", "Closure "}),
//Replaced blank pmo dates with today
#"Changed Type" = Table.TransformColumnTypes(
ExpandedCols,
{
{"PSC Action - Date MGT records set up (TA Relevant)", type date},
{"ITN/RfP Submission Deadline", type date},
{"PMO status", type text},
{"Project Type", type text},
{"Business Unit name", type text},
{"Project Owner", type text},
{"Project Name", type text},
{"Project Code", type text},
{"FA number (General tab)", type text},
{"Date Standstill Period Ends", type date},
{"PMO closed down date", type date},
{"Closure", Int64.Type},
{"PQQ Submission Deadline", type date}
}
),
SS_StandStillNotNull = Table.SelectRows(
#"Changed Type",
each ([Date Standstill Period Ends] <> null)
),
NotCapProc = Table.SelectRows(
SS_StandStillNotNull,
each not Text.Contains([Project Type], "Capital Procurement Projects")
),
SS_AfterApr2021 = Table.SelectRows(NotCapProc, each [Date Standstill Period Ends] > #date(2021, 4, 1))
in
SS_AfterApr2021
//Replaced blank pmo dates with today
I used relative path as per my research but it still gives the error of dynamic source. The data is being pulled correctly but I can't seem to set a refresh.