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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors