API connection with headers renewing online? (Dyflexis)

[jelleschuurman]

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

[lbendlin]

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

[jelleschuurman]

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

[lbendlin]

Show the Power Query code.  Make sure to use both RelativePath and Query.