Export Dataverse view to Excel using Power Automate

[CharlesS]

Hello!

Data from a Dataverse view can easily be exported to Excel manually, either directly from Dataverse (in the Maker Portal) or from within a model-driven app (if the "Export to Excel" permission is enable for the user's role).

However, is there an easy citizen-developer way to export a view to Excel using Power Automate? For example, a flow could be triggered by a button, then the data in a view would be exported to Excel in a single step, and sent wherever in subsequent steps.

I know you can do this with Power Automate with Apply to each logic (row by row) to get the same result, but the process is not intuitive for entry-level citizen devs and requires several steps. 

This is a hybrid Power Automate/Dataverse question, but I decided to post it in this forum because I like you guys so much 😁

Thanks

Charles

[mahoneypat · Accepted solution]

This flow seemed to work, using List Rows and Create Table in Excel actions.  I had to create the flow within an environment for Dataverse for Teams so that the CDS actions were available to choose.  If you didn't want to pre-create the Excel file, you could instead create a CSV table instead, create the csv file, and then store that somewhere (which could then be easily opened in Excel).

Regards,

Pat

[CharlesS]

Thanks for your reply Pat,

Yes, that is a good suggestion, but there are a few problems with this approach:

1. As you mentioned, it requires the pre-creation of the Excel file, which is not always possible or practical.

2. Indeed, you could create a CSV table. However, if the purpose of the data export is to send a report to a business user, the CSV file is "ugly". Business users prefer Excel files with fancier formatting.

3. In both cases, you are exporting all of the data in the table (or filtered as per the List rows step), but you are not exporting a specific view, as you would from within a model-driven app. You would basically have to re-create the entire view's logic programmatically, which is out of reach for many entry-level makers.

Here's where I'm going with this: I would like to submit an idea to this effect, but I want to make 100% sure there isn't an easy, breezy, beautiful way to get the job done already.

Best Regards,

Charles

[EricRegnier · Accepted solution]

Hi @CharlesS,

It is possible to export to a new Excel file in Power Automate. As per your point #1, it needs a file pre-created, but you can just use a simple blank Excel template, then export the desired data to a new file.

To use a predefined view, pass the parameter savedQuery in the List Row action. Not sure the exact place it should go in that action as I never personally tried savedQuery in Power Automate. More info on savedQuery : https://docs.microsoft.com/powerapps/developer/data-platform/webapi/retrieve-and-execute-predefined-... 

If you can't get it to work with List Rows action, you can just use the HTTP action to retrieve the data. More info: https://dynamics-chronicles.com/article/power-automate-and-d365-api-call-tutorial 

Hope this helps!

[CharlesS]

Thank you @mahoneypat and @EricRegnier for your solutions! I would still love to see an easy, out-of-the-box solution to export views from Dataverse tables using a Power Automate action... So I have gone ahead and posted this idea:

Export Dataverse view to Excel using Power Automat... - Power Platform Community (microsoft.com)

[Anonymous]

Hi mahoneypat

Regarding your 1st solution: Create Table does not enter any data to the created table. So this is not a solution for the asked problem. I think "add Row" is the only solution to add data to the Excel table.

The obvious data transaction "export from ... to excel" is not supported by Flows.

[manoj007]

Hi, I have multiple table in my app, so a record will be created with combination of tables, i want to export all those records from multiple tables, can you help me?

eg: I have customer complaint app, where a complaint will be created using multiple tables and a record will be created.

[EricRegnier]

Hi @manoj007,

You can still do it with Power Automate, where you'll have to construct the CSV or Excel file from the multiple tables. You can also build your query with linking (joining) the multiple tables with FetchXml (suggest to use the FetchXmlBuilder from XrmToolbox) and use that FetchXml in the List Rows action. Hope this helps!