Assistance with Power Query – Missing ‘Closed’ Status in Data Extract

[ashmitp869]

Hi,
I need some assistance with Power Query. I’m using a script to retrieve all status data, but I’m not getting the expected results—for example, statuses marked as ‘Closed’ are missing. I can’t find any of these in the data extract.

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

    // Pagination settings
    pageSize = 100,
    baseUrl = "https://api.XXXX.com",
    relativePath = "v3/inf/records",

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

    // Generate pages dynamically until empty
    allPages = List.Generate(
        () => [offset = 0, data = GetPage(0)],
        each List.Count([data]) > 0,
        each [offset = [offset] + pageSize, data = GetPage([offset])],
        each [data]
    ),

    combined = List.Combine(allPages),
    table = Table.FromList(combined, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = table,
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "sourceID", "module", "moduleName", "form", "formName", "lastModified", "formVersion", "versionState", "archivedStep", "complete", "creationDate", "invitations", "displayText", "fieldsToValues", "fieldsToAdditionalValues", "fieldsToQuestionWithAnswers", "deleted"}, {"id", "sourceID", "module", "moduleName", "form", "formName", "lastModified", "formVersion", "versionState", "archivedStep", "complete", "creationDate", "invitations", "displayText", "fieldsToValues", "fieldsToAdditionalValues", "fieldsToQuestionWithAnswers", "deleted"}),
    #"Expanded fieldsToValues" = Table.ExpandRecordColumn(#"Expanded Column2", "fieldsToValues", {"Date Occurred", "docNumber", "status", "Motor Vehicle Incident Type"}, {"Date Occurred", "docNumber", "status", "Motor Vehicle Incident Type"})
in
    #"Expanded fieldsToValues"

The third party is using this API link and is getting the expected results.

[v-dineshya]

Hi @ashmitp869 ,

Thank you for reaching out to the Microsoft Community Forum.

You are not getting any “Closed” records because your Power Query request is not applying the same filter that the third-party API call is using. The third-party request shows below filter.

filter=fieldsToValues.status:Closed

But your Power Query script never includes this filter.

Please update your query parameters section with below code.

queryParams = [
sort = "creationDate",
asc = "false",
mobileOnly = "false",
filter = "moduleName:Incident Reporting",
filter1 = "form:2",
filter2 = "archivedStep:true",
filter3 = "deleted:false",
filter4 = "fieldsToValues.status:Closed",
rowStart = Text.From(offset),
rowLimit = Text.From(pageSize)
],

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[PhilipTreacy]

Thx @v-dineshya , I already pointed this out in my 1st reply.

Regards

Phil

[v-dineshya]

Hi @PhilipTreacy ,

Sorry , i din't observe your previous response. 

Regards,

Dinesh

[PhilipTreacy]

HI @ashmitp869 

Are you sure that is the correct query parameter convention?  Is that what the API documentation says?

It is unusual to have multiple parameters with the same name (filter).  Certainly if you are calling a PHP script then this is bad and won't work as only the last filter parameter value is saved.  But it will dependon what is receiving the request and how it is processed.

I would expect to use filter1, filter2 etc instead

If that really is the convention for this API try this

queryParams = [
        sort = "creationDate",
        asc = "false",
        mobileOnly = "false",
        filter = "moduleName:Incident Reporting&filter=form:2D&filter=archivedStep:true",
        rowStart = Text.From(offset),
        rowLimit = Text.From(pageSize)
    ]

Have you tried using postman to confirm the query format and the returned data?

Download Postman | Get Started for Free

Phil

[ashmitp869]

I have tried Insomnia

I am getting the result.

I have used 

etPage = (offset as number) =>
        let
            response = Json.Document(Web.Contents(
                "https://api.myosh.com", // Static base URL
                [
                    RelativePath = "v3/inf/records",
                    Query = [
                        rowLimit = "1000",
                        sort = "creationDate",
                        asc = "false",
                        mobileOnly = "false",
                       // filter = "moduleName:Incident Reporting",
                        filter ="moduleName:Incident Reporting&filter=form:2&archivedStep:true",
                        //filter2 = "form:2",
                        //filter3 = "archivedStep:true",
                       // filter4 = "deleted:false",
                        offset = Number.ToText(offset)
                    ],
                    Headers = [
                        #"x-api-key" = apiKey,
                        #"Authorization" = authHeader,
                        #"Accept" = "application/json"
                    ]
                ]
            )),
            results = try response[results] otherwise {}
        in
            results

, but not getting the result as expected. 

I have tried to use direct Url, it is extracting all data in PBI desktop.
When publish to PBI server - I am getting error

"This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. "

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

    // 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

[PhilipTreacy]

@ashmitp869 

Also, try this - note that syntax around the filter parameters

    baseUrl = "https://api.myosh.com/v3/inf/records",
    GetPage = (offset as number) =>
        let
            response = Json.Document(
                Web.Contents(
                    baseUrl,
                    [
                        RelativePath = "",
                        Query = [
                            rowLimit = "1000",
                            sort = "creationDate",
                            asc = "false",
                            mobileOnly = "false",
                            #"filter" = "moduleName:Incident Reporting",
                            #"filter" = "form:2",
                            #"filter" = "archivedStep:true",
                            #"filter" = "deleted:false",
                            offset = Number.ToText(offset)
                        ],
                        Headers = [
                            #"x-api-key" = apiKey,
                            #"Authorization" = authHeader,
                            #"Accept" = "application/json"
                        ]
                    ]
                )
            ),
            results = try response[results] otherwise {}
        in
            results

Phil

[ashmitp869 · Accepted solution]

Hi @PhilipTreacy 

I found the correct one, I was missing {} 

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

Thanks for all your guidance

[v-dineshya]

Hi @ashmitp869 ,

Could you please try the proposed solutions shared by  @PhilipTreacy ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[PhilipTreacy]

@ashmitp869 

PBI Service requires that the data source (your baseUrl) be static - that is, PBI Service doesn't like the query parameters in the URL.

Try this

    baseUrl = "https://api.myosh.com/v3/inf/records",
    GetPage = (offset as number) =>
        let
            response = Json.Document(
                Web.Contents(
                    baseUrl,
                    [
                        RelativePath = "",
                        Query = [
                                 rowLimit = "1000&sort=creationDate&asc=false&mobileOnly=false&filter=moduleName:Incident Reporting&filter=form:2&filter=archivedStep:true&filter=deleted:false",
                                 offset = Number.ToText(offset)
                        ],
                        Headers = [
                            #"x-api-key" = apiKey,
                            #"Authorization" = authHeader,
                            #"Accept" = "application/json"
                        ]
                    ]
                )
            ),
            results = try response[results] otherwise {}
        in
            results

Regards

Phil

[PhilipTreacy]

Hi @ashmitp869 

The 3rd party API call you've shown is using a URL parameter filter=fieldsToValues.status:Closed but your M code is not.  Do you need to include this in your query?

Have you tried calling GetPage in a separate query and confirming what data it returns?

Is it possible that in your query above, when you do the #"Expanded Column2" step that you aren't actually expanding the record with the Closed status data?

Regards

Phil

[GeraldGEmerick]

@ashmitp869 I don't see anything obvious in your code that would exclude that status. Is it possible that one of your filters is inadvertently excluding closed items?

                asc = "false",
                mobileOnly = "false",
                filter = "moduleName:Incident Reporting",
                filter1 = "form:2",
                filter2 = "archivedStep:true",
                filter3 = "deleted:false",                

[ashmitp869]

Hi,

Can you help me with this

the actual path is like 

actualPath = "https://api.XXX.com/v3/inf/records?rowLimit=100&sort=creationDate&asc=false&mobileOnly=false&filter=... Reporting&filter=form:2&filter=archivedStep:true"


How to apply the filter with & in this Power M query - 

    // Function to fetch a single page
    GetPage = (offset as number) =>
        let
            queryParams = [
                sort = "creationDate",
                asc = "false",
                mobileOnly = "false",
                filter = "moduleName:Incident Reporting",
                filter1 = "form:2",
                filter2 = "archivedStep:true",
                filter3 = "deleted:false",                
                rowStart = Text.From(offset),
                rowLimit = Text.From(pageSize)
            ],
            

 I am trying to use this but not working

queryParams = [
        sort = "creationDate",
        asc = "false",
        mobileOnly = "false",
        filter = "moduleName:Incident Reporting AND form:2 AND archivedStep:true",
        rowStart = Text.From(offset),
        rowLimit = Text.From(pageSize)
    ],

@PhilipTreacy @GeraldGEmerick  Please kindly assist.