Use Power Automate to extract filtered data to Excel

[jtooke]

Hello,

I am looking to use Power Automate to allow a user to extract data from a Power BI report to Excel. The data should include any on-screen slicer selections. Due to the data size, I have been looking at doing this through the Power Automate "query a dataset", but I am unable to get the query correct to update dynamically based on filtered selections.

I have included a sample screeshot with the page I am looking to extract data from. The slicers pull from the RR table which is related to Table. The columns that will be extracted come from Table.

Table

IDOverall InherentA InherentB InherentOverall ResidualA ResidualB Residual

1	High	Medium	Low	Low	Low	Low
2	Medium	Medium	Low	Medium	Medium	Low
3	Medium	Medium	Low	Low	Low	Low
4	Medium	Medium	Low	Low	Low	Low
5	High	Medium	Low	Low	Low	Low

RR

IDGroupRating TypeRating

1	Overall	Inherent	High
1	Overall	Residual	Low
1	A	Inherent	Medium
1	A	Residual	Low
1	B	Inherent	Low
1	B	Residual	Low
2	Overall	Inherent	Medium
2	Overall	Residual	Medium
2	A	Inherent	Medium
2	A	Residual	Medium
2	B	Inherent	Low
2	B	Residual	Low
3	Overall	Inherent	Medium
3	Overall	Residual	Low
3	A	Inherent	Medium
3	A	Residual	Low
3	B	Inherent	Low
3	B	Residual	Low
4	Overall	Inherent	Medium
4	Overall	Residual	Low
4	A	Inherent	Medium
4	A	Residual	Low
4	B	Inherent	Low
4	B	Residual	Low
5	Overall	Inherent	High
5	Overall	Residual	Low
5	A	Inherent	Medium
5	A	Residual	Low
5	B	Inherent	Low
5	B	Residual	Low

Any help is appreciated, thank you!

[tharunkumarRTK · Accepted solution]

@jtooke 

When you are creating a power automate flow for this use case, do not create a flow in power automate portal, instead create in power bi desktop. Otherwise, your flow might not be able to capture the selections in the power bi slicer. 

To prepare the dax query easily, I would suggest you to create a table visual in your report and make sure unwanted columns are removed, columns are renamed appropriately  and totals are disabled from the visual.

Place the slicer and select one or two values, Then from the performance analyzer you will be able to capture the visual dax query. 

You can use the same in your power qutomate flow. This is not completed yet, as the slicer selections would be hardcoded, you can identify the section where the hardcoded values are present in the dax query and replave them with the values capture by the flow. 

Note: if it is only one value then you can directly replace the value with the captured value, if there are multiple values selected in the slicer then you might need to use join/concatenate function in power automate to concatenate all the values delimted by a comma

here is a detailed article: https://powerbi.microsoft.com/en-us/blog/unlocking-new-self-service-bi-scenarios-with-executequeries...

Connect on LinkedIn

read my blogs here: techietips.co.in

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[lbendlin]

Otherwise, your flow might not be able to capture the selections in the power bi slicer. 

The Power Automate visual has its own value columns. It will only be impacted by slicers if the data model has the neessary joins.

If you lose the trigger payload in the flow you need to do is reassign the array in the trigger options.

[cengizhanarslan]

1) Capture slicer selections as values in the flow

In Power Automate, the easiest way is to use the Power BI button visual (or the Power Automate visual) and pass the slicer fields as inputs.

You want to pass the selected values for:

• RR[Group]

• RR[Rating Type]

• RR[Rating]

If multi-select is allowed, pass them as arrays/strings you can split.

2) Use a DAX query with TREATAS to apply those slicer selections

Because your slicers are on RR but you’re extracting columns from Table, you should filter RR first, then let the relationship filter Table via ID.

Example DAX query (single-select version):

DEFINE
    VAR vGroup      = @GroUp
    VAR vRatingType = @RatingType
    VAR vRating     = @Rating

    VAR FilterRR =
        CALCULATETABLE (
            VALUES ( RR[ID] ),
            TREATAS ( { vGroup }, RR[Group] ),
            TREATAS ( { vRatingType }, RR[Rating Type] ),
            TREATAS ( { vRating }, RR[Rating] )
        )

EVALUATE
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table',
            "ID", 'Table'[ID],
            "Overall Inherent", 'Table'[Overall Inherent],
            "A Inherent", 'Table'[A Inherent],
            "B Inherent", 'Table'[B Inherent],
            "Overall Residual", 'Table'[Overall Residual],
            "A Residual", 'Table'[A Residual],
            "B Residual", 'Table'[B Residual]
        ),
        TREATAS ( FilterRR, 'Table'[ID] )
    )

[lbendlin]

Consider using the Power Automate visual in Power BI. It can be subjected to the same filters as the other visuals.

[hnguy71]

Hi @jtooke 

Using Power Automate to Execute a DAX query requires a valid query to be sent. It will execute against your model based on the query execution parameters.

For example:

EVALUATE RR

This would return an output of your 'RR' table. The problem with your approach is that on Power BI service, you cannot send the result of a visual calculation to Power Automate. 

Instead, you have a few other approaches that would work. On the top of my head, two comes to mind:

1. Embed PowerApps into your model. This will pull the data directly from your model along with any filters your user has selected. From there, you can create an export button which would save the current table or gallery result and you can ask it to generate an excel file and have it sent to their email or your desired location.

2.  Give them the ability to connect to your model by live connecting to your model via Excel. With this, they can explore the data as they see fit.