46,248 questions
[Dynamic source] Can't set scheduling despite using relative path in M-Code
Abdul Wasay
1
Reputation point
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.
Community Center | Not monitored
Sign in to answer