Skip to content
Ditio Developer Docs

Power Query Examples

These Power Query (M) snippets are the queries behind the Power BI template. Use them to add Ditio data to an existing report, or as a starting point for your own queries. They read the Data Extraction API page by page, with a token from your API client.

Every query of the template is also available as a separate file in the samples repository. To add one, create a Blank Query in Power Query, open the Advanced Editor, paste the code and name the query after the file. The template’s tables and fields have Norwegian names; the field glossary maps them to API fields.

Parameters

Section titled “Parameters”

Create the parameters described in Power BI → Set up as Power BI parameters (Manage Parameters → New Parameter). All are of type Text except RangeStart and RangeEnd, which are Date/Time: they are Power BI’s incremental-refresh parameters, so the same queries work with a refresh policy. The credential caution for the template applies to your report too.

Shared functions

Section titled “Shared functions”

The data queries call these functions. Add each as its own query, with the name shown.

DitioGetAccessToken

Section titled “DitioGetAccessToken”
() as text =>
let
    UseProvidedToken = AccessToken <> null and AccessToken <> "",
    Token =
        if UseProvidedToken then
            AccessToken
        else if ClientId = null or ClientId = "" or ClientSecret = null or ClientSecret = "" then
            error Error.Record("Ditio-pålogging mangler", "Fyll ut parameterne ClientId og ClientSecret, eller AccessToken.")
        else
            let
                Response = Web.Contents(
                    IdentityUrl,
                    [
                        RelativePath = "connect/token",
                        Headers = [#"Content-Type" = "application/x-www-form-urlencoded", Accept = "application/json"],
                        Content = Text.ToBinary(
                            Uri.BuildQueryString(
                                [
                                    grant_type = "client_credentials",
                                    client_id = ClientId,
                                    client_secret = ClientSecret,
                                    scope = "reportingapiv1"
                                ]
                            )
                        ),
                        ManualStatusHandling = {400, 401, 403}
                    ]
                ),
                Body = Binary.Buffer(Response),
                Status = Value.Metadata(Response)[Response.Status]
            in
                if Status >= 400 then
                    error Error.Record("Ditio-pålogging feilet", "HTTP " & Text.From(Status) & " fra " & IdentityUrl, Text.FromBinary(Body))
                else
                    Json.Document(Body)[access_token]
in
    Token

DitioExtract

Section titled “DitioExtract”

Calls a Data Extraction endpoint and follows continuationToken until every page is loaded.

(path as text, optional filters as nullable record) as list =>
let
    Token = DitioGetAccessToken(),
    CompanyFilter = if CompanyId = null or CompanyId = "" then [] else Record.FromList({CompanyId}, {"CompanyId"}),
    BaseQuery = (if filters = null then [] else filters) & CompanyFilter & [ChunkLimit = "5000"],
    GetPage = (continuationToken as nullable text) as record =>
        let
            PageQuery = if continuationToken = null then BaseQuery else BaseQuery & [ContinuationToken = continuationToken],
            Response = Web.Contents(
                ReportingApiUrl,
                [
                    RelativePath = path,
                    Query = PageQuery,
                    Headers = [Authorization = "Bearer " & Token, Accept = "application/json"],
                    Timeout = #duration(0, 0, 10, 0),
                    ManualStatusHandling = {400, 401, 403, 404, 500, 502, 503}
                ]
            ),
            Body = Binary.Buffer(Response),
            Status = Value.Metadata(Response)[Response.Status]
        in
            if Status >= 400 then
                error Error.Record("Ditio API-kall feilet", "HTTP " & Text.From(Status) & " fra " & path, Text.FromBinary(Body))
            else
                Json.Document(Body),
    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each
            let
                Next = Record.FieldOrDefault(_, "continuationToken", null)
            in
                if Next = null or Next = "" then null else GetPage(Next),
        each Record.FieldOrDefault(_, "data", {})
    ),
    Records = List.Buffer(List.Combine(List.Transform(Pages, each if _ = null then {} else _)))
in
    Records

DitioDateTimeFromText

Section titled “DitioDateTimeFromText”
(value as nullable text) as nullable datetime =>
    if value = null or value = "" or Text.StartsWith(value, "0001-01-01") then
        null
    else
        DateTime.FromText(Text.Start(value, 19), "en-US")

DitioDateWindow

Section titled “DitioDateWindow”

Whole days from RangeStart to RangeEnd. The API’s ToDateTime is inclusive, so after extracting, keep only rows whose date is >= RangeStart and < RangeEnd (as the item transactions query below does); otherwise neighbouring incremental-refresh partitions overlap.

() as record =>
[
    // Whole days. The API's ToDateTime is inclusive, so a row exactly at RangeEnd is fetched by two
    // neighbouring partitions; the [RangeStart, RangeEnd) clip in each table keeps it in one.
    FromDateTime = Date.ToText(Date.From(RangeStart), "yyyy-MM-dd") & "T00:00:00",
    ToDateTime = Date.ToText(Date.From(RangeEnd), "yyyy-MM-dd") & "T00:00:00"
]

DitioModifiedWindow

Section titled “DitioModifiedWindow”

Used for alerts and checklists, whose endpoints filter on ModifiedSince rather than FromDateTime/ToDateTime. Anything created in the period has been modified since RangeStart.

() as record =>
[
    ModifiedSince = Date.ToText(Date.From(RangeStart), "yyyy-MM-dd") & "T00:00:00"
]

Example: projects

Section titled “Example: projects”

Every table follows the same pattern: extract the records, pick the columns, parse dates and set types. Swap the endpoint and columns for any other Data Extraction endpoint; pass DitioDateWindow() instead of null for registrations filtered by date.

let
    Records = DitioExtract("v1/project", null),
    Columns = {"id", "number", "name", "externalNumber", "externalDim01", "externalDim02", "companyId", "companyName", "active", "isExternal", "createdDateTime", "modifiedDateTime", "isDeleted"},
    Raw =
        if List.IsEmpty(Records) then
            #table(Columns, {})
        else
            Table.ExpandRecordColumn(
                Table.FromList(Records, Splitter.SplitByNothing(), {"Record"}, null, ExtraValues.Error),
                "Record",
                Columns
            ),
    Ordered = Table.ReorderColumns(Raw, {"id", "number", "name", "externalNumber", "externalDim01", "externalDim02", "companyId", "companyName", "active", "isExternal", "createdDateTime", "modifiedDateTime", "isDeleted"}),
    Dates = Table.TransformColumns(Ordered, {
        {"createdDateTime", DitioDateTimeFromText, type datetime},
        {"modifiedDateTime", DitioDateTimeFromText, type datetime}
    }),
    Typed = Table.TransformColumnTypes(Dates, {
        {"id", type text},
        {"number", type text},
        {"name", type text},
        {"externalNumber", type text},
        {"externalDim01", type text},
        {"externalDim02", type text},
        {"companyId", type text},
        {"companyName", type text},
        {"active", type logical},
        {"isExternal", type logical},
        {"isDeleted", type logical}
    }, "en-US"),
    WithoutDeleted = Table.SelectRows(Typed, each [isDeleted] <> true),
    // Keys must be unique for the model relationships.
    Result = Table.Distinct(WithoutDeleted, {"id"})
in
    Result

The template’s other queries — work orders, resources, users, hours, machine registrations, absence, alerts, checklists and mass transport — are in the samples repository.

Item transactions

Section titled “Item transactions”

Item transactions come from a web query that returns semicolon-separated text rather than JSON. The header row is localized and does not line up with the data columns, so the query skips it and names the columns by position.

let
    Token = DitioGetAccessToken(),
    // The endpoint takes whole days, both included: RangeStart's day up to the day before RangeEnd.
    FromDateText = Date.ToText(Date.From(RangeStart), "yyyy-MM-dd"),
    ToDateText = Date.ToText(Date.AddDays(Date.From(RangeEnd), -1), "yyyy-MM-dd"),
    Response = Web.Contents(
        ReportingApiUrl,
        [
            RelativePath = "v1/item-registrations/web-query",
            Query = [FromDate = FromDateText, ToDate = ToDateText],
            Headers = [Authorization = "Bearer " & Token],
            Timeout = #duration(0, 0, 10, 0),
            ManualStatusHandling = {400, 401, 403, 404, 500, 502, 503}
        ]
    ),
    Body = Binary.Buffer(Response),
    Status = Value.Metadata(Response)[Response.Status],
    Checked =
        if Status >= 400 then
            error Error.Record("Ditio API-kall feilet", "HTTP " & Text.From(Status) & " fra v1/item-registrations/web-query", Text.FromBinary(Body))
        else
            Body,
    // Semicolon-separated, one header row (localised, ignored) then one line per transaction.
    Csv = Csv.Document(Checked, [Delimiter = ";", Columns = 16, Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    WithoutHeader = Table.Skip(Csv, 1),
    Named = Table.RenameColumns(
        WithoutHeader,
        List.Zip({Table.ColumnNames(WithoutHeader), {"itemName", "projectName", "projectNumber", "taskName", "transType", "userName", "transDate", "machineName", "qty", "costPrice", "price", "costAmount", "amount", "warehouseName", "warehouseQty", "description"}})
    ),
    // Numbers use the server's culture: accept "1,5" and "1.5", and the Unicode minus sign.
    Numbers = Table.TransformColumns(
        Named,
        List.Transform(
            {"qty", "costPrice", "price", "costAmount", "amount", "warehouseQty"},
            (column) => {column, each if _ = null or _ = "" then null else Number.FromText(Text.Replace(Text.Replace(_, ",", "."), "−", "-"), "en-US"), type number}
        )
    ),
    TransDate = Table.TransformColumns(
        Numbers,
        {"transDate", each if _ = null or _ = "" then null else DateTime.FromText(_, [Format = "dd.MM.yyyy HH:mm:ss", Culture = "en-US"]), type datetime}
    ),
    Typed = Table.TransformColumnTypes(TransDate, {
        {"itemName", type text},
        {"projectName", type text},
        {"projectNumber", type text},
        {"taskName", type text},
        {"transType", type text},
        {"userName", type text},
        {"machineName", type text},
        {"warehouseName", type text},
        {"description", type text}
    }),
    Mapped1 = Table.AddColumn(Typed, "transTypeName", each
        if [transType] = null then null
        else Record.FieldOrDefault([Consumption = "Forbruk", Sale = "Salg", Purchase = "Innkjøp", Adjustment = "Justering"], [transType], [transType]),
        type text),
    WithDateKey = Table.AddColumn(Mapped1, "dateKey", each Date.From([transDate]), type date),
    // Keep [RangeStart, RangeEnd) only: incremental-refresh partitions must not overlap.
    InPeriod = Table.SelectRows(WithDateKey, each [dateKey] <> null and [dateKey] >= Date.From(RangeStart) and [dateKey] < Date.From(RangeEnd)),
    Result = InPeriod
in
    Result

Payroll export

Section titled “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.

Section titled “Related”