Send Power BI report as excel via email using Power Automate

[POSPOS]

Hi All,
I have a Power BI report with 5 pages. One of the page (3rd page) has a table with multiple rows. My requirement is to send the 3rd page data as an excel attachment via email to users.
Can someone please suggest on how to create a Power Automate flow for this requirement?

Thank you.

[SamWiseOwl]

Hi @POSPOS 

I want this data stored outside of Power BI.

First thing to do is go to View | Performance analyzer and toggle this on.

When you click Performance analyzer a new pane will appear on the right.

This will show you how long each of your visuals takes to load, click on Refresh visuals button.

When you click, all visuals will appear beneath.

Click on the + icon and choose Copy Query to add the DAX code to your clipboard.

We now have the code Power BI uses to generate the visual.

Now to extract the data you want, swap to Power Automate in your browser.

Create a new flow by clicking on New flow | Instant cloud flow.

You can choose from several triggers but here we choose Instant cloud flow.

Give your flow a suitable name and choose Manually trigger a flow to create your new flow.

Manually triggered flows run when you choose but there are other options.

This will open up your designer where you choose all the steps of the flow. Click on the + to add a new step.

Choose Add an action

This will list out all the actions we have to choose from. Search for Run a query against a dataset and you should see Power BI.

We want to run the query copied earlier.

Select the Workspace and Dataset of your published report, then paste the code from your clipboard into Query Text.

Power Automate will create the table we saw in our report.

Now in the top right choose Save and then click on Test to run your flow.

You must save before selecting the Test option.

If you have run this flow before, you can choose Automatically to re-run with previous data.

For now choose Manually and select Test to try your new flow.

You can run the flow by clicking Test.

We should now see each step with a tick next to it. You can now select the Power BI step to see the returned data.

Click on the last step to see more information.

Each step has an Inputs and Outputs section and by choosing Show raw outputs we can see our data.

Clicking here will show you a JSON file of the returned information.

Now you can see all the returned data in the body section of the output.

The headers section includes useful information like run Date.

From here you could email this data or store it somewhere like a SharePoint list!

I imagine you will want to choose Create HTML table and then add the Output of the HTML table to the body of your email.

[POSPOS]

@SamWiseOwl  - Thanks for the steps.

I tried to follow and created a flow as below: Can you pls suggest on how to add a step to receive and email as excel.

But, the email did not come as an excel.

[SamWiseOwl · Accepted solution]

Ahh Sorry I didn't notice you wanted it as an attachment.

Use HTML to embedd the table in the body of the email.

Use CSV to send as an attachment that will open in Excel.

When you are happy it works, change your trigger from manual to Power BI when button is clicked 🙂

[POSPOS]

@SamWiseOwl- Thank you for the detailed steps and now I was able to send the report as a csv attachement via email.
One obervation is that , by default

• the grand total appears as first row
• the columns are not ordered as per the report
• measure fields show up as first column.

Is this something we can control to show the total at the bottom and have the same ordering of fields as developed in Power BI report.

[SamWiseOwl]

If you expand the CSV step there is an advanced option called columns.

Enter the column name you want, then in the second column use the lighting bolt icon to choose the field from Power BI you want in the column.

Create a new row and repeat for each column.

There is a step called FILTER that you could use to get rid of unwanted rows.

Alternatively you could create an Excel file but that requires OneDrive or SharePoint read write permissions which becomes complicated when a variety of users will be running the script. 

Alternatively you could have them all run through your account but where is the fun in that 😛

[POSPOS]

@SamWiseOwl  - Below is a sample report I created.

CSV attachment I received is as below:

As suggested, I checked the advanced options, and I do not see a list of fields from Power BI when clicked on the  lighting bolt icon.

List from the  lighting bolt icon.

[SamWiseOwl]

In the Query you pasted in delete the SubTotal bit if you dont want the grand total, so here I keep the Region[RegionName] bit but delete rollup and "IsGrandTotal" also delete IsGranTotal, 0 from the TopN bit and finally delete IsgrandTotal Desc from the Order By.

The Column order in the Summarize columns is the order of the output.

So here mine is Region, Sum Qty, Sum Price

[Prashant_123]

Hi   

Can you please help me I want to copy data from Power Bi workspace to Excel as I am running my flow , and I am not getting the error but the data is not copying in excel file.
here I am attching the link 

https://app.powerbi.com/Redirect?action=OpenReport&appId=0bb48a38-72f3-420b-84d8-386d210d925f&report...

Also attaching the excel file 

{

"apiId": "subscriptions/8164d157-006d-4158-80c8-81ed00f0e5b9/providers/Microsoft.Web/locations/westus/runtimes/msmanaged-

{

"groupid": "17f0e6dc-92d3-4217-8294-7554480c18a7",

"datasetid": "7514186c-7218-4e79-a45e-7fefa2999697",

"specification/query": "// DAX Query\nDEFINE\n\tVAR __DS0FilterTable = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('CUSTOMER LIST'[Sales Rep])),\n\t\t\tNOT('CUSTOMER LIST'[Sales Rep] IN {\"\"})\n\t\t)\n\n\tVAR __DS0FilterTable2 = \n\t\tTREATAS({\"WEBSITE\"}, 'ORDER SOURCE'[Order Source])\n\n\tVAR __DS0FilterTable3 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('Date Table'[Date])),\n\t\t\tAND('Date Table'[Date] >= DATE(2022, 6, 1), 'Date Table'[Date] < DATE(2025, 6, 1))\n\t\t)\n\n\tVAR __DS0FilterTable4 = \n\t\tTREATAS({\"no\"}, 'NS_BOOKING'[Cancelled?])\n\n\tVAR __DS0FilterTable5 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('BUSINESS UNIT'[BUSINESS UNIT])),\n\t\t\tNOT('BUSINESS UNIT'[BUSINESS UNIT] IN {BLANK()})\n\t\t)\n\n\tVAR __DS0FilterTable6 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('Location-business unit-class'[Custom Class])),\n\t\t\tNOT('Location-business unit-class'[Custom Class] IN {BLANK(),\n\t\t\t\t\"PARTS\"})\n\t\t)\n\n\tVAR __DS0FilterTable7 = \n\t\tFILTER(\n\t\t\tKEEPFILTERS(VALUES('ITEM LIST'[Stock Code])),\n\t\t\tOR(\n\t\t\t\tNOT(SEARCH(\"smoke\", 'ITEM LIST'[Stock Code], 1, 0) >= 1),\n\t\t\t\tNOT(SEARCH(\"lift gate\", 'ITEM LIST'[Stock Code], 1, 0) >= 1)\n\t\t\t)\n\t\t)\n\n\tVAR __DS0Core = \n\t\tSUMMARIZECOLUMNS(\n\t\t\tROLLUPADDISSUBTOTAL('Date Table'[FiscalYear], \"IsGrandTotalRowTotal\"),\n\t\t\tROLLUPADDISSUBTOTAL(\n\t\t\t\t'Date Table'[FY Month No.], \"IsGrandTotalColumnTotal\",\n\t\t\t\t'Date Table'[Month], \"IsDM2Total\"\n\t\t\t),\n\t\t\t__DS0FilterTable,\n\t\t\t__DS0FilterTable2,\n\t\t\t__DS0FilterTable3,\n\t\t\t__DS0FilterTable4,\n\t\t\t__DS0FilterTable5,\n\t\t\t__DS0FilterTable6,\n\t\t\t__DS0FilterTable7,\n\t\t\t\"SumAmount__Net_\", CALCULATE(SUM('NS_BOOKING'[Amount (Net)])),\n\t\t\t\"Booking___Difference\", 'NS_BOOKING'[Booking % Difference]\n\t\t)\n\n\tVAR __DS0PrimaryWindowed = \n\t\tTOPN(\n\t\t\t102,\n\t\t\tSUMMARIZE(__DS0Core, 'Date Table'[FiscalYear], [IsGrandTotalRowTotal]),\n\t\t\t[IsGrandTotalRowTotal],\n\t\t\t0,\n\t\t\t'Date Table'[FiscalYear],\n\t\t\t1\n\t\t)\n\n\tVAR __DS0SecondaryBase = \n\t\tSUMMARIZE(\n\t\t\t__DS0Core,\n\t\t\t'Date Table'[FY Month No.],\n\t\t\t[IsGrandTotalColumnTotal],\n\t\t\t'Date Table'[Month],\n\t\t\t[IsDM2Total]\n\t\t)\n\n\tVAR __DS0Secondary = \n\t\tTOPN(\n\t\t\t102,\n\t\t\t__DS0SecondaryBase,\n\t\t\t[IsGrandTotalColumnTotal],\n\t\t\t1,\n\t\t\t'Date Table'[FY Month No.],\n\t\t\t1,\n\t\t\t[IsDM2Total],\n\t\t\t1,\n\t\t\t'Date Table'[Month],\n\t\t\t1\n\t\t)\n\n\tVAR __DS0BodyLimited = \n\t\tNATURALLEFTOUTERJOIN(\n\t\t\t__DS0PrimaryWindowed,\n\t\t\tSUBSTITUTEWITHINDEX(\n\t\t\t\t__DS0Core,\n\t\t\t\t\"ColumnIndex\",\n\t\t\t\t__DS0Secondary,\n\t\t\t\t[IsGrandTotalColumnTotal],\n\t\t\t\tASC,\n\t\t\t\t'Date Table'[FY Month No.],\n\t\t\t\tASC,\n\t\t\t\t[IsDM2Total],\n\t\t\t\tASC,\n\t\t\t\t'Date Table'[Month],\n\t\t\t\tASC\n\t\t\t)\n\t\t)\n\nEVALUATE\n\t__DS0Secondary\n\nORDER BY\n\t[IsGrandTotalColumnTotal],\n\t'Date Table'[FY Month No.],\n\t[IsDM2Total],\n\t'Date Table'[Month]\n\nEVALUATE\n\t__DS0BodyLimited\n\nORDER BY\n\t[IsGrandTotalRowTotal] DESC, 'Date Table'[FiscalYear], [ColumnIndex]\n\n",

"specification/serializerSettings/includeNulls": true

}

OutputsShow raw outputs

statusCode



200headers



{

"Cache-Control": "no-store, must-revalidate, no-cache",

"Pragma": "no-cache",

"Transfer-Encoding": "chunked",

"Strict-Transport-Security": "max-age=31536000; includeSubDomains",

"X-Frame-Options": "DENY",

"X-Content-Type-Options": "nosniff",

"RequestId": "0d36350d-afd3-4b03-b80c-b310fa7b65ee",

"Access-Control-Expose-Headers": "RequestId",

"x-ms-client-region": "unitedstates",

"x-ms-flavor": "Production",

"X-Ms-Workflow-Resourcegroup-Name": "1FDAE3E71B2E46559856B4A395BE3BAD-DEFAULT1FDAE3E71B2E46559856B4A395BE3BAD-ENV",

"x-ms-workflow-subscription-id": "8164d157-006d-4158-80c8-81ed00f0e5b9",

"x-ms-environment-id": "default-1fdae3e7-1b2e-4655-9856-b4a395be3bad",

"x-ms-tenant-id": "1fdae3e7-1b2e-4655-9856-b4a395be3bad",

"x-ms-dlp-re": "-|-|-",

"x-ms-dlp-gu": "-|-",

"Timing-Allow-Origin": "*",

"x-ms-apihub-cached-response": "false",

"x-ms-apihub-obo": "false",

"Date": "Fri, 13 Dec 2024 12:05:59 GMT",

"Content-Type": "application/json",

"Content-Length": "5834"

}body



{

"results": [

{

"tables": [

{

"rows": [

{

"Date Table[FY Month No.]": 1,

"[IsGrandTotalColumnTotal]": false,



these are the input and output parametres ? how to do can you please help me

 

I am stuck in only 1 task , I can show live also my email is gupta.prash008@gmail.com
7217785817
Do let me know


na/apis/powerbi",

"connectionReferenceName": "shared_powerbi",

"operationId": "ExecuteDatasetQuery"

}

@SamWiseOwl