Forum Discussion

POSPOS's avatar
POSPOS
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Send Power BI report as excel via email using Power Automate

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 ...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    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's avatar
POSPOS
Icon for Post Partisan rankPost Partisan
1 year ago

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's avatar
SamWiseOwl
Icon for Super User rankSuper User
1 year ago

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's avatar
    Prashant_123
    Icon for Helper I rankHelper I
    1 year ago

    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&reportObjectId=679ad6b9-7c11-4fa5-9327-14852f92c199&ctid=1fdae3e7-1b2e-4655-9856-b4a395be3bad&reportPage=075ddd3b6403af508155&pbi_source=appShareLink&portalSessionId=99b93ecb-568b-4473-bd80-0ac1036596c8

     

     

    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 [email protected]
    7217785817
    Do let me know


    na/apis/powerbi",
    "connectionReferenceName": "shared_powerbi",
    "operationId": "ExecuteDatasetQuery"
    }

     






    SamWiseOwl