How to Turn Your Excel Spreadsheets into Power Apps
Вставка
- Опубліковано 6 чер 2024
- #dataverse #powerapps #copilot
In this video I show you how you can get your spreadsheet into a Power App (and a Dataverse table) in just a couple of clicks with the new Copilot for Power Apps. For more complex "mission critical" spreadsheets with data relationships, I show you how you can go further by creating and relating multiple tables and importing your data into Dataverse and working with it in Power Apps.
0:00 - Moving from Excel to Power Apps
0:30 - Create Power Apps from Excel with Microsoft Copilot
5:50 - Testing and Editing the Canvas App
9:23 - Working with spreadsheets with multiple tables
11:30 - Configuring the Dataverse Contact table
13:40 - Working with Solutions
16:02 - Alternate Keys
17:20 - Create a Dataverse table from Excel
21:02 - Create a data relationship between tables
22:41 - Create a view and a form
25:39 - Create a model-driven app
27:03 - Import your data from Excel into Dataverse
31:48 - Update your Canvas App
32:28 - Learn more about model driven Power Apps
-------------------------
Connect with me:
☕ Buy me a coffee: www.buymeacoffee.com/lisacrosbie
🦉 Learn more about AI: aka.ms/learnwithlisa
🖇 LinkedIn: / lisa-crosbie
📼 TikTok: / lisa.crosbie
🐦 X (Twitter): / lisacrosbie
📚Take my LinkedIn Learning Course: Microsoft Power Platform Fundamentals (PL-900) Exam: Power Apps / microsoft-power-platfo... - Наука та технологія
Connect with me:
☕ Buy me a coffee: www.buymeacoffee.com/lisacrosbie
🦉 Learn more about AI: aka.ms/learnwithlisa
🖇 LinkedIn: www.linkedin.com/in/lisa-crosbie/
📼 TikTok: www.tiktok.com/@lisa.crosbie
🐦 X (Twitter): twitter.com/LisaCrosbie
📚Take my LinkedIn Learning Course: Microsoft Power Platform Fundamentals (PL-900) Exam: Power Apps www.linkedin.com/learning/microsoft-power-platform-fundamentals-pl-900-cert-prep-power-apps/
Thank you so much for this training. I've been trying to wrap my mind around this with more real life examples and this helped tremendously. Also, I love your teaching style and your ability to communicate clearly. Thank you for that. I'm looking forward to watching your longer video as soon as I have a chance.
Hi Lisa, I have checked multiple MVP's about this particular topic (turning an Excel sheet into PowerApps) and I would like to compliment you on your work. I could 't really understand and get this technique into practice AND understand every step and reasonings, but your extensive way of explaining this concept and every little step taken into account, really helped me to understand everything. You really stand out and I really appreciate it and like it that you take the time to help us understand. Just wanted to give you my sincere compliments I am so happy I dropped by your channel, because it helped me to reproduce it in my own environment AND understand the concept behind it. Thank you for helping us to get better! You rock! You made it look easy for me now (and I was struggling)!
You are wonderful for writing all that, thank you so much, genuinely appreciated and so happy I was able to help you.
You are a great presenter Lisa. I'm totally new to this universe so I have a lot of learning to do about use cases and how to do things properly. I appreciate your energy and style-thanks very much.
Thank you so much, very kind of you and I really appreciate that.
You teach so well and the excitement in your voice makes the learning experience interesting. Thank you.
Great video, Lisa! Thanks so much for sharing your knowledge.
Oh My. I've struggled for 2 weeks with this. Raised a request with Microsoft...told me that my import was wrong. Contacted old friends at Microsoft that showed me a convoluted method of exporting then merging then reimporting the data to get it to link. Your amazing because spending the time watching this free video resolved it ALL for me. Thank you so much! Learning from Lisa - This is the way!
Oh I love that you found the answer here, thank you for sharing that! 👏
You are just amazing Lisa. The way you present, speak and demo is just simply so easy to understand. 🤩
Appreciate that, thank you so much.
Amazing content getting to where I want to go with my data transformation but still have to watch more of your content. Thank you!
Thank you for the clear explanation Lisa, this was a great video to get some more insight on how this applications work in the back-end side. I work with CRM, and now I have a better idea on how some aspects of the application are developed. Thank you :)
Great explanation Lisa, this will be invaluable for me in my role. I have no hesitation in subscribing to your channel and look forward to exploring more of your presentations. You have a great presentation/training style that's easy to follow. Thank you.
Thanks so much 😀
Thank you so much for breaking this down for me!!!!
There's so much happening in the MS stack at the moment, thanks for all your work making it accessible to the rest of us.
So glad to hear it helps, I love doing it, and thank you.
When I'm searching for a topic I click on your video first. LOVE all your content.
Yay! Thank you!
Thank you so much for this video Lisa. I've done the self paced online training but got really stuck with importing choice columns and specifying the correct column for lookup. This is just what I needed! Really amazing 👏
Awesome, so glad to have helped!
@@LisaCrosbie would you know by any chance why the import from csv pop up would stop working please? I did a first import, when I tried again, the pop up just doesn't load anymore. I've tried on another table in the model driven app, deleted the app and created it again, reopened the microsoft edge browser, restarted the laptop. But I'm still getting the same issue. Any guidance would be lovely. I was so hoping to see everything come alive today! Thank you
This was me asking the question from the work laptop by the way... 😂
Awesome content! Just subscribed!
From one Functional to another; you explain this very well Lisa - whilst avoiding mentioning CRM or ALM explicitly. 😉 As an old timer, who is habitually used to reaching for the legacy CRM designer experience, this is really helpful.
Love the idea of having the choice of a canvas or a model-driven app based on the same tables in Dataverse. Incidentally, for readers of Lisa's comments: These apps can be consumed via a web page, a mobile device or a power apps SharePoint web part.
Thanks so much, appreciate that. I’m always trying to educate to reach people who don’t necessarily know the tech jargon (as a career changer myself). It’s so hard to switch at first from the legacy interface but now when I go back I hate it!
Cool
Hello Lisa, Thank you for such a nice tutorial. I had no experience with Powerapps but i have completed a vulnerability tracking app assigned by my organization. Love your work and really really appreciate.
Awesome, thank you and well done. 👏
Very helpful. thank you.
thanks, great explainer/explanation
Thanks for the intro...looking to move away from MS Access so this gave me some hope :)
Excellent, you're in the right place, best of luck.You might also like to look at the tools available to migrate directly from Access to Dataverse support.microsoft.com/en-au/office/get-started-migrate-access-data-to-dataverse-013c8bab-7737-46ca-ad2e-892bbf26287d
great work thanks!
Love the Lego collection in the background!
Yeah it’s pretty cool isn’t it 😎
I'm a big fan of your YT courses. You explain them so well..!!
One query - I have an excel with more than 200K data rows. And when followed your process to convert the excel to app, I'm not getting all the data. Is their any data/size restriction?
If yes, what is the best way to import such a big file to the app and update the same frequently (weekly)??
You are lovely ! thanks
This was very helpful and informative. This helps me a lot since I have come from a world of Microsoft Access and InfoPath. I would be interested in seeing how to apply a multi-tiered membership database to these scenarios.
Oh, that's a good scenario, thanks, I'll add it to the list. Post here if you have more details about what that includes?
Thank you for the video. After many errors, I think I have a rough understanding of the process. I am confused about how to send this to my team for them to use the new app.
Hi , I liked watching your video and wanted to share a use case with you where user would like excel like experience in the app when they are editing the records that is bulk edit of multiple records in one screen instead of going into individual form and editing each record one by one .
Hi, thank you for your help on this! I am (trying) to build a workbook that will help small business owners. Once completed, I intend to sell it, but it seems I have no clue on how to correctly protect that workbook, as there are many apps that will crack the code! Would you have any advice on that? Thank you
I left the corporate world 10 years ago so apologies if I’m missing something. This just seems like an over the top way of making an Access database.
Dataverse is a relational database, and in some ways it's the modern equivalent - the tool business users can turn to (instead of a spreadsheet) to create a proper database, but it's so much more than Access - cloud based, flexible form design, secure to row level security, scalable to terrabytes of data, can drive entire end to end business processes and logic, sophisticated search and reporting and the list goes on. This question comes up a lot, I might need to do a comparison video at some point!
Great presentation Lisa as always! Is it necessary to have Power Apps licence plan or you can use it with a simple business premium or basic licence?
Thanks. You do need a full Power Apps license to do this because it uses Dataverse.
If you're importing a static CSV does that not mean that you will just end up back in the spreadsheets every time you need to add a new customer? Would it not just be better to link this to a system such as Dynamics presumably allowing it to update live?
Apologies for the ignorance this is only the second video I have seen on PowerApps so I am brand new to this whole thing. Up until a week ago Excel was as much as I knew (no Access, Power BI, Automate or Apps but i'm trying to learn fast so I can apply this to the real world).
Ps. Thank you for all of the knowledge you provide. Trainers like you will help make the world a more efficient place to live in, so more time for that beverage of choice you referred to! Subscribed :)
Got inspired, so thanks for a great explanation. I have a business critical excel file to keep an eye on Patent renewal and associated cost - Those patents are often liked to Account projects / Opportunities descried in Microsoft Dynamics. Any tutorials on how to link an Excel and Dynamics info into a more useful app?
I would suggest getting away from excel, create a related table in your Dynamics 365 environment, and then import from the spreadsheet as shown in the second part of this video. From there just do everything in Dynamics 365. The only way you can "link" Dynamics 365 and Excel is by creating a canvas app with 2 data sources.
Great presentation. Are there any elements of what you have shown here that you don't get with Dataverse for Teams? (=included in E3 license)
You need full Power Apps license for everything shown here because it builds on Dataverse.
I want to use an app like this for my insurance business. Currently I use a worksheet with several different tabs- client details, home details, auto details, etc. I also have a sheet that lists all of the client information with a header and one row because I knew eventually I was going to need to step up my game. After watching this, it would seem that instead of trying to import a csv with over 70 columns (or data fields if you will), I need to break out the related information into their own separate csv files, right? If so, what would be the process of adding a new client's information? Start at the contact tab and create button links for the other tables? Is something like that detailed in your longer video?
Great video Lisa! I created the excel files and followed it all the way through. I ran into a problem when I tried to remove the Customer ID field from the Contract Renewal table. It said I couldn't because it had dependencies. Fair enough. I then tried to delete the dependencies, but these were system created Views, and the system won't allow me to delete them. Any ideas?
I have a tracker with program descriptions in paragraphs and other smaller details of the program like date, budget, location etc. Can i upload it into power apps? Also, should everyone in an organization have access to powerapps to view it?
super
Great video, but... when you bring your table created from the Excel into the solution, it still has the default solutions prefix (cr36c_), is it possible to use the Excel file to create the table within the solution so it gets your publishers prefix (lc_ in your case) or can you update the publisher some other way. See 20:50 of the video.
Hi Lisa, Thank you for this detailed and concise tutorial. Love the shortcut feature to create an Dataverse table from an Excel file. However is there a way to specify a publisher so that the column prefixes can be predefined like "lc_" instead of arbitrary?
There is a new features which has just been added where you can do that now (wasn’t available when I made this video) , there is a fairly big button at the top of the screen that says “set preferred solution ”. learn.microsoft.com/en-us/power-apps/maker/data-platform/preferred-solution
Amazing! Thank you so much for the instant response! Very helpful and Greetings from Miami👍
What app would I use if I want to consistently analyze excel reports that have changing values monthly?
is there a way to add multiple filters for the user to use so they can better find customers rather than just search? State, zip code, type.
Hi Lisa, nice to see an Aussie doing a presentation. I have a quick question. When I load my spreadsheet it does not recognize my columns as choice, my column has a lot of entries in the dropdown list, to many to add individually. Is there a fix or work around please?
Thanks! That's a new one, I haven't heard about that before -how many entries are there in your dropdown list? (I'll see what I can find out)
@@LisaCrosbie I was trying to modify your demo as a training matrix for our company so I have table that list courses, if they are optional and who has completed them, so in the table I had dropdown lists so the numer of courses where 242.
thanks Lisa, watching many of these and a udemy and pl100 scheduled soon pl900 done last week. unsure i can do in the work place but ill do the odd app for my company. never know on the future. it all helps
Hi Lisa, First I need to thank you very much for sharing ample of knowledge on dynamic platform. I have one query, can we upload multiple excels with same columns and create canvas app? Early waiting for reply🙄
If you are uploading multiple Excel sheets to dataverse then yes, you can do that as shown here, and use the canvas app connected to that. If you are just building a canvas app without dataverse, you can connect to multiple spreadsheets but if they all have the same columns that won't make much sense and you'd be better to combine them into a single spreadsheet.
@@LisaCrosbie Thanks for reply.
Great - now how do we deploy and share these apps??
I'm new to this, and I'm trying to learn how to create an app to a couple of specific things with my work. It seems that PowerApp creation is like the second (or third) generation from the old Access database. Am I correct? The terminology (keys, relationships, tables etc) are all very similar and even though I'm new to it, it is chiming with me from years and years ago trying to get to grips with Access
Yep, absolutely spot on 👍
Excellent presentation.
Lisa… nice video… but can we make apps in microsoft teams, where my team member can input data, using form, store data in excel (only the owner has access to this), but all of member can look at the data. And edit if necesarry. Thank you
Yes, you can build a Power App (canvas app) on top of Excel as a data source and then embed that app in Teams. Very different from what's shown here though, this is about moving away from Excel into Dataverse for your data storage.
That´s a great new feature. Do you know, when this availble for europe/germany. Don´t find any information about.
There are no announced release dates at this stage unfortunately, it’s just public preview in US environments
Great video Lisa, thank you. My issue with the "Start with data" approach is that there does not seem to be a way to control the publisher prefix of the tables that get created - the Default publisher is used. See an example at 20:45 in your video, it is using the cr36c_ prefix. Do you know any way to control the publisher of the object, as when creating a table manually in a solution?
There seems to be quite a difference between the new build experiences and what happens when you go via a solution - I’m going to see what I can find out!
@@LisaCrosbie Thanks Lisa, appreciated
@@LisaCrosbie Hey Lisa....answering my own question here. The new 'Preferred Solution' feature provides the answer here. If you set your preferred solution, that sets the preferred publisher, and when you do things like create tables via Excel import, the prefix is now correct. They got there in the end!
Hi Lisa, thanks for the video. How much more complicated is this process if it is scaled up? For example, if there were more tables with more complicated database design? I'm a little confused on how I would use the logic here on 11 related tables. Should I create all the empty tables with the alternate keys and then upload the csv filed in hierarchy?
Honestly if I had 11 tables I wouldn’t be starting with this Copilot, as you can see it’s really designed to create a single table with an app on top. I have another videos here which goes through designing a data model which might help. I plan to make more on this. How to Design and Build a Data Model in Microsoft Dataverse
ua-cam.com/video/iujZ0qplapM/v-deo.html
@@LisaCrosbie Hi, thanks so much
I want to learn this any guard on how to play with it on my own? Things like to setup account and follow the instructions. Now only watch the video like I am watching Netflix
Would you recommend moving from Access to Dataverse? If so, how are keys and foreign keys maintained.
Yes, moving from Access to Dataverse is a great way to modernise your apps and give you a heap more flexibility and functionality. When you create a dataverse table it comes with a primary key (name) field, and you use lookups to create relationships between tables. This might help support.microsoft.com/en-us/office/get-started-migrate-access-data-to-dataverse-013c8bab-7737-46ca-ad2e-892bbf26287d
Hi Lisa, thanks for this video.
I tried following these steps, however there are a few columns in my excel file which I want to use as Choices or Dropdowns. However, Dataverse is not recognizing those columns as Choices. How can I resolve this?
This is still preview functionality so everything may not be perfect just yet. You can edit the column type in that first screen when it comes up, to change it into a Choice column and then add the dropdown list of options. Do you have a very long list of options (someone else reported it didn't work for them with a long list)
@@LisaCrosbie Thank you so much for responding. Yes, I do have a couple of columns which has a huge no. of options. I was able to edit the types of the columns with fewer options in the first screen. Not sure how to handle the big ones.
Do I need to establish a relationship between Contact Table and Contract Renewal table?
Yes, as shown in this video - creating the lookup created the relationship at the same time.
hey Lisa how can one insert images in that excel colums and get them appear in powerapps
You’d need the images to be online and then have the image link in the excel spreadsheet
Hi Lisa. The + New Column is greyed out when trying to add a new column to the imported Contact table. There is a warning stating "One or more commands are unavailable due to your current privileges for this environment.". How do I add privileges to allow me to add a column?
You will need to have the System Customizer security role applied to you.
I've followed along as exactly as I can, but in the final result, I am getting the customer ID showing up linked instead of the customer name. Am I making a common mistake? Is it because I have Customer ID as the primary column for my Contact table? Wondering if there's a quick fix or if you would mind creating a video explaining why this is happening. Thanks for the video, Lisa!
Figured it out! it was the primary column tripping me up.
Hey Lisa, is it possible to monetize a power app I have created? I want to sell it on a subscription basis. I am finding all kinds of information which is quite honestly confusing. Hope you could help.
You can sell through app source, or else it's just a commercial arrangement you set up as a consultant, where you deploy your app and bill customers on a monthly basis for your app. appsource.microsoft.com/en-US/
At 31:04, you have that Related Tab which is a like a Menu with some Items and when you click on it you get some options, and if you click on one of these options, you kinda get an extra tab.
I am trying an App and in that related tab, I have "Software and Updates" which are related to "Device". One could always click on "Related" and then on the particular menu item to open the new tab; however, I would like to avoid these extra 2 steps and make the related tab of installed "Software and Updates" to be shown by default, besides that "General" tab. Is it possible?
If not, is it possible to add the contents of the mentioned related tab on the right side of the form in the General tab? This way I would have the Device entry on the left, and the list of software installed on it on the right -> both on the same page and that would be amazing haha
I notice that the "Software and Updates" is a shown as a _relationship_ [between the "Device" and "Software and Update" Tables, linked by "Equipment Number" that I defined as key]; however, I don't know how to add that to my form - I only see other options like 1-column tab, or add a sections, and so on.
Thanks in advance.
Yes it’s absolutely possible. When you configure the form in your app, add a tab and then add a subgrid to that tab to show items from the related table. My tutorial here and I also show it in my main model driven apps tutorials Power Apps Model-Driven Apps: Subgrids and Quick Create Forms Tutorial
ua-cam.com/video/baRLW8bak9Q/v-deo.html
@@LisaCrosbie, I kinda whispered "OMG. I love you" in my thoughts😅
I don't mean any disrespect, just like "Wow", in a friendly way haha.
I will watch the video you sent here, and I am also in the middle of the big, full tutorial haha; I will definitely make sure I pay attention to this _Subgrid_ Part and see how I can apply that to my App.
Please. Never stop providing such high-quality content for free. We all here love you so much for that.
Be well.
Hi Lisa, how did you get the file Contact.csv? Is it the same predefined Contact table in Dataverse with sample data?
I tempted to "Export data" from the Contact table view inside the solution but I was showing this error message: "The export service is not available: Connection set initialization failed for project DMExport_2023_09_04T16_37_42_460Z. Error: Tried creating PQ Service principal but still getting PQ Service principal retrieval failed".
Hi - all i did was create a blank spreadsheet and just fill in some demo data. (I used chatgpt to help make that quicker!). Nothing fancy.
If you export from Dataverse you will get a heap of other ID columns that link to the records in dataverse. After you export, unhide columns A-C in your spreadsheet, and delete them. Then save as csv. Or else copy the columns you can see, paste as text into a new spreadsheet, and save that.
I've also uploaded it here github.com/lisamcrosbie/excel
is the app created a canvas app or model-driven app? Thanks.
It creates a Dataverse table with a canvas app.
LEGO!!!❤
Yeah 😎
Can I get the data set used in this video ?
github.com/lisamcrosbie/excel
Tried to insert my Excel, but it is a bit harder than I thought. It does not recognize the time format and it just chooses a column as a primary key which I do not want. A bit annoying.
AMerica....we got the dates right!
On behalf of the rest of the world - sorry, no! :-)
How do I use a new Excel file to replace data?
If you want to change or update data already in the app then you can do it with the edit in Excel online option (in the export to excel menu) or else export to excel, make your changes and reimport your data. Be careful when you export that you keep the 3 hidden columns on the left lined up with the right rows.
Off topic but I prefer your accent over my US accent. :)
Haha thanks!!
Can anyone show me how to export the table Contact and save it as .csv?
If you export from Dataverse you will get a heap of other ID columns that link to the records in dataverse. After you export, unhide columns A-C in your spreadsheet, and delete them. Then save as csv. Or else copy the columns you can see, paste as text into a new spreadsheet, and save that.
I created 8 tables. Then I established relationships. Ok so far. Then I tried to convert some of the columns into lookup options. The columns didn't let me change them.😢
You’re right, I’m afraid once the data type is set that’s it. Unfortunately the create from Excel doesn’t create lookups yet. 🥲
I have a question you can't answer. How did you become a genius? I'm serious. I've used excel all my life atleast 30 years but I'm not even a tenth of your skills and other geniuses. ❤❤❤❤❤
You're right, I can't answer that. I'm really not a genius, but I do love learning and working out how to deconstruct and explain things to make them sound simple. Thanks for making me smile!
So far so good. My Excel table is similar to yours except for one of the columns. One of the columns I need to show a calculation. Let's say your Column K was not an input. Rather, it was a number equal to Phone x Type x Category. (I know, those are text fields for you, but they are numeric for me). How can I put in each cell of Column K an equation like: E2 * H2 * I2 for cell K2. E3 * H3 * I3 for cell K3 and so on. If you answer this, will you post it here or do I need to give you my email address?
Putting an equation in Excel won't move that equation over to the Dataverse column, so you have two options - either copy and paste values in your Excel spreadsheet so that when the column is created in Dataverse it just brings across those values. Otherwise if you need your table in Dataverse to do the ongoing calculations, then leave that column out at this stage, and create a new column of type Formula in the Dataverse table that gets created, and set up that formula using PowerFx.
How is this difficult from MS Access ?
Dataverse is a could based relational database that will scale to TB data and enable complex security and business logic. Then you can use Power Apps to create whatever user experiences you want (from forms over data through to custom low code mobile apps)
How to create Home Expenses App in Power App
I'm going to use powerapps when it become a free or at least it should be included in MS office solution.
There is a lot of Power Apps capability included in M365 but if you want to work with an enterprise database system (which is where Dataverse fills in) that’s always going to be a different cost and value outside that productively suite.
I thought this was going to be about scripting with VBA in Excel, turning Excel into a mini-program.
Nope, entirely low code click and point. 😎
Cyber security engineers worst nightmare. This is why data governance is getting harder and harder as self service tools become more accesible.
If you have 1 customer who is in Europe in that table, you are now subject to GDPR compliance rules and if you aren't protecting or storing Personally Identifiable Information (PPI) correctly that is a whole new bunch of ways to be sued.
I love that these tools are getting easier, but we need to ensure that proper protections are in place, even when its only accesible inside of your company network.
This is about storing data in Microsoft Dataverse instead of Excel, and making it easier to make that switch to a more secure data storage - particularly important for PII data.
Holy winding road batman!!!!! Are you saying power apps lets you "create a 1:N relationship" between tables with a lookup field.... and that lookup field only defines which tables are related and what type of relationship but not which fields create the relationship..... and power apps hides the process of how to select which specific fields are used to join the tables, except in this insane workaround? Great video, but this makes no sense. There is no other way?
You can just go in and say - create relationship - the relationship is always connected to the Primary Name field. What I'm showing here is in the context of showing that the Excel single table upload doesn't do that.
Hey, does it only works with first 20 rows? What if we want to add large data?