Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Mike7532
Frequent Visitor

Power Automate Run Query Against a Dataset Syntax?

I am trying to get Power Automate to generate an output from a query against a Power BI dataset. With a test dataset containing a table named StaffingRequest, I have tried:

Table.SelectRows(StaffingRequest, each [CC_Check_Firm_Offer_Due] = "Firm offer overdue")

and also

EVALUATE Table.SelectRows(StaffingRequest, each [CC_Check_Firm_Offer_Due] = "Firm offer overdue")

I keep getting error messages like:

{"error":{"code":"DatasetExecuteQueriesError","pbi.error":{"code":"DatasetExecuteQueriesError","parameters":{},"details":[{"code":"DetailsMessage","detail":{"type":1,"value":"Query (1, 10) The syntax for 'Table' is incorrect. (EVALUATE Table.SelectRows(StaffingRequest, each [CC_Check_Firm_Offer_Due] = \"Firm offer overdue\")\n)."}},{"code":"AnalysisServicesErrorCode","detail":{"type":1,"value":"3238920194"}}]}}}

I know the syntax for the Table.SelectRows function is correct as I have tested it in Power BI using the last applied step in place of the table name, and it works fine:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsssylXIT0tLLVLIL0stSilNVYrVwS4cCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CC_Check_Firm_Offer_Due = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CC_Check_Firm_Offer_Due", type text}}),
    #"SelectRows" = Table.SelectRows(#"Changed Type", each [CC_Check_Firm_Offer_Due] = "Firm offer overdue")
in
    #"SelectRows"

 

What is missing???

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Mike7532 

 

Where did you execute this query? Table.SelectRows is a Power Query function which should be used in Power Query Editor or Dataflows in the service. The language used in Power Query is called M language. While EVALUATE is often used to evaluate a DAX expression. DAX is a totally different language from M. 

 

If you want to execute a DAX query against a Power BI dataset and export data to a file, you can refer to Curbal's videos as below:

Export from Power BI to Excel up to 🔥100k rows 🔥| Run a query against a dataset 

Automatic exports of Power BI data 🤖 | Run a query against a dataset 

 

If you want to use Power Automate to export data without executing a query, you can refer to the following videos:

Power BI export to excel and csv with Power Automate 

How to Export filtered data to Excel from Power BI button using Power Automate? 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

Hi @Mike7532 

 

Where did you execute this query? Table.SelectRows is a Power Query function which should be used in Power Query Editor or Dataflows in the service. The language used in Power Query is called M language. While EVALUATE is often used to evaluate a DAX expression. DAX is a totally different language from M. 

 

If you want to execute a DAX query against a Power BI dataset and export data to a file, you can refer to Curbal's videos as below:

Export from Power BI to Excel up to 🔥100k rows 🔥| Run a query against a dataset 

Automatic exports of Power BI data 🤖 | Run a query against a dataset 

 

If you want to use Power Automate to export data without executing a query, you can refer to the following videos:

Power BI export to excel and csv with Power Automate 

How to Export filtered data to Excel from Power BI button using Power Automate? 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Great stuff, 

 

how do i use this method to export data with all the relevant slicers and filters, 

i mean "what is actually selected in the report" and not "fixed in the dax" itself ?

Thank you very much for your reply! I realized after doing a bit more reading that Power Automate was looking for DAX and not M. I had tried some DAX previously, but I was unfamiliar with the EVALUATE command and none of my DAX attempts included an EVALUATE command.

I know this post is getting old now but I stumbled across it looking for the same thing.
To quickly and easily generate a dataset query, you can do it from inside Power Bi and copy/paste it into Power Automate, or wherever.

1. In Power BI click on "View" > "Performance Analyzer"

2. Click "Start recording"

3. Refresh your data by clicking "Refresh"

4. Click "Stop recording"

5. You will then have a representation of your data (mine was in a table), so click the "+" next to it then click "Copy query" and paste it wherever you need it. Very fast and most importantly - accurate!

Hi @Mike7532, I'm trying to do something similar. Would you mind posting your code below to give me some guidance? 

Thank you!

Here is what I ended up using:

 

EVALUATE FILTER('Staffing Request', [DAX_Check_Firm_Offer_Due]="Firm Offer Overdue")

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.