Forum Discussion

ashmitp869's avatar
ashmitp869
Icon for Responsive Resident rankResponsive Resident
9 months ago
Solved

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

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 fi...
Need Help
  • ashmitp869's avatar
    ashmitp869
    9 months ago

    Hi PhilipTreacy 

    I found the correct one, I was missing {} 

     

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

     

    Thanks for all your guidance

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
9 months ago

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's avatar
    ashmitp869
    Icon for Responsive Resident rankResponsive Resident
    9 months ago

    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's avatar
      PhilipTreacy
      Icon for Super User rankSuper User
      9 months ago

      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's avatar
      PhilipTreacy
      Icon for Super User rankSuper User
      9 months ago

      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

       

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        9 months ago

        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