Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities - Lets Get Started!
Announcing new navigation menus and the Engage with the Community forum!
News and Announcements icon
Community site session details
Power Platform Community / Forums / Power Automate - AI Builder / Power Automate error u...
Power Automate - AI Builder
Answered

Power Automate error using Run a query against a dataset

Like (1) ShareShare
ReportReport
Posted on 26 Jul 2024 10:51:06 by AW-26071034-0

Hi there

 

Please can someone let me know why I am getting an error with this step in my flow:

 

{ "error": { "code": "DatasetExecuteQueriesError", "pbi.error": { "code": "DatasetExecuteQueriesError", "parameters": {}, "details": [ { "code": "DetailsMessage", "detail": { "type": 1, "value": "Query (28, 3) The syntax for ')' is incorrect. (// DAX Query\nDEFINE\nVAR _TodaysDate = TODAY()\n\n\tVAR __DS0FilterTable = \n\t\tTREATAS({\"andy.test@abc.com\"}, 'SDT Clarity Tasks'[Assignee])\n\n\tVAR __DS0FilterTable2 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('SDT Clarity Tasks'[Status])),\n\t\t\tAND(\n\t\t\t\tNOT(SEARCH(\"Closed\", 'SDT Clarity Tasks'[Status], 1, 0) >= 1),\n\t\t\t\tNOT(SEARCH(\"Completed\", 'SDT Clarity Tasks'[Status], 1, 0) >= 1)\n\t\t\t)\n\t\t)\n\n\tVAR __DS0FilterTable3 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('SDT Clarity Tasks'[Phase])),\n\t\t\tNOT(SEARCH(\"Delivered\", 'SDT Clarity Tasks'[Phase], 1, 0) >= 1)\n\t\t)\n\n\tVAR __DS0FilterTable4 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('SDT Clarity Tasks'[ETD])),\n\t\t\t\t\t\t'SDT Clarity Tasks'[ETD] = _TodaysDate\n\t\t\t)\n\t\t)\n\n\tVAR __DS0Core = \n\t\tSELECTCOLUMNS(\n\t\t\tKEEPFILTERS(\n\t\t\t\tFILTER(\n\t\t\t\t\tKEEPFILTERS(\n\t\t\t\t\t\tSUMMARIZECOLUMNS(\n\t\t\t\t\t\t\t'SDT Clarity Tasks'[Assignee],\n\t\t\t\t\t\t\t'SDT Clarity Tasks'[ID],\n\t\t\t\t\t\t\t'SDT Clarity Tasks'[Customer],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day],\n\t\t\t\t\t\t\t__DS0FilterTable,\n\t\t\t\t\t\t\t__DS0FilterTable2,\n\t\t\t\t\t\t\t__DS0FilterTable3,\n\t\t\t\t\t\t\t__DS0FilterTable4,\n\t\t\t\t\t\t\t\"CountRowsSDT_Clarity_Tasks\", COUNTROWS('SDT Clarity Tasks')\n\t\t\t\t\t\t)\n\t\t\t\t\t),\n\t\t\t\t\tOR(\n\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\t\t\t\tNOT(ISBLANK('SDT Clarity Tasks'[Assignee])),\n\t\t\t\t\t\t\t\t\t\t\tNOT(ISBLANK('SDT Clarity Tasks'[ID]))\n\t\t\t\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\t\t\t\tNOT(ISBLANK('SDT Clarity Tasks'[Customer]))\n\t\t\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year]))\n\t\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month]))\n\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo]))\n\t\t\t\t\t\t),\n\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]))\n\t\t\t\t\t)\n\t\t\t\t)\n\t\t\t),\n\t\t\t\"'SDT Clarity Tasks'[Assignee]\", 'SDT Clarity Tasks'[Assignee],\n\t\t\t\"'SDT Clarity Tasks'[ID]\", 'SDT Clarity Tasks'[ID],\n\t\t\t\"'SDT Clarity Tasks'[Customer]\", 'SDT Clarity Tasks'[Customer],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]\n\t\t)\n\n\tVAR __DS0PrimaryWindowed = \n\t\tTOPN(\n\t\t\t501,\n\t\t\t__DS0Core,\n\t\t\t'SDT Clarity Tasks'[Assignee],\n\t\t\t1,\n\t\t\t'SDT Clarity Tasks'[ID],\n\t\t\t1,\n\t\t\t'SDT Clarity Tasks'[Customer],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day],\n\t\t\t1\n\t\t)\n\nEVALUATE\n\t__DS0PrimaryWindowed\n\nORDER BY\n\t'SDT Clarity Tasks'[Assignee],\n\t'SDT Clarity Tasks'[ID],\n\t'SDT Clarity Tasks'[Customer],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]\n)." } }, { "code": "AnalysisServicesErrorCode", "detail": { "type": 1, "value": "3238920194" } } ] } } }

 

The query is this:

{ "groupid": "58366a46-0e11-4996-8a91-3cc1f5b1eaf5", "datasetid": "4f25f6ea-c53f-4f11-948d-51ebd39da8ff", "specification/query": "// DAX Query\nDEFINE\nVAR _TodaysDate = TODAY()\n\n\tVAR __DS0FilterTable = \n\t\tTREATAS({\"andy.test@abc.com\"}, 'SDT Clarity Tasks'[Assignee])\n\n\tVAR __DS0FilterTable2 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('SDT Clarity Tasks'[Status])),\n\t\t\tAND(\n\t\t\t\tNOT(SEARCH(\"Closed\", 'SDT Clarity Tasks'[Status], 1, 0) >= 1),\n\t\t\t\tNOT(SEARCH(\"Completed\", 'SDT Clarity Tasks'[Status], 1, 0) >= 1)\n\t\t\t)\n\t\t)\n\n\tVAR __DS0FilterTable3 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('SDT Clarity Tasks'[Phase])),\n\t\t\tNOT(SEARCH(\"Delivered\", 'SDT Clarity Tasks'[Phase], 1, 0) >= 1)\n\t\t)\n\n\tVAR __DS0FilterTable4 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('SDT Clarity Tasks'[ETD])),\n\t\t\t\t\t\t'SDT Clarity Tasks'[ETD] = _TodaysDate\n\t\t\t)\n\t\t)\n\n\tVAR __DS0Core = \n\t\tSELECTCOLUMNS(\n\t\t\tKEEPFILTERS(\n\t\t\t\tFILTER(\n\t\t\t\t\tKEEPFILTERS(\n\t\t\t\t\t\tSUMMARIZECOLUMNS(\n\t\t\t\t\t\t\t'SDT Clarity Tasks'[Assignee],\n\t\t\t\t\t\t\t'SDT Clarity Tasks'[ID],\n\t\t\t\t\t\t\t'SDT Clarity Tasks'[Customer],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t\t\t\t\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day],\n\t\t\t\t\t\t\t__DS0FilterTable,\n\t\t\t\t\t\t\t__DS0FilterTable2,\n\t\t\t\t\t\t\t__DS0FilterTable3,\n\t\t\t\t\t\t\t__DS0FilterTable4,\n\t\t\t\t\t\t\t\"CountRowsSDT_Clarity_Tasks\", COUNTROWS('SDT Clarity Tasks')\n\t\t\t\t\t\t)\n\t\t\t\t\t),\n\t\t\t\t\tOR(\n\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\t\t\tOR(\n\t\t\t\t\t\t\t\t\t\t\tNOT(ISBLANK('SDT Clarity Tasks'[Assignee])),\n\t\t\t\t\t\t\t\t\t\t\tNOT(ISBLANK('SDT Clarity Tasks'[ID]))\n\t\t\t\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\t\t\t\tNOT(ISBLANK('SDT Clarity Tasks'[Customer]))\n\t\t\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year]))\n\t\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month]))\n\t\t\t\t\t\t\t),\n\t\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo]))\n\t\t\t\t\t\t),\n\t\t\t\t\t\tNOT(ISBLANK('LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]))\n\t\t\t\t\t)\n\t\t\t\t)\n\t\t\t),\n\t\t\t\"'SDT Clarity Tasks'[Assignee]\", 'SDT Clarity Tasks'[Assignee],\n\t\t\t\"'SDT Clarity Tasks'[ID]\", 'SDT Clarity Tasks'[ID],\n\t\t\t\"'SDT Clarity Tasks'[Customer]\", 'SDT Clarity Tasks'[Customer],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t\t\t\"'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]\", 'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]\n\t\t)\n\n\tVAR __DS0PrimaryWindowed = \n\t\tTOPN(\n\t\t\t501,\n\t\t\t__DS0Core,\n\t\t\t'SDT Clarity Tasks'[Assignee],\n\t\t\t1,\n\t\t\t'SDT Clarity Tasks'[ID],\n\t\t\t1,\n\t\t\t'SDT Clarity Tasks'[Customer],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t\t\t1,\n\t\t\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day],\n\t\t\t1\n\t\t)\n\nEVALUATE\n\t__DS0PrimaryWindowed\n\nORDER BY\n\t'SDT Clarity Tasks'[Assignee],\n\t'SDT Clarity Tasks'[ID],\n\t'SDT Clarity Tasks'[Customer],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Year],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[MonthNo],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Month],\n\t'LocalDateTable_85d7c4ad-513b-40f5-83df-1aec3770b53d'[Day]\n", "specification/serializerSettings/includeNulls": false }

 

Many thanks !

Categories:
Business Processes
  • Verified answer
    NsL Coder Profile Picture
    NsL Coder 469 Super User 2025 Season 1 on 29 Jul 2024 at 13:50:40
    Power Automate error using Run a query against a dataset
    You can using Power Automate, so it is easy for you to simply use expression that will produce the "fixed date" part of your query.
     
    Instead of
    DATE(2024, 7, 29)

    Assuming you are trying to do greater than or equals to yesterday (7/29/24) and less than today (7/30/24):
    Add 2 compose actions for your 2 dates: (you don't need to use compose actions if you want to simply add all the part of expression into the query, just easier on the eyes)
    Compose Today:
    utcNow()
    or use convertFromUtc(utcNow(), 'Eastern Standard Time') (for example if you are local to Eastern Standard Time)
    Compose Yesterday:
    addDays(outputs('Compose_Today'), -1)
     
    then in your query:
    DATE(@{formatDateTime(outputs('Compose_Yesterday'), 'yyyy')}, @{formatDateTime(outputs('Compose_Yesterday'), 'M')}, @{formatDateTime(outputs('Compose_Yesterday'), 'd')})
  • AW-26071034-0 Profile Picture
    AW-26071034-0 on 29 Jul 2024 at 13:35:30
    Power Automate error using Run a query against a dataset
    Thanks for your help.
     
    Basically I am trying to change this piece of code to use "todays date" rather than a fixed date, so if you could explain how best to do this, that would be really helpful, thank you:
     
    VAR __DS0FilterTable4 =
    FILTER(
    KEEPFILTERS(VALUES('SDT Clarity Tasks'[ETD])),
    AND(
    'SDT Clarity Tasks'[ETD] >= DATE(2024, 7, 29),
    'SDT Clarity Tasks'[ETD] < DATE(2024, 7, 30)
    )
    )

    I get the idea thank you so have added in your suggestion to say give me records where ETD date > yesterday and < tomorrow but I think I have the format wrong as I am getting an error.

    New filter code is:

    utcNow()
    addDays(outputs('Compose_Yesterday'), -1)
    addDays(outputs('Compose_Tomorrow'), +1)
    VAR __DS0FilterTable4 =
    FILTER(
    KEEPFILTERS(VALUES('SDT Clarity Tasks'[ETD])),
    AND(
    'SDT Clarity Tasks'[ETD] > addDays(outputs('Compose_Yesterday'), -1),
    'SDT Clarity Tasks'[ETD] < addDays(outputs('Compose_Tomorrow'), +1),
    )
    )

    Error is:

    "The syntax for 'utcNow' is incorrect."

    Please assist, thanks !

  • CU29071043-0 Profile Picture
    CU29071043-0 2 on 29 Jul 2024 at 10:51:59
    Power Automate error using Run a query against a dataset
    according to copilot, it could be here
     
    VAR __DS0FilterTable4 = 
        FILTER(
            KEEPFILTERS(VALUES('SDT Clarity Tasks'[ETD])),
            'SDT Clarity Tasks'[ETD] = _TodaysDate
        )  // This is the line with the missing parenthesis
    VAR __DS0Core = 
        SELECTCOLUMNS(
            KEEPFILTERS(
                FILTER(
     
  • Michael E. Gernaey Profile Picture
    Michael E. Gernaey 42,617 Super User 2025 Season 1 on 27 Jul 2024 at 03:51:03
    Power Automate error using Run a query against a dataset
    Hi,
     
    I'm not going to parse that whole thing, but the error is clear. Somewhere you have an ) and its incorrect.
     
    So go through your Query with a fine-tooth comb, only you know what you need.
     
    That error usually means you have not correctly used ) where you may have a } 
     
    remember functions use ( ) and text uses { }

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities - Lets Get Started!
Announcing new navigation menus and the Engage with the Community forum!

Quick Links

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Leaderboard >

Last month Overall leaderboard

Featured topics

Question for everyone : How are you using Create Text GPT in AI Builder?

Product updates

Microsoft Power Platform Community release plans