Assistance with Power BI Power Query M Code

[ashmitp869]

I am using Power BI to connect to the Myosh API via the Web API option.
Currently, I select Type: Anonymous and Privacy Level: Organizational.

Using the base URL directly, I can successfully fetch data. However, I need to use the Relative Path for compatibility with the Power BI Service.

When I attempt to use the Relative Path, I am not getting the same results.

Script with direct base URL

let
    // Credentials
    apiKey = "xxxx",
    username = "xxxx",
    password = "xxxx",
    credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
    authHeader = "Basic " & credentials,

    // Base URL without offset
    baseUrl = "https://api.myosh.com/v3/inf/records?rowLimit=1000&sort=creationDate&asc=false&mobileOnly=false&filter=moduleName:Incident Reporting&filter=archivedStep:true&filter=form:2",

    // Function to get a page
    GetPage = (offset as number) =>
        let
            url = baseUrl & "&offset=" & Number.ToText(offset),
            response = Json.Document(Web.Contents(url,
                [Headers = [
                    #"x-api-key" = apiKey,
                    #"Authorization" = authHeader,
                    #"Accept" = "application/json"
                ]]
            )),
            results = try response[results] otherwise {}
        in
            results,

    // Generate offsets (0, 1000, 2000, … up to 10000 for example)
    offsets = List.Generate(() => 0, each _ < 10000, each _ + 1000),

    // Fetch all pages
    allPages = List.Combine(List.Transform(offsets, each GetPage(_))),

    // Convert to table
    table = Table.FromList(allPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    Expanded = Table.ExpandRecordColumn(table, "Column1", 
        {"id", "sourceID", "module", "moduleName", "form", "formName", "lastModified", "formVersion", "versionState", "archivedStep", "workflowStepHistory", "workflowStepHistories", "complete", "creationDate", "important", "importantDocNo", "linkUrl", "location", "persistedSinceLastEdit", "readAccessRoles", "records", "invitations", "displayText", "hasNormalReverseRecord", "fieldsToValues", "fieldsToAdditionalValues", "fieldsToQuestionWithAnswers", "deleted"}
    ),
    ExpandedFields = Table.ExpandRecordColumn(Expanded, "fieldsToValues", 
        {"Date Occurred", "Motor Vehicle Incident Type", "Classification (Outcomes)", "Actual Incident Category", "docNumber", "status", "Drivers Licence validation confirmed by issuing authority?"}
    ),
    #"Filtered Rows" = Table.SelectRows(ExpandedFields, each true)
in
    #"Filtered Rows"







​

Script with Relative Path

let
    // Parameters

    ModuleName = "Incident Reporting",
    ArchivedStep = "true",
    PageSize = 1000,
    PageCount = 35,

        // e.g., 35

    // Credentials
    apiKey = "xxxx",
    username = "xxxx",
    password = "xxxx",
    credentials = Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
    authHeader = "Basic " & credentials,

    // Base URL
    baseUrl = "https://api.myosh.com",
    relativePath = "v3/inf/records",

    // Pagination offsets
    offsets = List.Transform({0..(PageCount - 1)}, each _ * PageSize),

    // Function to fetch a single page
    GetPage = (offset as number) =>
        let
            response = Json.Document(Web.Contents(baseUrl, [
                RelativePath = relativePath,
                Query = [
                    sort = "creationDate",
                    asc = "false",
                    mobileOnly = "false",
                     filter = "=moduleName:Incident Reporting&filter=archivedStep:true&filter=form:2",
                    rowStart = Text.From(offset),
                    rowLimit = Text.From(PageSize)
                ],
                Headers = [
                    #"x-api-key" = apiKey,
                    #"Authorization" = authHeader,
                    #"Accept" = "application/json"
                ]
            ])),
            results = response[results]
        in
            results,

    // Combine all pages
    allPages = List.Combine(List.Transform(offsets, each GetPage(_))),
    table = Table.FromList(allPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    Expanded = Table.ExpandRecordColumn(table, "Column1", 
        {"id", "sourceID", "module", "moduleName", "form", "formName", "lastModified", "formVersion", "versionState", "archivedStep", "workflowStepHistory", "workflowStepHistories", "complete", "creationDate", "important", "importantDocNo", "linkUrl", "location", "persistedSinceLastEdit", "readAccessRoles", "records", "invitations", "displayText", "hasNormalReverseRecord", "fieldsToValues", "fieldsToAdditionalValues", "fieldsToQuestionWithAnswers", "deleted"}
    ),
    ExpandedFields = Table.ExpandRecordColumn(Expanded, "fieldsToValues", 
        {"Date Occurred", "Motor Vehicle Incident Type", "Classification (Outcomes)", "Actual Incident Category", "docNumber", "status", "Drivers Licence validation confirmed by issuing authority?"}
    ),
    #"Changed Type" = Table.TransformColumnTypes(ExpandedFields,{{"Date Occurred", type date}})
in
    #"Changed Type"
​

[PhilipTreacy]

@ashmitp869 

What results are you getting when using RelativePath?

I notice that in the 2nd query above you have this query parameter

filter = "=moduleName:Incident Reporting&filter=archivedStep:true&filter=form:2"

but shouldn't that be this (without the leading = in the parameter value)

filter = "moduleName:Incident Reporting&filter=archivedStep:true&filter=form:2"

Regards

Phil

[ashmitp869 · Accepted solution]

Hi @PhilipTreacy 

I found the correct one, I was missing {} 

filter = {"moduleName:Incident Reporting", "archivedStep:true", "form:2"}