cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sudhavi_84
Helper II
Helper II

Power APP from Excel or list

Hi Experts,

I have a question not sure I am posting here correctly or not.

 

1.) Current Process, We have latest 3 Months of data (projects) maintaining in Excel, and we comparing always Budget Vs latest 3 months of data of all projects.

user will check against budget with latest month and enters the value in same excel sheet about variances..Variances can be in different categories..

 

Is it possible that we can do this in Power APP? I don't want to do this in Excel because it is too much time consuming and in App  they can enter manually variances for each project..

 

2.)  Example in Latest month, projects will be repeated twice or thrice or more, so first Initially it should be summarised values always based on Month/year, so first should I do in List to summarise and then should I need to incorporate into Power APP?

 

Please guide me I am so excited to do this in Power APP for users

 

Thank you in Advance

1 ACCEPTED SOLUTION

Accepted Solutions

OK, good to hear you understand why adding columns over time is a bad idea. Minimizing the number of columns is a good thing too. Power Apps is a cloud-based service and therefore app performance is often tied to the quantity of data that must be transferred from server to user and vice-versa. One table with 40 columns is typically less performant than two tables with 20 columns each, since you have influence over when each table is loaded or operated upon.

 

This may not be a big deal for a simple app, but as your apps become more complex, this will become more of an issue.

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

View solution in original post

14 REPLIES 14
BCLS776
Super User
Super User

While Power Apps can read data from a Table in Excel, it is not the best data source available to work with the platform, mostly due to speed and data limits. A Sharepoint list is a much better option.

Some new users how a Power App will fill out an Excel sheet at specific cells and sometimes with formulas. Unfortunately, that is functionality that does not yet exist.

That said, if you've got lots of data in Excel to load into database tables, a Power App can help you do it.

Hope that helps,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
Sudhavi_84
Helper II
Helper II

Perfect @BCLS776 ,

 

I will do it then I need some guidance on this on going forward to start up

 

Currenlty in Excel We have Budget and 3 Latest Months (Inlcudes Repeated Projects in Monthly and Budget File).

 

We have Year1, Year2, Year3,Year 4 columns includng Monthly columns for each years.

 

So first I may need to summarise in List isn't it? So that project will appear once only based on criteria?

 

Or should I not summarise in list get everything in Canvas app in text boxes by writing query or something?

 

I have lots of data in Excel and we have various templates in each sheet like comparing Budget vs Current Month, Budget vs Last Month, Budget vs Last before Month etc... and lets say Budget Vs Current Month we have variances, and that variances need to fill in 5 Categories like timing delay or covide delay or any other delay etc..

 

If I get this in Power APP, that would be amazing and users can select there own project and enter the variances manually like in excel etc...

 

Could you please let me know what best I can do to acheive this in power APP?

 

Thank you:-)

Yes, spend a bit of time at the start to plan your data. In your case, consider at least two lists: A Projects list and a Budgets list. The Projects list will contain details specific to your projects, including drawing a budget number from the Budgets list. The Budgets lists will contain details specific to budgets, such as the year and amounts. Give each "object" in your app its own list/table.

When you get to building your app, Power Apps does great things with Form and Gallery controls. These are the most feature-rich ways of displaying and editing data from data sources such as lists. You can set up an interface within those that give users just the info they need and can accept their information back. Some good reading is available at this link.

 

That's only a start. As you get further into your app, do some searches on here to look for posts related to your question, or post a new question for the community to answer.

 

Good luck,

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
Sudhavi_84
Helper II
Helper II

That's Awesome @BCLS776 ,

 

One more question for today sorry

 

Currently in Excel we are maintaining latest 3 Months(Jun,Jul and Aug) tabs and Budget Tab

All 4 tabs includes Yearly and Monthly.

 

So should I first Summarise this 4 tabs in to 4 separate list? so that i can create in Canvas app and pull the data and appear in text boxes etc? and additional text boxes so that can fill the variance manually and it will update into the List? and if previous variances are already avaliable, then it will show into the text boxes as well correct?

 

I am very new to the APP and your link will be very much helpfull for sure. But i am just making sure that i am planning properly for this on goibg forward..

 

And also same question

Should I summarise the Excel in to List when I move all 4 tabs in to list for example each project will show only once

For example project ABC  is repeated thrice and in Aug it has 3 values (100,200,300), should I keep in list as it is or is it  better to summarise it in list like ABC and in Aug as 600?  

But if I need to summarise there are plenty columns to summarise in list and don't know what is the best way to summarise in list too..

 

Sorry for asking:-)

Good for you for doing some pre-planning - it usually saves some work later on.

 

With a Excel sheets and tabs, you have a lot of flexibility on where you put things. As well, you attach formulas to individual cells. Power Apps (and databases in general) operate differently. In canvas Power Apps, the data don't contain formulas like in Excel. The formulas (functionality) are in the canvas app itself. In lists (or any tabular data source), data is organized into tables with columns. Columns (or fields) contain the data that applies to the subject of the table. All the rows (called records) are alike in data structure, meaning they can be described by the same sets of columns. Those records can have data added/deleted/updated in their columns as necessary to accomplish the goals of the app.

 

So, to answer your question, if you have budget data spread over many tabs by date, chances are that will work better if it is organized into a single list with some sort of column that describes the period or date applicable to that budget item. As to whether you should have three entries in August for a project or one, think about whether there is a need for users to have that kind of detail, or if they simply want to know the current or total budget amount.

 

For new users, my advice is frequently: if you are unsure if you should break something down into two lists, the answer is almost always yes if it makes your data easier to understand AND you can describe in words how the records on the first list relate to the items on the second list. New users sometimes fall into the trap of trying to cram everything into a single list with 60 or 100 columns, and that's a tough thing to fix. If you have to add more than 10 columns to a list, start asking yourself if you should have a second list.

 

This is pretty abstract stuff for now. Feel free to reach out again when you have some specific ideas to explore.

 

Bryan

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.
Sudhavi_84
Helper II
Helper II

Hi @BCLS776 ,

Thank you for your response. I am slowly slowly getting in to it. 

First I am trying to undesrstand the list and thinking to split as you adviced.

 

Firstly, Budget Excel Workbook Contains only one single Tab with Projects, Categories, Monthly Forecast and Yearly Forecast

Secondly August Excel Workbook contains only one Single Tab with Projects, Categories,Monthly (Actual Value and Forecast) and Yearly

Thirdly, Other 2 Workbooks Same as above for June and July.

 

So What I am thinking is to create one more list as simply Projects, Categories, Sub Categories etc as master table and give relation to all List, so that I can retrieve in to canvas app based on categories orders in projects and then get the Budget and August data in other 2 text boxes based on Categorie and project..

 

Is it something better to give relationship in List and go with Power APP?

 

and also we can expect every month new projects..usually we get in excel format, How can I update Master list table next month if new projects are added?

 

What do you suggest?

 

Thank you and your help is highly appreciated.

Hmm, I am not sure if I will address your question, but here is an example of how a couple of lists could interact and be queried within a Power App. Consider two lists and their associated columns:

 

ProjectList

ProjectName

ProjectID

Category

SubCategory

 

ProjectBudgets

ProjectID

BudgetMonth

BudgetAmount

 

ProjectList is designed to carry the details of projects, while ProjectBudgets contains info on the budgets themselves. Since the ProjectID column is common to both lists, there is a relationship between the tables. Queries you could do in Power Apps from these two lists include:

  • A breakdown of budgets by category
  • Find out the budget amounts allocated to a particular project in the months of June, July, & August
  • The name of the project with the highest and lowest budget amounts

    As you add in additional tables, you can create a relationship with those and do further queries & calculations.

     

    Does that help a bit more?

     

     

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Hi @BCLS776 ,

 

Thanks for your help on this so far. I am sorry for not making from my self clear but you did understand well.

 

ProjectBudgets

ProjectID

Year 2021

Jan 2021

Feb 2021

Year 2022

Jan 2022

Feb 2022

 

You see above fields for Project Budget, similarly we have same fields for June, July and August.

 

So what I am doing is I am creating one more list as Master list which contains as you specified below

 

ProjectList

ProjectName

ProjectID

Category

SubCategory

 

After I build this 5 List and I will bring into Power APP to compare. 

I want to bring  2021 Field from ProjectsBudget and 2021 Field from August In to App.

Example

Project  Aug 2021(Budget Table)  August 2021(August Table)  Variance  

ABC       5 M                                  6 M                                        1 M

 

              user need  to allocate variance in few categories like Timing, Covid Delay, Council Access

             So user will enter manually 1M variance in to 3 defined categories to explain in which category and where it came that much variance

 

             Covide Delay  Timing  Council Access

              .5 M                .3 M     .2 M

I want to capture above  Variance categories into list as well. Do you think it's better to autoupdate in Project list Table?

 

Secondly, after creating project list table, Is it better to create relationship like one(Project List) to many(Budget,June,July and August)? Do you advice me to do relationship first in List?

can we define relationship in Power APP? 

 

Thank you again @BCLS776  for your help:-)

Be careful about creating columns tied to months in a table. In my experience, this means adding columns over time, which makes the table grow with time. Each time a table changes, this can mean potential changes to any Power App using the table.

 

 

Instead, add a column that can contain the time period, which is why I suggested a BudgetMonth column in my example. Within that column, you can put in the info for the record that applies to the month involved. In this way, you will create as many rows in your table as necessary to record all of your entries without having to add columns. This is a departure from how you think about Excel and data but will serve you well as you build more apps.

 

A really good resource from one of our busiest community members, Warren, is available at this link. It is a tremendous resource for those who are new to Power Apps, so I strongly recommend you have a look.

 

Keep this principle in mind: Decide on your columns & tables on the front end of the app build. After that point, they should rarely need to change. Rows, however, change all the time as your data gets updated, added, deleted.

 

Bryan

 

_________________________________________________________________________________________
Help the community help more users by choosing to "Accept as Solution" if this post met your needs. If you liked the post and want to show some appreciation, please give it a Thumbs Up.

Helpful resources

Announcements

Announcing | Super Users - 2023 Season 1

Super Users – 2023 Season 1    We are excited to kick off the Power Users Super User Program for 2023 - Season 1.  The Power Platform Super Users have done an amazing job in keeping the Power Platform communities helpful, accurate and responsive. We would like to send these amazing folks a big THANK YOU for their efforts.      Super User Season 1 | Contributions July 1, 2022 – December 31, 2022  Super User Season 2 | Contributions January 1, 2023 – June 30, 2023    Curious what a Super User is? Super Users are especially active community members who are eager to help others with their community questions. There are 2 Super User seasons in a year, and we monitor the community for new potential Super Users at the end of each season. Super Users are recognized in the community with both a rank name and icon next to their username, and a seasonal badge on their profile.    Power Apps  Power Automate  Power Virtual Agents  Power Pages  Pstork1*  Pstork1*  Pstork1*  OliverRodrigues  BCBuizer  Expiscornovus*  Expiscornovus*  ragavanrajan  AhmedSalih  grantjenkins  renatoromao    Mira_Ghaly*  Mira_Ghaly*      Sundeep_Malik*  Sundeep_Malik*      SudeepGhatakNZ*  SudeepGhatakNZ*      StretchFredrik*  StretchFredrik*      365-Assist*  365-Assist*      cha_cha  ekarim2020      timl  Hardesh15      iAm_ManCat  annajhaveri      SebS  Rhiassuring      LaurensM  abm      TheRobRush  Ankesh_49      WiZey  lbendlin      Nogueira1306  Kaif_Siddique      victorcp  RobElliott      dpoggemann  srduval      SBax  CFernandes      Roverandom  schwibach      Akser  CraigStewart      PowerRanger  MichaelAnnis      subsguts  David_MA      EricRegnier  edgonzales      zmansuri  GeorgiosG      ChrisPiasecki  ryule      AmDev  fchopo      phipps0218  tom_riha      theapurva  takolota     Akash17  momlo     BCLS776  Shuvam-rpa     rampprakash  ScottShearer     Rusk  ChristianAbata     cchannon  Koen5     a33ik   Heartholme     AaronKnox        Matren        Alex_10        Jeff_Thorpe        poweractivate        Ramole        DianaBirkelbach        DavidZoon        AJ_Z        PriyankaGeethik        BrianS        StalinPonnusamy        HamidBee        CNT        Anonymous_Hippo        Anchov        KeithAtherton        alaabitar        Tolu_Victor        KRider        sperry1625        IPC_ahaas      zuurg    rubin_boer   cwebb365   Dorrinda   G1124   Gabibalaban   Manan-Malhotra   jcfDaniel   WarrenBelz   Waegemma      If an * is at the end of a user's name this means they are a Multi Super User, in more than one community. Please note this is not the final list, as we are pending a few acceptances.  Once they are received the list will be updated. 

Power Platform Connections - Episode 6 | March 23, 2023

Episode Six of Power Platform Connections sees David Warner and Hugo Bernier talk to talk to Business Applications MVP Shane Young, alongside the latest news, product updates, and community blogs.    Use the hashtag #PowerPlatformConnects on social media for a chance to have your work featured on the show!      Show schedule in this episode:  0:00 Cold Open 00:24 Show Intro 01:02 Shane Young Interview 22:00 Blogs & Articles 22:20 Integrate FullCalendar.io with Power Pages 23:50 Text Data 25:15 Zero to Hero Power Apps Saga 25:44 Parent Hub Association 26:33 Using Custom Values for OneNote Power Automate References​ 28:04 ​Dynamics Power Israel 28:44 Create Beautiful Canvas Apps in Dataverse for Teams ​ 30:36 Outro & Bloopers  Check out the blogs and articles featured in this week’s episode:   https://francomusso.com/integrate-fullcalendar-io-with-power-pages-from-json-basics-to-advanced-outp... @crmbizcoach https://yerawizardcat.com/text/ @YerAWizardCat www.fromzerotoheroes.com/mentorship ​ @thevictordantas https://www.expiscornovus.com/2023/03/16/parent-hub-association/ ​ @Expiscornovus https://lindsaytshelton.com/2023/03/15/the-painful-process-of-custom-values-for-onenote-power-automa... @lshelton_Tech https://never-stop-learning.de/create-beautiful-canvas-apps-in-dataverse-for-teams/ @MMe2K    Action requested: Feel free to provide feedback on how we can make our community more inclusive and diverse.  This episode premiered live on our YouTube at 12pm PST on Thursday 23rd March 2023.  Video series available at Power Platform Community YouTube channel.    Upcoming events:  Business Applications Launch – April 4th – Free and Virtual! M365 Conference - May 1-5th - Las Vegas Power Apps Developers Summit – May 19-20th - London European Power Platform conference – Jun. 20-22nd - Dublin Microsoft Power Platform Conference – Oct. 3-5th - Las Vegas  Join our Communities:  Power Apps Community Power Automate Community Power Virtual Agents Community Power Pages Community  If you’d like to hear from a specific community member in an upcoming recording and/or have specific questions for the Power Platform Connections team, please let us know. We will do our best to address all your requests or questions. 

Microsoft Power Platform Conference | Registration Open | Oct. 3-5 2023

We are so excited to see you for the Microsoft Power Platform Conference in Las Vegas October 3-5 2023! But first, let's take a look back at some fun moments and the best community in tech from MPPC 2022 in Orlando, Florida.   Featuring guest speakers such as Charles Lamanna, Heather Cook, Julie Strauss, Nirav Shah, Ryan Cunningham, Sangya Singh, Stephen Siciliano, Hugo Bernier and many more.   Register today: https://www.powerplatformconf.com/   

Check out the new Power Platform Communities Front Door Experience!

We are excited to share the ‘Power Platform Communities Front Door’ experience with you!   Front Door brings together content from all the Power Platform communities into a single place for our community members, customers and low-code, no-code enthusiasts to learn, share and engage with peers, advocates, community program managers and our product team members. There are a host of features and new capabilities now available on Power Platform Communities Front Door to make content more discoverable for all power product community users which includes ForumsUser GroupsEventsCommunity highlightsCommunity by numbersLinks to all communities Users can see top discussions from across all the Power Platform communities and easily navigate to the latest or trending posts for further interaction. Additionally, they can filter to individual products as well.       Users can filter and browse the user group events from all power platform products with feature parity to existing community user group experience and added filtering capabilities.     Users can now explore user groups on the Power Platform Front Door landing page with capability to view all products in Power Platform.    Explore Power Platform Communities Front Door today. Visit Power Platform Community Front door to easily navigate to the different product communities, view a roll up of user groups, events and forums.

Welcome to the Power Apps Community

Welcome! Congratulations on joining the Microsoft Power Apps community! You are now a part of a vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun! Now that you are a member, you can enjoy the following resources:   The Microsoft Power Apps Community Forums If you are looking for support with any part of Microsoft Power Apps, our forums are the place to go. They are titled "Get Help with Microsoft Power Apps " and there you will find thousands of technical professionals with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Apps community forums! Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered!   Microsoft Power Apps IdeasDo you have an idea to improve the Microsoft Power Apps experience, or a feature request for future product updates? Then the "Power Apps Ideas" section is where you can contribute your suggestions and vote for ideas posted by other community members. We constantly look to the most voted Ideas when planning updates, so your suggestions and votes will always make a difference.   Community Blog & NewsOver the years, more than 600 Power Apps Community Blog Articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on building Power Apps. On the Power Apps Community Blog, read the latest Power Apps related posts from our community blog authors around the world. Let us know if you would like to become an author and contribute your own writing — everything Power Apps related is welcome!   Power Apps Samples, Learning and Videos GalleriesOur galleries have a little bit of everything to do with Power Apps. Our galleries are great for finding inspiration for your next app or component. You can view, comment and kudo the apps and component gallery to see what others have created! Or share Power Apps that you have created with other Power Apps enthusiasts. Along with all of that awesome content, there is the Power Apps Community Video & MBAS gallery where you can watch tutorials and demos by Microsoft staff, partners, and community gurus in our community video gallery.   Again, we are excited to welcome you to the Microsoft Power Apps community family! Whether you are brand new to the world of process automation or you are a seasoned Power Apps veteran. Our goal is to shape the community to be your ‘go to’ for support, networking, education, inspiration and encouragement as we enjoy this adventure together!   Let us know in the Community Feedback if you have any questions or comments about your community experience.To learn more about the community and your account be sure to visit our Community Support Area boards to learn more! We look forward to seeing you in the Power Apps Community!The Power Apps Team

Microsoft Power Platform | March 2023 Newsletter

Welcome to our March 2023 Newsletter, where we'll be highlighting the great work of our members within our Biz Apps communities, alongside the latest news, video releases, and upcoming events. If you're new to the community, be sure to subscribe to the News & Announcements and stay up to date with the latest news from our ever-growing membership network who find real "Power in the Community".    LATEST NEWS Power Platform Connections Check out Episode Five of Power Platform Connections, as David Warner II and Hugo Bernier chat with #PowerAutomate Vice President, Stephen Siciliano, alongside reviewing out the great work of Vesa Juvonen, Waldek Mastykarz, Maximilian Müller, Kristine Kolodziejski, Danish Naglekar, Cat Schneider, Victor Dantas, and many more.   Use the hashtag #PowerPlatformConnects on social media for a chance to have your work featured on the show!   Did you miss an episode?  Catch up now in the Community Connections Galleries Power Apps, Power Automate, Power Virtual Agents, Power Pages     Power Platform leading a new era of AI-generated low-code development.   **HOT OFF THE PRESS** Fantastic piece here by Charles Lamanna on how we're reinventing software development with Copilot in Power Platform to help you can build apps, flows, and bots with just a simple description! Click here to see the Product Blog         Copilot for Power Apps - Power CAT Live To follow on from Charles' blog, check out #PowerCATLive as Phil Topness gives Clay Wesener Wesner a tour of the capabilities of Copilot in Power Apps.       UPCOMING EVENTS   Modern Workplace Conference Check out the Power Platform and Microsoft 365 Modern Workplace Conference that returns face-to-face at the Espace St Martin in Paris on 27-28th March. The #MWCP23 will feature a wide range of expert speakers, including Nadia Yahiaoui, Amanda Sterner, Pierre-Henri, Chirag Patel, Chris Hoard, Edyta Gorzoń, Erika Beaumier, Estelle Auberix, Femke Cornelissen, Frank POIREAU, Gaëlle Moreau, Gilles Pommier, Ilya Fainberg, Julie Ecolivet, Mai-Lynn Lien, Marijn Somers, Merethe Stave, Nikki Chapple, Patrick Guimonet, Penda Sow, Pieter Op De Beéck, Rémi Riche, Robin Doudoux, Stéphanie Delcroix, Yves Habersaat and many more.  Click here to find out more and register today!     Business Applications Launch 2023 Join us on Tuesday 4th April 2023 for an in-depth look into the latest updates across Microsoft Power Platform and Microsoft Dynamics 365 that are helping businesses overcome their biggest challenges today. Find out about new features, capabilities, and best practices for connecting data to deliver exceptional customer experiences, collaborating and creating using AI-powered capabilities, driving productivity with automation, and building future growth with today’s leading technology. Click Here to Register Today!       Power Platform Conference 2023 We are so excited to see you for the Microsoft Power Platform Conference in Las Vegas October 3-5th, 2023! But first, let's take a look below at some fun moments from MPPC 2022 in Orlando Florida. 2023 sees guest speakers such as Charles Lamanna, Heather Cook, Julie Strauss, Nirav Shah, Ryan Cunningham, Sangya Singh, and many more taking part, so why not click the link below to register for the #PowerPlatformConf today! Vegas, baby! Click Here to Register Today!      COMMUNITY HIGHLIGHTS Check out our top Super and Community Users reaching new levels!  These hardworking members are posting, answering questions, kudos, and providing top solutions in their communities.   Power Apps:  Super Users:  @WarrenBelz  |  @iAm_ManCat  Community Users: @LaurensM | @Rusk | @RJM07    Power Automate:   Super Users: @abm  | @Expiscornovus | @RobElliott  Community Users:  @grantjenkins | @Chriddle    Power Virtual Agents:   Super Users: @Expiscornovus | @Pstork1  Community Users: @MisterBates | @Jupyter123 | Kunal K   Power Pages: Super Users:  @OliverRodriguesOliverRodrigues | @Mira_Ghaly  Community Users: @FubarFubar | @ianwukianwuk  LATEST PRODUCT BLOG ARTICLES  Power Apps Community Blog  Power Automate Community Blog  Power Virtual Agents Community Blog  Power Pages Community Blog  Check out 'Using the Community' for more helpful tips and information:  Power Apps, Power Automate, Power Virtual Agents, Power Pages 

Top Solution Authors
Top Kudoed Authors
Users online (2,699)