API connection with headers renewing online? (Dyflexis)

jelleschuurman — Wed, 20 Mar 2024 18:04:03 GMT

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

Re: API connection with headers renewing online? (Dyflexis)

lbendlin — Wed, 20 Mar 2024 23:51:52 GMT

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

Re: API connection with headers renewing online? (Dyflexis)

jelleschuurman — Thu, 21 Mar 2024 07:59:55 GMT

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

Re: API connection with headers renewing online? (Dyflexis)

lbendlin — Thu, 21 Mar 2024 19:08:49 GMT

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

topic Re: API connection with headers renewing online? (Dyflexis) in Power Query

API connection with headers renewing online? (Dyflexis)

Re: API connection with headers renewing online? (Dyflexis)

Re: API connection with headers renewing online? (Dyflexis)

Re: API connection with headers renewing online? (Dyflexis)