web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : KQEXakZcBuJlFINg6ECfap
Power Platform Community / Forums / Power Apps - Microsoft Dataverse / Converting Excel appli...
Power Apps - Microsoft Dataverse
Answered

Converting Excel application into PowerApps (Complex calculation problem)

Like (0) ShareShare
ReportReport
Posted on 3 Oct 2020 02:49:15 by ragavanrajan 7,036 Most Valuable Professional

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?  

 

 

Categories:
Business Rules
Solution Explorer
I have the same question (0)
  • Verified answer
    EricRegnier Profile Picture
    EricRegnier 8,716 Most Valuable Professional on 04 Oct 2020 at 06:40:38
    Re: Converting Excel application into PowerApps (Complex calculation problem)

    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-relationships
    2. Calculated and rollup fields: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/calculated-rollup-attributes
    3. Business Rules: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-create-business-rule
    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/557121

    Hope this helps!

  • ragavanrajan Profile Picture
    ragavanrajan 7,036 Most Valuable Professional on 04 Oct 2020 at 10:02:20
    Re: Converting Excel application into PowerApps (Complex calculation problem)

    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 Profile Picture
    EricRegnier 8,716 Most Valuable Professional on 04 Oct 2020 at 18:25:54
    Re: Converting Excel application into PowerApps (Complex calculation problem)

    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-of-lookups
    Once you have the lookup fields defined, then you can easily pull data from lookup entity in queries, forms, views, etc.

  • ragavanrajan Profile Picture
    ragavanrajan 7,036 Most Valuable Professional on 04 Oct 2020 at 20:26:22
    Re: Converting Excel application into PowerApps (Complex calculation problem)

    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 Profile Picture
    EricRegnier 8,716 Most Valuable Professional on 05 Oct 2020 at 23:23:00
    Re: Converting Excel application into PowerApps (Complex calculation problem)

    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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Tom Macfarlan – Community Spotlight

We are honored to recognize Tom Macfarlan as our Community Spotlight for October…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 752 Most Valuable Professional

#2
developerAJ Profile Picture

developerAJ 472

#3
Michael E. Gernaey Profile Picture

Michael E. Gernaey 358 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans