At times, you want to do a quick calculation but the Data Source doesn’t have that column present.
Example, you’re populating some data from a SharePoint List which is a Scoreboard. But it doesn’t tell the difference between the current Score and Top Score.
So, to find how many points does the Score trails by the Top Score. To do that, we’ll dynamically add a Column to our Data Table in a Canvas Power App by using AddColumn() function.
SharePoint List & Canvas App
First part is the list itself which you already saw above, I have the below Canvas App –
- The only thing I have in this Canvas App is a Data table which I named as ScoreBoard
Which will look like this
- I’m setting the Data Source of this Data Table to be DynamicList and not the actual SharePoint List. Because, in order to create a dynamic column, I should be using Collections in this demo and I can’t use the SharePoint List as Data Source directly.
- And to write my function, I’ll also add a Button so that on click of which, I can populate my internal Data Source in this case which is Collection called as DynamicList to be populated with data. You can use any other way as suitable.
- Since I want to generate my dynamic column, I’ll first use ClearCollect() method to add the SharePoint List with the dynamic column to my Collection which I’ll call as DynamicList.
All this OnSelect of the Button which I added.
- The formula goes as below
In the above formula,
ClearCollect(DynamicList) is being created to create a Collection called DynamicList and Clear if there’s any pre-existing data in it before populating fresh data.
AddColumns(Scoreboard,”Trial By”, ‘Top Score’ – Score) – Scoreboard is the actual SharePoint List I’m using which doesn’t have the column, Trial By is the dynamic column I’m creating. And my expression is Subtract Score from Top Score so that I get the difference.
- Make sure you select the columns. Perhaps once you run in in your browser, and try to click the button, the column will be available for your selection in the below Edit Fields section. [I had to do a little back and forth, perhaps you might have a smarter way 😊]
Here’s Microsoft Docs on the function – https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping ?WT.mc_id=DX-MVP-5003911
Now, when I run the app, and click on Load Scoreboard button, I see the below data populated with by dynamic column Trail By.
Hope this was useful.
Here are some more Power Automate / Flow posts you might want to check
- Call HTTP Request from a Canvas Power App using Flow and get back Response | Power Automate
- Send a Power App Push Notification using Flow to open a record in Canvas App | Power Automate
- Accept HTTP Requests in a Flow and send Response back | Power Automate
- Terminate a Flow with Failed/Cancelled status | Power Automate
- Adaptive Cards for Teams to collect data from users using Power Automate | SharePoint Lists
- ChildFlowUnsupportedForInvokerConnections error while using Child Flows [SOLVED] | Power Automate
- BPF Flow Step as a Trigger in CDS (Current Environment) connector | Power Automate
- Pause a Flow using Delay and Delay Until | Power Automate
- Generate Dynamics 365 record link in a Flow using CDS connector | Power Automate
- Text Functions in a Flow | Power Automate
- Loop through array of objects in a Flow & Create records in CDS | Power Automate
- Get Count of records retrieved in CDS connector in a Flow | Power Automate