Can someone point me to a tutorial of how to create Excel rows from a Dataverse table using Power Automate? For example, I have a Dataverse table with one of the columns being "color". For every record with "blue", I want to add a row to an Excel file. I'm having a hard time finding a youtube or blog that explains this but I'm sure I'm just not searching well enough.

I've tried to connect to a Dataverse table but not sure how to loop it through all the rows. Also don't quite understand all the commands in the Dataverse filter, I'm used to PowerApps filters but getting used to eq and ne, etc..

CodePudding user response：

There are quite a few ways to achieve the result you want. I've gone for a relatively easy approach and one that should do what you want but depending on performance, etc. you may want to change it out for something else.

Firstly, I created a table in DataVerse with a few dummy fields, obviously, you just need to use your own table.

Here's the structure of the table so you have context and I've highlighted the additional fields that I want to pull out (along with the ID, Name and Created On) ...

Now in my flow, I've added the Dataverse -> List rows action. Specifically, I have filtered the data to the cr160_color column for the value blue as you want to do based on your scenario.

The next step involves traversing the data retrieved from the table and writing it out to an Excel document on a SharePoint workspace that has a table (must be defined as a table) with the following fields.

This is the action step in the flow ...

When it executes, I get my data ...

If you run the flow again, it will double up all of the data so if you need to clear the table before loading again (you could always try updating or delta loading if you can be bothered) then you can inject a step before the loading of the data to clear the table.

You can do that with an office script. To do that, in Excel online, you should have a tab in the ribbon called Automate ...

In there, create a new script, call it Clear Table Data and add this code ...

function main(workbook: ExcelScript.Workbook, worksheetName: string, tableName: string)
{
  let worksheet = workbook.getWorksheet(worksheetName);
  let tableToClear = worksheet.getTable(tableName);

  let rowsToDelete = tableToClear.getRangeBetweenHeaderAndTotal().getRowCount();
  
  try {
    tableToClear.deleteRowsAt(0, rowsToDelete - 1);
  }
  catch { }
}

Now inject that step in your flow before it loads all of the data back in ...

This is the final flow ...