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

GeraldGEmerick's avatar
GeraldGEmerick
Icon for Memorable Member rankMemorable Member
9 months ago

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

    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=moduleName:Incident 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.