Forum Discussion
API connection with headers renewing online? (Dyflexis)
Hi,
I'm creating a report based on Dyflexis. to get data I have to connect to an API. The API requires this headers:
Content-Type: application/json Accept: application/json Authorization: Token YOUR_AUTH_TOKEN
In desktop it works fine and I can refresh, but I can't refresh the data online. Is there a way to rewrite this M code, so that online refreshing is possible? This is the M-code for the Scheduled Shifts: (API description: https://developer.dyflexis.com/v3/#tag/Business-Intelligence/operation/Scheduled%20shifts )
let
BaseUrl = "https://app.planning.nu/xxxxx/api/business/v3/scheduled?",
Token = "xxxxx",
GetJson = (Url) =>
let
Options = [
Headers = [
Accept = "application/json",
#"Content-Type" = "application/json",
#"Authorization" = "Token " & Token
]
],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in
Json,
GetShifts = (PageNumber) =>
let
Url = BaseUrl & "page=" & Text.From(PageNumber),
Json = GetJson(Url),
Shifts = Json[shifts]
in
Shifts,
PageCount = try GetJson(BaseUrl & "$count=true&$top=0")[pageCount] otherwise 1,
Pages = List.Transform({1..PageCount}, each GetShifts(_)),
Table = Table.FromList(List.Combine(Pages), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "employeeId", "personnelNumber", "firstName", "infix", "lastName", "officeId", "officeName", "departmentId", "departmentName", "costCenterId", "costCenterName", "costCenterCode", "startDateTime", "endDateTime", "breakMinutes", "duration", "remark", "marking"}, {"id", "employeeId", "personnelNumber", "firstName", "infix", "lastName", "officeId", "officeName", "departmentId", "departmentName", "costCenterId", "costCenterName", "costCenterCode", "startDateTime", "endDateTime", "breakMinutes", "duration", "remark", "marking"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"startDateTime", type datetime}, {"endDateTime", type datetime}, {"id", Int64.Type}, {"employeeId", Int64.Type}, {"personnelNumber", Int64.Type}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"startDateTime", type date}})
in
#"Changed Type1"
Or is there another way to do this?
Thanks in advance.
Jelle
3 Replies
- lbendlinSuper User
No need for the question mark if you don't have Query parameters.
Not clear what is behind the xxxxxx but if that is variable then you need to use RelativePath.
Web.Contents - PowerQuery M | Microsoft Learn
- jelleschuurmanHelper I
The xxxxx is our organisationname, static.
When I remove the questionmark, I get this error:
DataSource.Error: Web.Contents failed to get contents from 'https://app.planning.nu/xxxxx/api/business/v3/scheduled$count=true&$top=0' (404): Not Found Details: DataSourceKind=Web DataSourcePath=https://app.planning.nu/xxxxx/api/business/v3/scheduled$count=true&$top=0 Url=https://app.planning.nu/xxxxx/api/business/v3/scheduled$count=true&$top=0- lbendlinSuper User
Show the Power Query code. Make sure to use both RelativePath and Query.