How to Export Dataverse Table to Excel

A Power Apps Dataverse table may be exported to an Excel sheet, did you know that? It is possible to use Power Automate to transform the dataverse table into an Excel spreadsheet.

However, this Microsoft Dataverse tutorial will teach you how to convert a Dataverse table to an Excel quickly and easily. In the excel sheet, we’ll also see how to highlight select particular dataverse fields.

The left image in the screenshot below shows a Dataverse table (Suppliers) converted to an Excel Spreadsheet (right image) using Power Automate or Microsoft Flow.

Export Power Apps Dataverse Table to Excel
Export Power Apps Dataverse Table to Excel

Export Dataverse Table to Excel Using Power Automate

Let’s begin with exporting a dataverse table to an Excel file in Power Apps using flow. Refer to the steps below.

Step – 1:

  • As seen in the screenshot below, the Power Apps Dataverse Solution contains two Dataverse tables: Product Sales and Supplier. For this example, we will take the second table i.e. Supplier.
  • In the Supplier table, there are four fields:
  1. Product ID = This column is a lookup that retrieves data from another table (Product Sales).
  2. Price = This is a currency field.
  3. Supplier Name = This is a single line of text data type column.
  4. Location = There are various names of countries in this Dataverse Choice field.

We are going to convert this Dataverse supplier table to an excel sheet.

Export PowerApps Dataverse Table to Excel
Export PowerApps Dataverse Table to Excel
  • You can see all the fields that are present in the Supplier table once you navigate to the Columns section. Consider the image below.
How to export Power Apps Dataverse Table to Excel
How to export Power Apps Dataverse Table to Excel

Step – 2:

  • Now we will create a new flow that will help to transfer the excel sheet. On the Power Apps Home page, go to Flows -> Expand + New flow -> Scheduled cloud flow.
Export Power Apps Dataverse Table to Excel using Power Automate
Export Power Apps Dataverse Table to Excel using Power Automate
  • Provide a unique flow name (Export Dataverse Table to Excel) and specify the date & time when the flow will run within a particular time period. Click on Create.
Export Dataverse Table to Excel
Export Dataverse Table to Excel

Step – 3:

  • The flow will appear as shown in the figure below once it has been created (Preview – Runs every day). Click on the + New step button to add the new action.
How to export Dataverse Table to Excel
How to export Dataverse Table to Excel
  • Moving on to the next action, search List rows in Dataverse. The action name will appear when you type it into the search bar as List rows [PREMIUM]. Simply click it.
Export Dataverse Table to Excel using Power Automate
Export Dataverse Table to Excel using Power Automate
  • A popup will ask you whether or not to begin your free 90-day trial because the List rows action has a premium version. Tap the Start trial button if you agree.
  • Select and Provide the Table name (Supplier) and the Row count of 7000. By default, the row count will be 5000. If your dataverse table contains more than 5000 records, you will need to adjust the value of the Row count field. Click on the + New step button to add the new action.
Export dataverse table to excel in Power Apps
Export dataverse table to excel in Power Apps

Step – 4:

  • Next, search Create csv in the search bar and insert the Create CSV table action under the Data Operation section.
Power Apps Export dataverse table to excel
Power Apps Export dataverse table to excel
  • This Create CSV table action has the fields below:
See also  PowerApps StartsWith and EndsWith Functions

From = Select this field and provide the value under the List rows action.

Power Apps Export dataverse table to excel using Power Automate
Power Apps Export dataverse table to excel using Power Automate
  • As soon as you click Show advanced options, you will notice an additional field named Columns with the predetermined value of Automatic. Don’t make any changes to this field at this time. Again click on the + New step button.
PowerApps Export dataverse table to excel
PowerApps Export dataverse table to excel

Step – 5:

  • Again search Create file action and add the Create file under the OneDrive for Business section.
Export dataverse table to excel Power Apps
Export dataverse table to excel Power Apps
  • The Create file action has the fields below. Such as:
  1. Folder Path = Specify the Document or folder path where you want to store the excel file (from the OneDrive).
  2. File Name = Provide a unique file name like “New_Product_utcNow()” and as well as the extension as .csv

IMPORTANT NOTES:

-> The “utcNow()” function helps to return the current date and time in UTC (Coordinated Universal Time). The new Excel file name will therefore be saved with the current date and time.
-> Additionally, be sure to include the file name and the “.csv” extension. If you don’t, you can run into problems and the file won’t open properly.

You may get the utcNow() function by selecting the Expression tab and adding it there.

Export dataverse table to excel PowerApps
Export dataverse table to excel PowerApps

3. File Content = Here, we can use the Output field of the Create CSV table section.

Export dataverse table to excel Power Apps using flow
Export dataverse table to excel Power Apps using flow

Step – 6:

That’s all there is to do in the flow. The generated flow needs to be saved and tested right away. the procedures listed below:

  1. Save the flow and then click on the Test button.
  2. Select Manually and click on Test.
  3. Click on the Run flow button on the Run flow pane.
  4. When the flow run is completed, click on the Done button.
Power Apps Export dataverse table to excel using flow
Power Apps Export dataverse table to excel using flow

Step – 7:

We can tell that the generated flow worked as intended because a successful notification is displayed above the page. Go to OneDrive now, and when we open the specific document or folder path, we can see that a new excel file with the name New_Product_<CurrentDateandTime>.csv has been created, as seen below.

How to Export Dataverse Table to Excel in Power Apps
How to Export Dataverse Table to Excel in Power Apps

Step – 8:

  • The excel file will show up with all the different fields as seen in the illustration below. You might assume that the field has been altered or has vanished from the sheet. But don’t be scared. Because only that sheet will have access to all the dataverse table fields.
  • Select Editing from the Viewing panel by clicking on the excel file. Click Convert in the File conversion popup.
how to export datatable to excel in powerapps
how to export datatable to excel in powerapps

Step – 9:

  • Next, select the excel table and go to Home -> Choose any table format under the Format As Table section. Check My table has headers and then tap on OK.
Export dataverse table to excel using microsoft flow
Export dataverse table to excel using microsoft flow

When everything is finished, the excel sheet will appear as the table in the image below.

See also  How to Sort Power Apps Gallery By ID?

Also Read: How to Upload images to Dataverse from Power Apps

Highlights from Dataverse Specific fields in an Excel table

Let’s say we want to draw attention to or highlight certain Dataverse fields in the excel table sheet. What should we do in that situation, or what are the procedures to follow?

For example, in the Excel sheet below, I wanted to draw attention to two different fields: Product ID (Dataverse Lookup field) and Location (Dataverse Choice field). To do this, follow to the directions below.

How to export datatable to excel in power apps
How to export datatable to excel in power apps
  • Go to the existing flow and edit it. Expand the Create CSV table action and then Show advanced options.
  • In the Columns field, choose Custom from the dropdown menu. Two more fields—Header and Value—will show up once we select the Custom option.
  • Here, we will add three fields i.e. Supplier Name (Single line of text), Product ID (LookUp), and Location (Choice).
  1. In the Header section, we can add the text “Supplier Name” and select the Value under the List rows action (under the Dynamic content tab).
Export Dataverse datatable to excel in power apps
Export Dataverse datatable to excel in power apps

2. Next, we will add the lookup field i.e. Product ID. As this one is a lookup, so we cannot add the value directly from the List rows action. Even if we will add also, the value will not appear in the excel table. To workaround with this, we need to follow 2 steps:

  • Go to that excel sheet, select the Product ID field and copy the meta data that appears in the formula bar as shown below.
  • Next, enter the text as Product ID under the Header section. For the Value, go to the Expression tab and apply the code below:
item()?['_tsinfo_productid_value@OData.Community.Display.V1.FormattedValue']

Where,

‘_tsinfo_productid_value@OData.Community.Display.V1.FormattedValue’ = This is the metadata of the Product ID field that is retrieved from the excel sheet. You need to paste here including a single quote. This meta data should apply after the item() function only.

Once everything is completed, click on OK. The lookup field will display as like below.

Export Dataverse table to excel in power apps using flow
Export Dataverse table to excel in power apps using flow

3. The same procedure as above must be followed in order to add the Location field (Choice). We are unable to directly add the value from the List rows action since this column is a choice column. The value won’t show up in the excel table even if we also add.

  • Select the Location field from the excel sheet and copy the meta information that shows in the formula bar as shown below to work with this.
tsinfo_location@OData.Community.Display.V1.FormattedValue

tsinfo_location@OData.Community.Display.V1.FormattedValue = Metadata of the Location field

How to export Dataverse Table to Excel using Power Automate
How to export Dataverse Table to Excel using Power Automate
  • Then, in the Header area, type the text as Location. Apply the following code on the Expression tab for the Value:
item()?['tsinfo_location@OData.Community.Display.V1.FormattedValue']

Where,

See also  How to create and use dataflow in Dataverse

‘tsinfo_location@OData.Community.Display.V1.FormattedValue’ = This is the Location field’s obtained metadata from the excel sheet. You must paste the entire thing in, including the single quote. This meta information should only be used after the item() method.

Click OK once everything has been finished. The choice field will show up like the example below.

Export Dataverse Table to Excel using Flow
Export Dataverse Table to Excel using Flow

Finally, save, test, and run the flow manually. Because a successful message is presented above the page, we can tell that the produced flow functioned as expected.

How to export Dataverse Table to Excel using Flow
How to export Dataverse Table to Excel using Flow

Once again, a new excel file with the name New_Product_<CurrentDateandTime>.csv has been created, as can be seen below, when we open the appropriate document or folder path on OneDrive.

how to export data from dataverse table to excel
how to export data from dataverse table to excel

Open the excel sheet now, and format it into a table using the instructions above. The highlighted fields will then appear in the sheet, as seen below.

Export dataverse data table to excel
Export dataverse data table to excel

These are the steps to export Dataverse Table to Excel using Power Automate.

Additionally, you may like some more Dataverse tutorials:

In this Dataverse tutorial, we will understand how to convert or export a dataverse table to excel sheet using Power Automate, what are the steps we need to follow to achieve it, how to highlight dataverse fields in excel, and many more.

>