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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors