The Problem: Iterating through excel rows inside a Power Automate flow can become time-consuming when the sheet exceeds a certain amount of rows.
→ Improving performance can only be done by a replacing the excel sheet with a better performing data source. An Azure Table Storage is such a powerful data source.
Show a preview of the flow...
PRECONDITIONS
For this Use Case you need:
- A Power Automate Premium subscription (for the "Azure Table Storage" Element).
- An Azure subscription with Resource Group. A Storage Account within the Resource Group is needed.
🔍 See: Quickstart - Create an Azure Storage table in the Azure portal | Microsoft Docs / Storage account overview | Microsoft Docs
💡 These subscription cost are outside of Luware and Nimbus product scope.
Show Icon Legend
| 💡 = A hint to signal learnings, improvements or useful information in context. | 🔍 = Info points out essential notes or related page in context. |
| ☝ = Notifies you about fallacies and tricky parts that help avoid problems. | 🤔 = Asks and answers common questions and troubleshooting points. |
| ❌ = Warns you of actions with irreversible / data-destructive consequence. | ✅ = Intructs you to perform a certain (prerequired) action to complete a related step. |
How-To Steps
Create Table Storage in Azure
- Log into the Azure portal
-
Create a Table - you can follow the instructions on Quickstart - Create an Azure Storage table in the Azure portal | Microsoft Docs.
🔍 By default rowKey is a string. It is important that your rowKey holds the phone number, because this column is indexed and thus efficient to search. -
Next, insert some example data for testing purpose. You can use the Storage browser → Tables → (your table name) → Add entity
🔍 Note, that the phone number holds the complete string with country prefix, in the same format as MicrosoftCallerID holds it in our Power AutomateConnector.
✅ Once the table is created and has at least one entry to query on, you can use it inside your Power Automate flow.
Create the flow
✅ Ensure that your service's workflow has a "Accept Conversation" Workflow Activity which starts the "GetOnNewTasks" Trigger Event.
- Start your flow with the a "GetOnNewTasks" Nimbus activity.
-
Add a Azure Table Storage element and filter on the CallerID inside the rowKey column:
- Add an Nimbus "UpdateTask" action. Update the fields according to your table data for instance:
- Customer.FirstName = outputs('Get_entity')?['body/additionalProperties/Firstname']
Fill the Azure Table with Excel Data
✅ Now that everything works, we just need to fill the Excel data into the Azure Storage Table.
You can easily upload a csv using the Microsoft Azure Storage Explorer (Download at http://storageexplorer.com/).
Or you write a script for PowerShell. Here is the code snippet for adding a new row:
$cloudTable = 'crm'
$callerNumber = '+44786536774'
$item = @{Firstname="Mathias"; Lastname="Meier"; Company= "Big Dreams"; PreferedCoulour= "Red"}
Add-AzTableRow -table $cloudTable -PartitionKey 'byCallerId' -rowKey $callerNumber -proper