Power Query Examples

These Power Query examples are maintained customer reference snippets for Power BI Desktop — useful when you need a specific query shape beyond the starter template, or a chunk-loaded query for a large date range.

Use the starter template described in Power BI for normal setup.

Parameters

The snippets expect these Power BI parameters unless a specific example says otherwise:

Parameter	Description
`ApiUrl`	Ditio API base URL (e.g. `https://core-api.ditio.dev/core` for test)
`AuthToken`	Bearer token used to call the API
`FromDate`	Start date, usually ISO formatted
`ToDate`	End date, usually ISO formatted
`ProjectCompanyId`	Optional project company filter used by the project transactions example

Absence registrations

let
  #"List of columns" = {"AbsenceTypeName", "AbsenceTypeCode", "ProjName", "FirstName", "LastName", "Payroll", "EmployeeNumber", "CanEdit", "ApprovedByName", "CreatedByName", "Qty", "AbsenceRegistrationTrans", "AbsenceTypeId", "StartDateTime", "StartDate", "EndDate", "Text", "ProjId", "Approved", "ApprovedBy", "ApprovedDateTime", "DaysQty", "HourQty", "PercentGrade", "CreatedDateTime", "CreatedBy", "ModifiedBy", "ModifiedDateTime", "Locked", "LockedDateTime", "LockedBy", "UserId", "IdentityId", "CompanyProfileId", "EmploymentId", "UserIdLegacy", "ModifiedAt", "ModifiedByUserId", "CreatedAt", "CreatedByUserId", "Id", "CompanyId", "RecVersion", "CompanyIds"},
  #"Api url" = ApiUrl & "/api/absence/period?fromDateTime="&FromDate&"&toDateTime="&ToDate,
  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",
  [
    Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
  ])),

  #"Data as table" = if List.IsEmpty(#"Raw data from api")
    then
      #table(#"List of columns",{})
    else
      Table.ExpandRecordColumn(Table.FromList(#"Raw data from api", Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", #"List of columns"),

    #"Changed Type" = Table.TransformColumnTypes(#"Data as table",{{"AbsenceTypeCode", type text}, {"Payroll", type logical}, {"EmployeeNumber", type text}, {"CanEdit", type logical}, {"Qty", type number}, {"StartDateTime", type datetime}, {"StartDate", type datetimezone}, {"EndDate", type datetimezone}, {"ApprovedDateTime", type datetime}, {"DaysQty", Int64.Type}, {"HourQty", type number}, {"PercentGrade", Int64.Type}, {"CreatedDateTime", type datetime}, {"ModifiedDateTime", type datetime}, {"LockedDateTime", type datetime}, {"ModifiedAt", type datetime}, {"CreatedAt", type datetime}, {"CompanyId", type text}})
in
    #"Changed Type"

Machine

let
  #"List of columns" = {"Id", "ResourceNameFull", "CostPriceMachine", "CostPrice", "AnnualInspectedDate", "InsuranceExpiryDate", "BuildYear", "EmissionClass", "SerialNumber", "EngineNumber", "RegistrationNumber", "CompanyName", "ResponsibleName", "ResourceExternalRefs"},
  #"Api url" = ApiUrl & "/api/machine",
  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",
  [
    Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
  ])),

  #"Data as table" = if List.IsEmpty(#"Raw data from api")
    then
      #table(#"List of columns",{})
    else
      Table.ExpandRecordColumn(Table.FromList(#"Raw data from api", Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", #"List of columns"),

    #"Changed Type" = Table.TransformColumnTypes(#"Data as table",{{"CostPriceMachine", type number }, {"CostPrice", type number}, {"AnnualInspectedDate", type datetime}, {"InsuranceExpiryDate", type datetime}})
in
    #"Changed Type"

Machine project plans

let
  #"List of columns" = {"id", "projectId", "projectName", "taskId", "taskName", "taskNumber", "isChapter", "machineId", "machineName", "machineNumber", "isMachineOnLoan", "date", "comment", "modifiedAt", "modifiedByUserId", "createdAt", "createdByUserId"},
  #"Api url" = ApiUrl & "/api/machine-project-plan/all?fromDate="&FromDate&"&toDate="&ToDate,
  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",
  [
    Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
  ])),

  #"Data as table" = if List.IsEmpty(#"Raw data from api")
    then
      #table(#"List of columns",{})
    else
      Table.ExpandRecordColumn(Table.FromList(#"Raw data from api", Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", #"List of columns"),

    #"Changed Type" = Table.TransformColumnTypes(#"Data as table",{{"isChapter", type logical}, {"isMachineOnLoan", type logical}, {"date", type datetime}, {"modifiedAt", type datetime}, {"createdAt", type datetime}})
in
    #"Changed Type"

Notifications

let
  #"List of columns" = {"CompanyName", "InternalType", "Number", "Revision", "InternalTypeName", "ExternalTypeName", "ConsequenceTypeName", "UserName", "CreatedDateTime", "Title", "Description", "Specification", "InvolvedCompanies", "InvolvedResources", "RiskSeverityText", "ActionOnSite", "NeedsFollowUp", "InternalComment", "Location", "GeoCoordinate", "Response", "Sendt", "SendtDateTime", "CustomerRef", "SendtTo", "StatusText", "ProjectName"},
  #"Api url" = ApiUrl & "/api/notifications/allcompanynotifications/external-report",
  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",
  [
    Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
  ])),

  #"Data as table" = if List.IsEmpty(#"Raw data from api")
    then
      #table(#"List of columns",{})
    else
      Table.ExpandRecordColumn(Table.FromList(#"Raw data from api", Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", #"List of columns"),
    #"Changed Type" = Table.TransformColumnTypes(#"Data as table",{{"Number", type text}, {"Revision", type text}, {"Sendt", type logical}, {"SendtDateTime", type datetime}, {"CreatedDateTime", type datetime}})
in
    #"Changed Type"

Payroll export

Use the Payroll Export API for the supported payroll export contract. No payroll Power Query snippet is included here until a chunk-loaded version has been validated in Power BI Desktop.

Project transactions (chunk loaded)

For large date ranges, this query loads project transactions in two-month chunks to avoid timeouts:

let
  startDate = DateTimeZone.FromText(FromDate),
  endDate = DateTimeZone.FromText(ToDate),
  projectCompanyId = if ProjectCompanyId <> null then ProjectCompanyId else "",

  GetChunk = (ChunkFrom as datetimezone, ChunkTo as datetimezone) =>

    let body = "{
        ""FromDate"":""" & DateTimeZone.ToText(ChunkFrom, "yyyy-MM-ddTHH:mm:ssZ") & """,
        ""ToDate"":""" & DateTimeZone.ToText(ChunkTo, "yyyy-MM-ddTHH:mm:ssZ") & """,
        ""ProjectCompanyId"":""" & projectCompanyId  & """
        }",

      query_source = Web.Contents(ApiUrl & "/api/reporting/external-report",[
        Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&""],
        Content=Text.ToBinary(body)
      ]),
      query_text = Text.FromBinary(query_source),
      query_JSON = Json.Document(query_text)
    in
      query_JSON,

  GetChunks = (chunkStartDate as datetimezone) =>
    let chunkEndDate = Date.AddMonths(chunkStartDate, 2),
    actualStartDateTime =  if chunkStartDate = startDate then chunkStartDate else chunkStartDate + #duration(0,0,0,1),
    actualEndDateTime =  if (chunkEndDate > endDate) then endDate else chunkEndDate
    in
      GetChunk(actualStartDateTime, actualEndDateTime),

  ExpandResult = (result as list) =>
    if List.Count(result) < 1
      then
        #table({"StartDateTime"},{{endDate}})
      else
        let
          table_converted = Table.FromList(result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
          expanded = Table.ExpandRecordColumn(table_converted, "Column1", {"Amount", "Approved", "ApprovedByName", "ApprovedDateTime", "BreakQty", "BreakUnplanned", "ChapterId", "CompanyName", "CompanyOrgNr", "CostAmount", "CostPriceBase", "Description", "DescriptionInternal", "DinnerSkipped", "DinnerSubtracted", "EmployeeNumber", "Equipments", "ExternalId", "Id", "Locked", "LockedByName", "LockedDateTime", "LunchSkipped", "LunchSubtracted", "Price", "PrimaryResourceName", "ProjectCompanyName", "ProjectName", "ProjectNumber", "ProjectLeaderName", "ProjectMainForemanName", "ProjectCompanyId", "Qty", "ResourceName", "ResourceNumber", "ResourceTypeName", "StartDateTime", "StopDateTime", "TaskName", "TaskNameLong", "UnitName", "UnitPrice", "UnitQty", "UserBirthDate", "UserName", "WorkShiftSettingName", "ExternalProjectNumber"}, {"Amount", "Approved", "ApprovedByName", "ApprovedDateTime", "BreakQty", "BreakUnplanned", "ChapterId", "CompanyName", "CompanyOrgNr", "CostAmount", "CostPriceBase", "Description", "DescriptionInternal", "DinnerSkipped", "DinnerSubtracted", "EmployeeNumber", "Equipments", "ExternalId", "Id", "Locked", "LockedByName", "LockedDateTime", "LunchSkipped", "LunchSubtracted", "Price", "PrimaryResourceName", "ProjectCompanyName", "ProjectName", "ProjectNumber", "ProjectLeaderName", "ProjectMainForemanName", "ProjectCompanyId", "Qty", "ResourceName", "ResourceNumber", "ResourceTypeName", "StartDateTime", "StopDateTime", "TaskName", "TaskNameLong", "UnitName", "UnitPrice", "UnitQty", "UserBirthDate", "UserName", "WorkShiftSettingName", "ExternalProjectNumber"})
        in
          Table.TransformColumnTypes(expanded,{{"StartDateTime", type datetimezone}}),

  #"Chunks" = List.Generate( () => GetChunks(startDate), (result) => List.Count(result) > 1, (result) => GetChunks(List.Max(ExpandResult(result)[StartDateTime])), (result) => ExpandResult(result) ),
  result = Table.Combine( #"Chunks"),
  #"Transformed result" = Table.TransformColumnTypes(result, {{"BreakQty", type number}, {"Amount", type number}, {"BreakUnplanned", type number}, {"CostAmount", type number}, {"CostPriceBase", type number}, {"Price", type number}, {"ProjectNumber", type text}, {"ProjectLeaderName", type text}, {"ProjectMainForemanName", type text}, {"Qty", type number}, {"ChapterId", type text}, {"CompanyName", type text}, {"CompanyOrgNr", type text}, {"Description", type text}, {"DescriptionInternal", type text}, {"DinnerSkipped", type logical}, {"Approved", type logical}, {"ApprovedByName", type text}, {"ApprovedDateTime", type datetimezone}, {"DinnerSubtracted", type logical}, {"EmployeeNumber", type text}, {"ExternalId", type text}, {"Id", type text}, {"Locked", type logical}, {"LockedByName", type text}, {"LockedDateTime", type datetime}, {"LunchSkipped", type logical}, {"LunchSubtracted", type logical}, {"PrimaryResourceName", type text}, {"ProjectCompanyName", type text}, {"ProjectName", type text}, {"ResourceName", type text}, {"ResourceNumber", type text}, {"ResourceTypeName", type text}, {"StartDateTime", type datetimezone}, {"StopDateTime", type datetimezone}, {"TaskName", type text}, {"TaskNameLong", type text}, {"UnitName", type text}, {"UnitPrice", type number}, {"UnitQty", type number}, {"UserName", type text}, {"WorkShiftSettingName", type text}})
in
  #"Transformed result"

User project plans

let
  #"List of columns" = {"id", "projectId", "projectName", "taskId", "taskName", "taskNumber", "isChapter", "userId", "Roles", "isUserOnLoan", "date", "comment", "modifiedAt", "modifiedByUserId", "createdAt", "createdByUserId"},
  #"Api url" = ApiUrl & "/api/project-plan/all?fromDate="&FromDate&"&toDate="&ToDate,
  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",
  [
    Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
  ])),

  #"Data as table" = if List.IsEmpty(#"Raw data from api")
    then
      #table(#"List of columns",{})
    else
      Table.ExpandRecordColumn(Table.FromList(#"Raw data from api", Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", #"List of columns"),

    #"Changed Type" = Table.TransformColumnTypes(#"Data as table",{{"isChapter", type logical}, {"isUserOnLoan", type logical}, {"date", type datetime}, {"modifiedAt", type datetime}, {"createdAt", type datetime}})
in
    #"Changed Type"

User shift plans

let
  #"List of columns" = {"id", "userId", "externalId", "departmentCode", "shiftCode", "shiftDescription", "shiftRemark", "workDescription", "colorCode", "createdAt", "workDate", "workHours", "date", "fromDateTime", "toDateTime", "isDayShift", "isEveningShift", "isNightShift", "isContingencyShift", "isAbsence", "isPartialAbsence"},
  #"Api url" = ApiUrl & "/api/shift-plan/all?fromDate="&FromDate&"&toDate="&ToDate,
  #"Raw data from api"  = Json.Document(Web.Contents(#"Api url",
  [
    Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
  ])),

  #"Data as table" = if List.IsEmpty(#"Raw data from api")
    then
      #table(#"List of columns",{})
    else
      Table.ExpandRecordColumn(Table.FromList(#"Raw data from api", Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", #"List of columns"),

    #"Changed Type" = Table.TransformColumnTypes(#"Data as table",{{"externalId", type text}, {"departmentCode", type text}, {"userId", type text}, {"isContingencyShift", type logical}, {"isAbsence", type logical}, {"isPartialAbsence", type logical}, {"workHours", type number}, {"fromDateTime", type datetime}, {"toDateTime", type datetimezone}})
in
    #"Changed Type"

A chunk-loaded variant of the shift-plan query (loading one day at a time for very large ranges) is available on request from support@ditio.no.

Users

let
    url = ApiUrl & "/api/userlegacy",
   Source  = Json.Document(Web.Contents(url,
   [
     Headers = [#"Content-Type"="application/json",#"Authorization"="Bearer "&AuthToken&"" ]
   ]
   )
   ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"CompanyObjectId", "CompanyMainObjectId", "CompanyMainName", "CompanyName", "UserGroupMainName", "UserGroupName", "PrimaryResourceName", "CardStatus", "Editable", "ActiveInContextCompany", "UserCompanyRoles", "Tags", "ProjectRoles", "CompanyIdMain", "IdentityId", "UserId", "EmployeeNumber", "CardId", "CardExpirationDate", "UserGroupIdMain", "UserGroupId", "PrimaryResourceId", "Email", "Name", "FirstName", "LastName", "CountryCode", "MobilePhone", "EmergencyContact", "EmergencyInfo", "Address", "Active", "BirthDate", "Initials", "Department", "CarRegNumber", "CarMakeType", "AvatarImageId", "LanguageId", "TimeZoneId", "Payroll", "ModifiedBy", "ModifiedDateTime", "Id", "CompanyId", "CompanyIds"}, {"CompanyObjectId", "CompanyMainObjectId", "CompanyMainName", "CompanyName", "UserGroupMainName", "UserGroupName", "PrimaryResourceName", "CardStatus", "Editable", "ActiveInContextCompany", "UserCompanyRoles", "Tags", "ProjectRoles", "CompanyIdMain", "IdentityId", "UserId", "EmployeeNumber", "CardId", "CardExpirationDate", "UserGroupIdMain", "UserGroupId", "PrimaryResourceId", "Email", "Name", "FirstName", "LastName", "CountryCode", "MobilePhone", "EmergencyContact", "EmergencyInfo", "Address", "Active", "BirthDate", "Initials", "Department", "CarRegNumber", "CarMakeType", "AvatarImageId", "LanguageId", "TimeZoneId", "Payroll", "ModifiedBy", "ModifiedDateTime", "Id", "CompanyId", "CompanyIds"})
in
  #"Expanded Column1"

Power BI — the starter template these snippets extend
Authentication — obtaining the AuthToken
Export Time Data — API-first exports for data warehouses