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

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