In the second blog of our Automate Excel series, we will examine how you can automatically transfer data from Dataverse to Excel using Encodian Flowr’s Add Rows to Excel action. The action allows you to add one or multiple rows of data at a time.
The scenario is that Customer data is held in a table in Dataverse. Any active customers have to be added to an Excel table so the data can be shared internally. Instead of having to transfer this data manually, an instant flow has been created that just needs to be run when the data needs to be shared. This blog won’t be looking at how the data is entered; we will just be focussing on getting the data to Excel.
The Dataverse table is simple, with only 6 added columns:
The Service and Industry columns are local choices, as the options aren’t being used elsewhere. This will slightly affect how we get the label values in the Power Automate.
The Active column determines whether the customer is currently using any services, if they aren’t active they are a historical customer at the current time. Status is a default column in Dataverse tables that sets a record’s status as Active or Inactive, however you can’t change the data manually from Dataverse, you can activate/deactivate a record from a Power App or a Power Automate. For demo purposes, I created the Active column; however, in a real solution, you may want to use the Status column as it is OOB, and you wouldn’t normally enter data straight into Dataverse.
The Power Automate is manually triggered. However, if you needed to run the report at set intervals (monthly/quarterly), you could make the flow a scheduled flow.
The excelData variable is initialised as an empty array variable.
List rows point to the Customers Dataverse table. It has a filter query to filter for all active contacts. When using the Yes/No Dataverse choice option, the results are boolean values, so your filter query will be:
Yes/No_Column_Name eq true Yes/No_Column_Name eq false
You don’t need to use the expression boolean values for the filter query.
Next, a new JSON entry into the excelData array needs to be made for each active customer. The key values have to match the Excel table column header values.
If you just put the Service or Industry dynamic content the same way you have done for the other key values, it will return a numerical value. That value is the value of the choice but not the text label.
To access the text label, you instead need to use these expressions:
items('Apply_to_each')?['en_service@OData.Community.Display.V1.FormattedValue']
items('Apply_to_each')?['en_industry@OData.Community.Display.V1.FormattedValue']
My JSON key values are the names of my columns in Excel. However, it doesn’t matter what your key values are; the action will add the data column by column. The difference here between using data in a table and data not in a table is that with data in a table, you don’t need to specify the starting row and column; it will automatically add the data from the first available line in the table.
If your Excel document has merged cells, you must account for this in your data JSON.
If you needed to populate the file above, your data JSON would need to look like this for each row:
{
"NUMBER":"1",
"NAME":"Hello",
"E":"",
"F":"",
"G":"",
"H":""
}
Now that our data array is complete, we must get the template Excel file. In this example, OneDrive is being used. When using Flowr’s ‘Add Rows to Excel’ action, it doesn’t add rows to an existing file, instead it will make a copy of that file and add the rows to a new copy, this means that it will be need to be saved as a new file. At the end of the Power Automate, the template Excel file will stay the same, and there will be a new file containing the added data.
A string needs to be provided for the Row Data input. We can convert our array variable into a string using the following expression:
string(variables('excelData'))
The last step is to create a new file using the file content from the ‘Add Rows to Excel’ step. To make the file name unique, I have added the date using:
utcNow('dd-MM-yyyy')
These are the results:
If you prefer videos, check out Sophie’s companion video below!
Search 150+ Actions to see how Flowr can save you time
Sign up for your free 30-day trial; no cards, catches, or contracts.
No job is too big or too small for our Professional Services team!
UPDATE: We’re excited to announce some significant updates to Flowr for Power Automate! As of October 2024, we’ve improved by updating action names and splitting Flowr’s central Power Automate connector into nine specialized connectors. These changes will make your workflow faster, smoother, and more efficient. The new action names are more precise and intuitive, saving you time, while the focused connectors enhance performance and flexibility. This update also helps future-proof the platform for even more powerful features. Check out our updated action names blog.
Technical Evangelist