Converting Excel application into PowerApps (Complex calculation problem)

[ragavanrajan]

Hello Experts, 

   Good day. I thought I will ask this question in this forum to get proper solution. I will try to explain in a brief way. 

Background: We have an complex Excel legacy application which consists of 20+ sheets. Complex refers to Vlookup formulas , Sumif and Pivot tables. "Summary Sheet" is high level aggregation for senior mangers which is derived from 20+ tabs. 

Using SharePoint:  Due to business decision and licensing cost of PowerApps. We have decided to use SharePoint online as data source. Basically, What I have done is 

 Every Excel sheet is  = SharePoint List 

 Everything went well, I have used Data table to connect to SharePoint list and used "AddColumns" to perform basic calculations and even Lookup formula in PowerApps to reflect the Excel complex calculations. 

Problem:   Datatable Add columns functionality is great but the newly added columns and its value I am not able to reference it to other screen. Summary screen. Following are the approaches I have tried so far : 

1. Basically I want all the newly added columns and its value should update back to SharePoint list.  So that reporting team can build a Power BI report out of it.  With AddColumns in Data Table it is always a temporary memory as far as I understand.

2. I have done my research and used collections on top of datatable and used "For All " with Patch to update the SharePoint list. But it is only doing the insert instead of update. 

3. Also as an alternative approach I have used PowerAutomate to update my SharePoint columns for lookups column using condition, it is going an infinite loop due to the trigger. "when an Item is created or modified" 

Using CDS as solution:  Senior management wants this APP as soon as possible. 

 Now I am here to get proper advise before  I create entities and relevant columns in CDS. I wanted to know for the described problem, is CDS a proper database to create complex lookup columns, sumif and Pivot table aggregation to other entities ? 

How do we solve this problem?  What is the actual way of transforming my Excel legacy to a proper PowerApp?  

[EricRegnier · Accepted solution]

Hi @ragavanrajan,

The short answer is yes, CDS can support complex data models. It supports relational entities/tables like SQL Server and in fact is SQL Server is the backend. Can you also apply low-code/no-code business logic to satisfy your complex requirements with the help of the following:

1. Ensure you have the proper relationships between the tables/entities/sheets set with relationships: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-edit-entity-relationship...
2. Calculated and rollup fields: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/calculated-rollup-attribute...
3. Business Rules: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-create-business-r...
4. Power Automate with the CDS (current environment) connector: https://docs.microsoft.com/en-us/connectors/commondataserviceforapps/ 
5. Workflows: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/overview-realtime-workflows

BTW CDS is more than a database, for more info: https://powerusers.microsoft.com/t5/Common-Data-Service-for-Apps/CDS-vs-SQL-What-to-choose-when/m-p/...

Hope this helps!

[ragavanrajan]

Hi @EricRegnier , thanks for the detailed reply and the links. Honestly it is very useful and I have gathered some key points to put forward my decision to business.  Rollup is useful to achieve my sum if and aggregation. 

Still the mystery to me is what is the equivalent to Excel Vlookup formula in CDS ? I had a look at Lookup field but that is not matching with Excel Vlookup. Is there any link which you can share would be grateful. 

[EricRegnier]

Glad it helps. It would be any relationship defined between 2 entities. The most common method is via lookup fields which creates a one-to-many relationship storing the foreign key (primary entity ID/Guid) in the related table. https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/types-of-fields#different-types...
Once you have the lookup fields defined, then you can easily pull data from lookup entity in queries, forms, views, etc.

[ragavanrajan]

Thanks @EricRegnier , 

 I understand the relationship is equivalent to Excel vlookup. Still in some of the places I dont understand how we achieve this. To give more specific example. In Excel: 

=IFERROR(I5*VLOOKUP(G5,'Lookup Data'!$D$3:$E$4,2,FALSE)/1.0336,"")

 To achieve the above formula from Excel: Here are the things I have done in CDS 

I have created two entities called Entity A and LookupEntity . In Entity A I have created a field called "App Rate" which is of type Lookup. As per the above Excel formula I need to make a lookup to LookupEntity and only if my current Entity A - field "x" matches the LookupEntity I want to bring the second column from Lookup Entity.  How do we achieve this using Relationship? Any pointers would be helpful.

[EricRegnier]

If I understand correctly, you want to get a field from LookEntity on Entity A only if LookEntity matches something (i.e. X) on Entity A.

In CDS context, the lookup field on Entity A will have a value to the right App Rate record where applicable or will be empty if no App Rate. This takes care of "only if LookEntity matches something (i.e. X)".

To get the field from LookEntity, there a a few ways to achieve this depending on your UX and where/how you want to see this:

• If on the form, then create a quick view form with the fields from LookEntity you want to see and add it on Entity A form
• If within a view/query/dashboard, edit your view and add the relevant related fields