Hello, Great Video. One thing to add: performance. I measured the time of writiing a single row of 3 data to Excel (There is great feature for performance analises - Monitor in PowerApps) . It usually takes 2-3 seconds to write a single record to Excel. In case of Dataverse for Teams it was only 200 miliseconds - Dataverse was about 10 to 15 times faster.
@ShanesCows3 жыл бұрын
Thanks for the info! I forgot about performance but you are so right! Good job.
@ostzonedeluxe57333 жыл бұрын
Thanks a lot shane! So many people don‘t understand what excel really is - is it everything but not a datasource! I love your videos. I‘ve created so much apps with powerapps because or your videos!
@ShanesCows3 жыл бұрын
Thanks. I agree!
@cpyong273 жыл бұрын
I experienced first hand issues myself using Excel as my data source. Thanks Shane for confirming Excel is a bad data source for production. Couldn't agree more.
@ShanesCows3 жыл бұрын
😎
@vusimahlangu9154 Жыл бұрын
Man, I have not finished watching your video and I am loving it.
@ShanesCows Жыл бұрын
Glad to hear it!
@canasman113 жыл бұрын
I have been developing Power Apps for the last couple of years and never ever used Excel as a data source, exactly for the reasons you mention in this video. I have apps connected to Sharepoint lists and SQL Server only. I'm considering to start building apps in Teams with dataverse as it looks very promising as well. Thanks for your videos Shane, I have learnt lots from you.
@ShanesCows3 жыл бұрын
Awesome Manny!
@TyRey3 жыл бұрын
I'm in the process of converting from Excel to SharePoint for my data source. It's mind-numbing and tedious but its underway. I'm getting a TON of delegation errors but working through each one. Thanks again for the advice.
@ShanesCows3 жыл бұрын
Awesome Tyler! It will be a pain but you will be much better off for it when you are done. 🐶
@issiewizzie3 жыл бұрын
limitations apply to Sharepoint as well ... especially when dealing with large list
@matthewv41702 жыл бұрын
@@issiewizzie you have to filter the data first. Or you can distinct collect using a dim table to collect it all up
@jogchumvandenberg13173 жыл бұрын
Used to have excel as a read only data source for an app. The file needed to be updated daily by employees and excel is familiar to them. It all worked beautifully. Then there was a need for added functionality to the app, which was possible but required writing back to the excel file. Trouble. My solution was to have employees keep their process and have them update the excel file. A flow then triggers which updates a designated sharepoint list. The app then talks to sharepoint instead of excel. Employees happy because they get to keep working with excel. Power apps happy because it gets to work with sharepoint. I guess the lesson is that even though excel as a read-only datasource works for the moment doesn't mean that it will not be a limitation in the future.
@ShanesCows3 жыл бұрын
PERFECT!!!! Good job. 🐶
@enzym200410 ай бұрын
Another very useful video. Totally agree with you that Excel has big limitations. It is, like you say, great for learning Power Apps, but you run into issues if you use it for production apps. Could have saved me some heavy googling by watching this video first ;) Keep up the good work!
@ShanesCows10 ай бұрын
Glad it was helpful! Excel is a TRAP for sure.
@franknielsen32193 жыл бұрын
102% of my apps uses SharePoint as a data source, only because I grew up with it, back in the SharePoint/InfoPath 2003 days. Lucky for me that I have never learned to use Excel properly :) THX for the tips......... and have a great day
@ShanesCows3 жыл бұрын
Awesome and thanks Frank.
@nishankbajaj32603 жыл бұрын
Thanks a lot Shane. I started with excel 6 months back. And am a pro SharePoint list person now 😀
@ShanesCows3 жыл бұрын
Fantastic!
@altino912552683 жыл бұрын
Great video! Very nice ! Main reasons for not using Excel as a data source: 1st Excel cannot be hosted in SharePoint, it must be in OneDrive Business. 2nd The tables that you intend to bring to Power Apps need is formatted as Tables in excel. 3rd When sending data from Power Apps to Excel you cannot have it open. 4th If you work with a lot of lines in your data source you will have problems with delegation. #Brazil
@ShanesCows3 жыл бұрын
Glad you enjoyed it!
@deivisonvicente88693 жыл бұрын
Altino, o Excel seria tão ruim assim como database? Conclui um projeto recente nele e não vi algo tão negativo
@altino912552683 жыл бұрын
@@deivisonvicente8869 Fala amigo, tudo bem ? O excel para DataBase tem mais pontos negativos do que positivo... Dependo a aplicação você vai encontrar problemas...(GRANDES) 1º Exemplo: Vc fez uma aplicação que envia dados para o Excel... Caso a planilha esteja aberto vc "perdeu" aqueles dados...Já no SharePoint não... Com o próprio Shane comentou o excel como database para apps de inicio é bacana, após isso nãoooooo. Outra coisa, geralmente fazemos aplicações "inicias" e no futuro se você precisar expandir / usar funcionalidade + Avançadas será que o Excel vai te dá suporte ? Qualquer coisa dá um grito.
@altino912552683 жыл бұрын
@@deivisonvicente8869 Me adiciona no LinkedIn (Altino Fernandes) que vou te enviar uma documentação para sua leitura.
@carlosdelamo56223 жыл бұрын
Hi Shane, very nice to hear that after some videos working with excel. I think this is the reason why my app didnt work but I didnt know. I will see how easy is to send my excel file to share point. to be honest I hear something abt that in another chanel but I was so faithful to you....
@ShanesCows3 жыл бұрын
Glad it helped!
@moisestatis5418 Жыл бұрын
Shane thanks so much for sharing this video. Great tips. It breaks my heart as a beleiver of Excel but I got the point. I understand that Sharepoint also has limitations? I'see if you have a video about it. Also, I checked tour webiste to learn more. Thanks again.
@ShanesCows Жыл бұрын
DataVerse is best but SharePoint is a very capable “free” datasource.
@joseguzman58753 жыл бұрын
Great Video Shane! I have been watching your videos for a long time. Keep up the great work!
@ShanesCows3 жыл бұрын
Thanks! Will do!
@kearley87922 жыл бұрын
@Shane Young While watching this video, I was reminded of an issue that comes up over and over to me and the answer I keep running into in the wild is to simply use concatenate, which seems more like a hack to the solution...let me explain. Often I am trying t get a "value" from some data structure or control that is naturally wrapped up in a record or table. I don't know how in PowerApps to simply get one field "value" out of that structure. Everyone I have asked says, "oh, just use concatenate". That solution doesn't digest well with me, because that's not what concatenate is for. My instinct is that MS has provided a way to get a single field or value from a record or table. For me, it's often a single row "record", so specifying which row is not necessary wither. Can you please help me with this? I am struggling every day to do this the "right way".
@FernandoJSantesteban3 жыл бұрын
Thank you, Shane! I learn a lot from your videos.
@ShanesCows3 жыл бұрын
Glad to help.
@anthonyleduc2 жыл бұрын
Never never use excel as a datasource. I'm 100% agree with you
@ShanesCows2 жыл бұрын
🐶😍
@tomaskavicky2 жыл бұрын
had few weeks ago a P-App talk with other Dept. and I was actually pretty much ''quoting you'' to avoid excel as source :D (.....tips from video helped), thanks
@ShanesCows2 жыл бұрын
Good stuff!
@ralphcabrera60282 жыл бұрын
Hi Shane, thanks again for this wonderful video. I was hoping you could help me if you have a video that shows how to attach and upload excel in power apps, save the rows of data in a collection, put the collection in a gallery (or others) for editing then have a button to patch it to sharepoint list.
@ShanesCows2 жыл бұрын
Hey Ralph I don' t have a video that shows that. This video does talk about uploading Excel and getting the data out, might be a start for you kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html
@patrickfillion71933 жыл бұрын
I was listening to a podcast and one problem they had in the UK with tracking the Covid cases, the automation they used was saving to a XLS instead of the XLSx file. The difference was the number of lines that can be saved... oops, they had lost several names for tracking who got infected.
@ShanesCows3 жыл бұрын
That is crazy!
@tomdoe12343 жыл бұрын
Funny my use case is the one case you said is okay :). I need a drop list of employees that I will update from SAP once a month. Employee ID and name that's it. If I were to make it into sharepoint list that would be an extra step. I already have an excel add in that cleans the sap data and creates a table. I'll just be overwriting the file each month.
@ShanesCows3 жыл бұрын
Cool. There are no absolutes. 😎
@dmdas19063 жыл бұрын
Thanks Shane for all the details you give in your videos. The way you present is so easy for beginners like me to understand. I am having one more issue with my Excel Database. The Table has a VB code for RefeshAll as soon as the main datasheet is updated. The datasheet is a list of documents with a last and next annual review date and a column with days remaining for the review. This works well but as soon as PowerApps connects with the table and creates its own ID column there, the automatic refreshing of data from the main sheet in the table stops. Is there a way around this? Please assist when you find the opportunity. Thanks.
@ShanesCows3 жыл бұрын
Sorry I haven’t done VB in Excel in 15 years. 🤨 Not sure how it factors in.
@moisestatis5418 Жыл бұрын
I have Excel file with fórmulas and with vba as data source, but before I make a "NEW" clean table using power query that extracts the data needed without fórmulas and vba. This NEW table is the datasource for power apps.
@rivernet623 жыл бұрын
Ahhhhhh! I made a comment a couple of weeks ago on one of your videos about this exact thing. Should my ears be burning? Hilarious though: In 10 minutes you have recapitulated my entire PowerApps database experience of the past year. When I started with PowerApps I'd never heard of SharePoint (now "Lists"), so Excel it was. So guys: Everything Shane says here (and probably elsewhere) is true. Take the effort to start out with something other than Excel. You'll just have to switch over later.
@ShanesCows3 жыл бұрын
Mark not sure it was you that triggered me but not sure you aren't either. 🤪
@rivernet623 жыл бұрын
@@ShanesCows I get that a lot :(
@gustavobaeza38903 жыл бұрын
Thanks for changing the color of your wall.. it was dark and ugly..now is cool and warm...
@ShanesCows3 жыл бұрын
It has been a journey trying to make it better. Glad you like it. 😍
@sebastiananello52553 жыл бұрын
Hi Shane thanks a lot for sharing all these knowledge with us. You are the best11
@ShanesCows3 жыл бұрын
Thanks 🤩
@guycarnegie7411 Жыл бұрын
Great video. Our team use a network shared Excel workbook to store data - not in onedrive, sharepoint or anywhere like that - just a networked shared folder - which I know is a bad thing but I'm not about to change that yet. It has about 70k rows in it. I have a list form that needs to "lookup" from it, based on list form user input, so I guess I'm going to need to do some VBA in the excel file to send/sync the data to another source which can serve as an intermediate data source for the Powerapps List form. What would you recommend to use as an intermediate data source that excel can write to, and Powerapps can read from - without suffering from delegation issues? I guess that could also serve as a "back-end" to the existing workbook when we decide to start migrating over properly.
@CGRB2 жыл бұрын
Thank you Shane
@ShanesCows2 жыл бұрын
You are welcome Brian.
@chrisyleenz2 жыл бұрын
I love Excel, it's basic, amazing, and powerful, like driving a manual car. However, since I've been following you on powerapp, my life has been taken to a new dimension, can never go back... Excel still offers that total freedom nevertheless, but the ability to do things with powerapp and sharepoint list is just too much that Excel could not compare
@ShanesCows2 жыл бұрын
Well said. 😊
@matthewknobel69543 ай бұрын
My issue is that our source data is locked up in SAP and we can not use premium connectors. We have to extract large number of rows into excel (10k to 100k+) to do work. How do I get this data from the excel extract into the likes to SP. SQL is also out of the question for the same reason as SAP.
@ShanesCows3 ай бұрын
Yuck. Your are going to struggle, sorry. This old video will hel you go from Excel to a SharePoint list. kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html But then you are going to have challenges with a SP List with 100k+ Items. :(
@sagarkavishwar70713 жыл бұрын
Thanks Shane, This very true and limitation to use Excel as data source, What can be the solution? Currently I have app with excel as data source and having more than 30 Columns and rows are increasing day by day..
@ShanesCows3 жыл бұрын
Use SharePoint is your next best standard option.
@russellniebolt14933 жыл бұрын
Hey Shane, great video. However......I imported a 777k-sized Excel into a Sharepoint list. Very basic text strings, 5 columns, no formulas, nada. Made it a SP data source. For whatever reason, I ran into the delegation errors just like Excel. I'm taking the delegation errors on 'Filter(ExcelTab1, "JoeBlow1" in Userid)'. Are the Sharepoint servers I'm working with.......possibly not tuned correctly for PowerApps, etc?
@danher46073 жыл бұрын
Dear Shane, thank you very much for your excellent advice (sorry for my writing, I'm bad at English). I would very much appreciate the answer to the following question: What would be the options to connect to a Power BI Dataset?
@ShanesCows3 жыл бұрын
There is not one that I know of. 😔 Sorry. And your English is great 😀
@838cheng3 жыл бұрын
Thanks Shane. I am a beginner in PowerApps and have learned a lot from your videos. You said SharePoint, did you mean SharePoint list ? I heard from someone that speed performance is an issue when SharePoint list has over 2000 records. Is that true? Also can PowerApps read Excel in SharePoint?
@ShanesCows3 жыл бұрын
SharePoint lists do get slower the more data they get in them. But they are still always better than Excel. No, you cannot host an Excel file in SharePoint the same way you do in OneDrive.
@ImranKhan-oj6st2 жыл бұрын
Your videos are very fruitful. Learnt a lot from it. Great going. I have a question. I am building a app with read only data. There will be no patch by the app users (around 100 users). I get the data quarterly from another department in csv file so it’s easy for me to update on excel file in OneDrive. My question for read only data in Powerapps is excel Recommended?
@danielam.guerron35762 жыл бұрын
Hi Shane I really love your videos. I have learned a lot thanks to you. I have a question about Excel Data Source. I have to use three Excel files with three tables (One of them has almost 900 items). They going to be read-only. I put all data in three different SharePoint lists but I’m pretty sure that this data will change at least 2 times a month. So what could I do? I was thinking let Excel files on Onedrive and overwriting every time but I dont know if this could be a problem.
@joaoluismartins Жыл бұрын
Hello Shane! Great video! In your opinion, what is the best Data Source for Power Apss when dealing with big tables (+1M rows) ? Thanks a lot, Cheers, João Martins
@ShanesCows Жыл бұрын
Dataverse hands down is the best data source. 😊 It is the fastest and most feature rich, the only downside is it requires a premium license. I actually have a video coming sooner than later that will talk about this. 😊
@joaoluismartins Жыл бұрын
Cool! Thanks for the answer! I was thinking that maybe a Azure SQL Databse would handle better the Power Apps integration, speacially if the idea is to use the data geneated on Power Apps on other places, like Power BI.
@PowerAutomator Жыл бұрын
It's super unstable when connecting to excel. Even less than 500 records, I am having issues the records are not removed when I use the remove function.
@ShanesCows Жыл бұрын
Terrible Data Source 🤩
@lornastoddard3 жыл бұрын
Thanks a lot Shane, I've learned so much from your videos they helped so much. I understand your recommendation to use a SharePoint list, however I have data over 5k rows. How would you recommend overcoming that limitation?
@ShanesCows3 жыл бұрын
There is no 5000 item limit in SharePoint. I have built an app with 33,000 records. It was slow but it works. If you really want scale use Dataverse but it requires a premium license.
@espeolguin3 жыл бұрын
Hello Shane, again, I love your videos!! I need some help to keep the functionality/relationship of several worksheets that are connected and have macros. I can transfer every worksheet but I also need to reconnect them to keep the functionality of the macros. I hope this makes sense. Thank you Espie
@McIlravyInc Жыл бұрын
As of now (2023) the current connector does allow connecting to SharePoint files and multiple users, yeah! Does delegation still apply with the new connector? Is there a limit of rows/file size that the app can edit? Too many records for SharePoint, and no Dataverse (except via Teams - but are there row limits there too? and how to get the 100K row Excel file data into DV?) Thanks!
@nocodeyuan3 жыл бұрын
1. Only on OneDrive, not SharePoint, and table. 2. Locked data source and apps break ( open in Excel online, Excel Desktop, Power Automate, etc.) 3. Delegation not working( delegation command like "search" doesn't work.) 4. Can't change Datatype ( decided on import )
@ShanesCows3 жыл бұрын
Like it!
@bgrebe263 жыл бұрын
Great Video, eplains all the problems I've been having! need to change my data source now. Thank You! If you're looking to add Team members, please reach out!
@cmccoy782 жыл бұрын
I'm trying to create an Inbound inspection sheet for a distribution center. Part of this would be trailer seal verification. What is the easiest way to verify an input? Shipping clerk would enter seal number from paperwork in datasource, then unloader would enter seal number in powerapps. Is there a way for this to have a warning if they don't match?
@ShanesCows2 жыл бұрын
Chris use the concept in this video. kzfaq.info/get/bejne/d9ZjkqiSnq6ckmQ.html The video is about not creating duplicates, you just want the opposite, do a LookUp and make sure they are the same.
@yourstruly97773 жыл бұрын
Hi Shane , There are multiple individual excel tables and inputs on each are used to calculate summary sheets which have formula in place, I am able to feed inputs using power apps but the summary sheet isn’t auto calculating the formula unless I am manually clicking refresh connection. Is there a force refresh option or some excel tweak that I am missing out ? I tried using individual sharepoint lists but share point doesn’t allow lookup and calculate using other SP lists .. I am really running out of ideas here 🤦🏻
@CarFinanceSimplified Жыл бұрын
My main issue with using SP lists is that you also have to let everyone have access to them on the site and you can’t hide them from users being able to get to the backend?!
@ShanesCows Жыл бұрын
In this video I show you how to set item level security in SharePoint using a flow. kzfaq.info/get/bejne/rK2DgdWbnZiuk30.html
@CarFinanceSimplified Жыл бұрын
@@ShanesCows Thanks very much. I have now moved over to Dataverse but I will be sure to check it out.
@econoshiftcom2 жыл бұрын
Hi Shane, I agree with you that we should use SP lists as database. I have many occasions that I have new data records (1000+) in Excel and need to update my SP list according to the new data (update or/and add records). Currently I make a program with VBA to do that using ADO. However it takes time to complete it (some cases take one hour+). Can you tell me any suggestion for that?
@ShanesCows2 жыл бұрын
Did you see this video on importing from Excel to SharePoint? Microsoft Flow Excel Online Connector - Dynamic files 😮, odata, pagination and more kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html
@leandroabraham86973 жыл бұрын
Excellent video! I'm having a problem with my Power Apps and I don't know how to fix it, if I can. My App is a Requirements Apps, and it works based on two tables. One with the data of the request, and another with the data of the comments. A request can have several comments, so what I would be needing is that when I access a request (which has a certain ID number) and I want it to go through a "View comments" button, for For example, I raised all the comments of that requirement (Obviously related to that requirement ID), and that I can also add new comments from the App. My database is an excel in One Drive that contains these two tables. I'm going crazy to be able to achieve this. Does Power Apps allow this or does everything have to be done in a single table, without relating it when the source is an excel spreadsheet? Thanks friend, and congratulations on your great channel!
@ShanesCows3 жыл бұрын
Sorry for the delay. You can build this as long as you have a "key" that relates the data. In this series I show how to do it. Part 2 is the video you want kzfaq.info/get/bejne/et-cq9aJrrvddJc.html though you may need to start with Part 1 for context.
@elisekeiflin58532 жыл бұрын
Thank you very much for this video ! it explains well why you should never ever use Excel as a data source !! Is it a good solution to update an Excel spreadsheet (with Power Auomate ?) from a Sharepoint List to import data in Excel (and update some charts) ?
@RiaanPretorius Жыл бұрын
Hi Shane, thanks for your great videos, they are super informative. Question: which data source do you recommend I use in place of Excel? I've tried Sharepoint Lists but the functionality is a bit limited. I would like to run my Business from one large central database but I don't know which one. Access is cool but also outdated. Thanks!
@ShanesCows Жыл бұрын
SharePoint is the best data source if you want "free" or included with your Standard license. Dataverse is the best data source if you have the premium license. It is the best by a wide margin. We are currently rebuilding a lot of our internal apps to run the business on Dataverse with both a combination of Canvas and Model-driven apps. 😎
@RiaanPretorius Жыл бұрын
@@ShanesCows thanks. I have started with Dataverse today. Just having some issues trying to get it to export a decent Excel file.
@ShanesCows Жыл бұрын
@@RiaanPretorius Did you see this Excel Dataverse feature? kzfaq.info/get/bejne/mtyIm82i3NPLY30.html
@nadiarahel51362 жыл бұрын
Thank you so much I needed this , I have a question not related to this video , is there a way I can export excel sheet from SharePoint list but only the items selected not the whole list ? Thank you in advance
@ShanesCows2 жыл бұрын
Yup. kzfaq.info/get/bejne/n9OYq9xkt6vLiJc.html filter the collection befor you send it.
@nadiarahel51362 жыл бұрын
@@ShanesCows Great , Thank you so much
@jagadeeshk.4743 жыл бұрын
Good one. Can you try a video with SAP connector?
@ShanesCows3 жыл бұрын
I have never used that. 😑
@eliasas49212 жыл бұрын
Hola Shane, necesito ayuda con una proceso entre excel y prower automate. Necesito automatizar el arrastre de filas hacia abajo.
@ShanesCows2 жыл бұрын
Did you see this video? kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html
@FranciscoCamacho_fjcamachoj3 жыл бұрын
Thanks for the advice Shane. In your experience wich sources (excluding SharePoint lists) are good options?
@ShanesCows3 жыл бұрын
Dataverse is the best data source, Azure SQL would be number 2 but both of them require premium Power Apps licensing. Dataverse for Teams might be a consideration, it isn't premium, but has different hoops to jump through.
@sachinmundra3 жыл бұрын
Hello Shane, love your videos. In my quest to not use Excel as data source, i want to import excel data into a dataverse table. I think i can manually do so, but in the spirit of automation, i need to import a file as it hits a folder. So something like an excel file is dropped in a folder --> a flow is looking for new files --> pickup the new file and imports the data into dataverse --> TimeStamps the excel file itself and moves to another folder. Now the data in excel will be in dataverse table and ready to be used :) ... Thanks for any tips.
@ShanesCows3 жыл бұрын
Sachin most of the hard parts are covered in this video. kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html It uses SharePoint but the concepts are the same.
@user-go1sq4wc6r Жыл бұрын
Hi Shane, can PowerApps data be updated from within the app when Excel is the data source? I'm putting together a pro and con list for using SharePoint lists vs. Excel as a data source.
@willbee65913 жыл бұрын
Thank you for this video. You talked about using Excel as a read-only source. I would like to use Excel for the input of a drop-down list (read-only) but write the selections to another source. Is that possible? Perhaps you have a video on how to use one source for input and another source for output. If not... wouldn't that be a cool idea for a new video?!? :) (hint, hint)
@ShanesCows3 жыл бұрын
Try this video. kzfaq.info/get/bejne/apmVns1mstKyeok.html It shows what you are looking from but from a SharePoint point of view.
@moisestatis5418 Жыл бұрын
Great question and thanks Shane for the hint.
@moisestatis5418 Жыл бұрын
@@ShanesCows thanks for the hint.
@saralayoffs84099 ай бұрын
I have a question If I used sharepoint list it won’t work for me even though I Dont want to use excel and that’s because sharepoint list has limitation number
@mayalejandroboteroquintero15213 жыл бұрын
Hello Shane, Please help me with some advice about the following data security/privacy concern: How do you avoid that the PowerApps users modify the data directly on the SharePoint list if it is your back end data source? The above taking into account the following: -I have to use SharePoint as data source due to several restrictions, so there is no other option so far. -As you know, when you shares a PowerApp you must grant access to the users to the back end SharePoint list. -If a "curious" user achieves to know where the PowerApp data is, they can go there and edit it, which is dangerous whether your solution is an approval app or you app contains confidential information. Please, share your advice about that. Thank you.
@martyr_njatc Жыл бұрын
I have connected my PowerApp to Excel because I use PowerAutomate to hit an API and recreate the table every night. (I have an OfficeScript to delete all of the content and then recreate from the ParseJSON action). Is there a way to either create a SharePoint list and then check for changes every night, or better yet to connect to the API directly? I tried to create a custom connector but was unsuccessful. Any guidance would be greatly appreciated.
@htgungor18913 жыл бұрын
Another great video Shane, thanks a lot! In our company we have recently migrated all data from local servers to Teams/Sharepoint. That works fine however for some finance excelsheets we see that in Sharepoint excel macros, scripts, BI cubes do not work properly so we have to keep them in a file server in Azure still. I really would like to hep finance team for automating their processing by building some apps but since their excelsheet with macros etc do not work in Sharepoint I cannot use powerapps at least in the most efficient way. Would you have any recommandations on this? I would appreciate if you can make a videos on how to use macros, automation scripts in powerapps if we put those excel files in SharePoint . Thanks in advance!
@ShanesCows3 жыл бұрын
You can't. Instead you would have to think about changing the process so data was stored in SharePoint and Power Apps did all of the calculations that used to be done by the Macros and such in excel.
@htgungor18913 жыл бұрын
@@ShanesCows Thanks Shane! Appreciate your prompt respond! I am a fan of you as a beginner PowerApper:)
@johnfromireland75513 жыл бұрын
@@ShanesCows FYI Office Scripts is the new VBA language for Excel Online.
@htgungor18913 жыл бұрын
@@johnfromireland7551 @Shane Young yes indeed! Would be really if you can make a video over using Office Scripts function in excel and using it via powerapps/automate..
@Themanliest3 жыл бұрын
Can you use power apps to collect the data and have a second workbook that links to the power apps one? Ie bookwithmacros.xlsm cell a1='powerbook!A1', etc? Even after loading if you vba'd a copy, paste value approach on a refresh data button? Does linking a file this way lock it to power apps writing?
@bart64293 жыл бұрын
Hi Shane, thanks for the video. In stead of share point I want to save it in business central. I am having trouble to save data from a collection to business central using a custom connector. the custom connector works fine when I test it (at the custom connectors section) and the Get function also works fine in the power app (I had to do some little tricks to make it work). I tried to use the following formula: Collect (businesscentralconnector_1.Post("'CRONUS NL'") ; NewItem). NewItem is a collection where I save my article data (just one record). businesscentralconnector_1.Post("'CRONUS NL'") is the custom connector with the post action. I want to save this NewItem collection to Business Central, so a new Item will be created in Business central. thanks in advance for helping me
@mabob126 ай бұрын
I'm having a hard time trying to solve a problem. I would like to ask someone in this community for help. I have an Excel sheet connected to my Powerapps Canvas Form and we use it as our data entry. The form has ComboBoxes and TextInput Fields. When we enter our data and submit, most of the relevant columns in excel are populated. Except some of the ComboBoxes are not. The problem is when I try to edit an existing case details, I would like to have the ComboBoxes filled by default in my PowerApps with the row data for its respective column in Excel. All the TextInput fields are populated, but the ComboBoxes are empty and not interacting with Excel at all. Please advise and share any thoughts and solutions to this. Many thanks,
@marialimadeoliveira74892 жыл бұрын
Hi Shane, thank you so much for the content. In my case, I constantly have to update my datasource, and these updates usually come in a large number of records. In this case, updating a sharepoint list is not ideal, since I haven't found a way to input many records at a time there. Is that possible? Or is there another alternative besides a sharepoint list? That's the only reason why I use Excel today... As I can simply paste new lines there at once :( But indeed it's really bad for the app, since I connected it to Excel I have a series of new random errors rs. I'll so appreciate if you can help! Best wishes!
@ricobudimulyadi850410 ай бұрын
I've been having issues with Excel recently with Power Automate, getting the data sometimes returns old data or even nothing at times, I'm having headache handling this reliability issue. It's frustrating cause I can't exactly debug what or why is this happening?
@abhiq13 жыл бұрын
Hi Shane, Firstly this was very helpful thanks. My query is I have data that I want to update everyday from another system and use it in my app as read only. Seems possible to get that data in excel file and use it here but issue is with deligation limit. Is there any way to update SharePoint list data everyday without manual activity? Flow takes very long time to do this.
@ShanesCows3 жыл бұрын
Flow would be my guess. Not sure.
@chetansoni1393 жыл бұрын
thanks for this Video @Shane i have been using Excel online for the past 1 year & now i'm getting trouble with it. my data type is mostly, the camera photos & data entry or description associated with the Pictures. data size: 4-5 GB of data per yearly now, i'm planning to migrate the data from Excel & Sharepoint Documents to MS SQL/Sharepoint list can you recommend me which data-source is the best in my scenario. MS SQL or Sharepoint List ?
@ShanesCows3 жыл бұрын
SQL is a better data source for tables but does require a premium license. So guessing SharePoint is the next step for you. I have lots of content on it.
@galinagolant3 жыл бұрын
Thank you for this video!! Answered a number of questions I had. I still have two questions, is it possible to save data in Excel and in the SharePoint list at the same time? (Add two commands to OnSelect Button) And you said that if the Excel is opened then we can't write a new record to it. What about the Patch command? Will it solve this problem?
@ShanesCows3 жыл бұрын
Yes, you can patch 2 records. Put a ; between the two formulas. No patch doesn’t save you from the locking issue.
@ChihMingLiao3 жыл бұрын
Great Video, Great Advise. However I find myself needing to use Power Query tool in Excel to do datamining from old database servers. Guess that's read only and presents less of an issue.
@LordOfTuna2 жыл бұрын
I'm currently thinking about nearly the same case. Would it be an option to import every old excel file into a sharepoint list?
@alanramsay97343 жыл бұрын
Hey Shane, I have been getting both the formula, (even though I don't have any), and the lock, (even though I own the file and its not locked), errors so I am going to covert over to SharePoint as you suggested. Do you have any videos that walk through using SP lists as the data source, how to use an SP list to populate a drop down in an app etc?
@ShanesCows3 жыл бұрын
Alan sorry I am slow. I have a lot of SharePoint videos. The good news is everything you do in Excel today will be the same when use SHarePoint (or any other table data source) all of the logic is the exact same so you don't have to learn new skills.
@rafaelmorales67363 жыл бұрын
Thanks Shane, I finished my app based on Excel, but I'm facing the problem that I should share this file as well and I don't want users can seen sensitive information on the spreadsheet. Question: May I solve this issue if I migrate my database to Share Point or SQL? Thanks for the advice. Keep well.
@ShanesCows3 жыл бұрын
Yes, get the data into different SharePoint lists and then secure each as appropriate.
@fathimaasmitha97682 жыл бұрын
Hi Shane great video. Thanks! Just a query, Is it possible to upload an excel file to modify the dataverse table datas, so that giving the customers an option for bulk uploading data to their app?
@ShanesCows2 жыл бұрын
Yes. Check out this video. kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html it is to SharePoint but same concept to DataVerse
@fathimaasmitha97682 жыл бұрын
@@ShanesCows Thanks Shane
@tahwnikcufos2 жыл бұрын
Your #1 problem isn't a problem... if you made an app to make it easier for "you" to enter/change data in "your" spreadsheet, it wouldn't make any sense to ever have them both open anyway... SP Lists are even more dangerous, because it's easy to delete/damage your data, with zero option to recover. But again, in any DB type tool, the underlying data should never be accessed directly regardless - in dedicated environments like SQL Server, the end user can't access the tables without special tools either.
@subhraghosh84343 жыл бұрын
I am using excel as a datasource ,can simultaneously multiple users update excel (create ,update) through powerapps ?what is the number of concurrent users ?
@ShanesCows3 жыл бұрын
One user at a time.
@anw19863 жыл бұрын
What about using Flow as a middleware, and interact with Excel?
@ShanesCows3 жыл бұрын
You can but Excel and Flow get grumpy also. There is a new Excel connector that is supposed to work better but I haven’t tried it yet.
@lopezsolisalexa44063 жыл бұрын
I do have a problem with excel , when the co owners open the app and wanna see uploaded photos in the forms , they can not :( they just see the Data , i dont know the reason , but it´s really frustating , I hope you can help me, I have already shared my file with the users :(
@ShanesCows3 жыл бұрын
I am not sure. Sorry. I don't ever use Excel so I haven't ran into that issue.
@magnushoback84683 жыл бұрын
Great video Shane! How many records can be stored in a SharePoint list?
@ShanesCows3 жыл бұрын
A lot. Millions? I am not sure honestly. Though you will see performance problems in Power Apps the more you have.
@espeolguin3 жыл бұрын
Sharepoint List has a limit of 20,000 “rows”/items per list. But also depends on the size of the data
@moisestatis5418 Жыл бұрын
@@espeolguin gracias Esperanza también estaba buscando esa información.
@SG-yb5tz3 жыл бұрын
Thanks Shane! I am struggling to connect excel data to my share point list. Can yoiu please make a video on that?? So the problem is : I want to add the excel table as a share point table/list and then use it as a source for the power app drop down options. But I cannot add the sharepoint table/list like I add the excel table in powerapp. Please let me know what to do.
@ShanesCows3 жыл бұрын
Maybe import the excel to SharePoint kzfaq.info/get/bejne/h8mHnaSlnLe6nmg.html
@SG-yb5tz3 жыл бұрын
Hi yes. Thank you so much for your response. I did that. But the problem is that I can’t update the SharePoint list (from which I created the Power app) with the excel list imported in the app.
@alycelow80643 жыл бұрын
How to show distinct values in Powerapps Choice Field?
@ShanesCows3 жыл бұрын
There is a Distinct function. kzfaq.info/get/bejne/jNSRl66IzszPomw.html
@ZorroZoroZ3 жыл бұрын
Thanks Shane. I uploaded an Excel file on my canvas app and planning to use it to store the label translations on the different languages I have to provide, means that I am only using Excell file readonly. Do you see any concern on performance? Can you suggest me an alternative for translation?
@ShanesCows3 жыл бұрын
Read only should be safe :)
@frankgovers84203 жыл бұрын
Hi Shane. But when a user has edge. He cannot import an excel sheet. It needs active x controls
@ShanesCows3 жыл бұрын
I imported with the new Edge just last week. Maybe the old Edge doesn't work? I am not sure.
@frankgovers84203 жыл бұрын
@@ShanesCows , sorry, even the new edge does not accept active x. So microsoft does not support its own products. But the solution is not to import data from excel, but to export from excel table.
@rahibmansuri40613 жыл бұрын
I have large data in SP(More than 5000 rows).. I want to pull that data in combo Box in Power Apps. Can you please help me out in this or give me some alternative for that
@ShanesCows3 жыл бұрын
Use filter. Nobody wants to scroll through 5000 items. I also think combobox has 500 item limit.
@rahibmansuri40613 жыл бұрын
@@ShanesCows okay.. let me try
@CarlosSolisNow2 жыл бұрын
@shane. Great content as always. Here is the one space where I have found excel to be great for my students, and I would love for you to show me, for real, how wrong I am. In excel, if I name a could something like monkes[image] and point the power app builder to it, it will automatically assume it is an image field, plus create the requisite image folder there. Easy and no sweat. For my students who are just beginning, this is super easy. I can’t seem to be able to do the same with SharePoint lists. Can I ?
@CarlosSolisNow2 жыл бұрын
To expand on this. I created a Sharepoint list with to fields, A single line column, called name, and an image column called face. PowerApps created the proper browsecreen, and a nice details screen, but the edit screen has no way to replace the current image when one edits an existing record, or to add a new pic when one want to create a new record. What is missing? Should all this be handled via hyperlinks?
@kanwalsaini97173 жыл бұрын
hello Shane, I want to understand if powerapps can read and update information of an excel file present in sharepoint library instead of onedrive? is it possible to do so
@solarpunk_3 жыл бұрын
Lesson learned the hard way Shane. Good tip though; Sharepoint wins over excel every time.
@ShanesCows3 жыл бұрын
🐶 Yup!
@vmorgan823 жыл бұрын
can you do a video on a series of dropdowns that eliminate the options as you go (ex. dropdown1 has 10 options, dropdown2 has 9 bc of the selection in dropdown1)
@ShanesCows3 жыл бұрын
Interesting. Can you tell me why? Someone asked for similar last week so curious what you all are up to. :)
@simonb75563 жыл бұрын
Another good video
@ShanesCows3 жыл бұрын
I appreciate that
@twilson8763 жыл бұрын
Hi Shane, Is it possible to use sharepoint as a data source and still keep the ability to save multiple images to a record by a reference table like the site inspection template (uses onedrive)?
@ShanesCows3 жыл бұрын
Sure. Save the file to a doc lib. Have another list that has a link to the file and the ID of its master record. Then you could have as many as you like.
@twilson8763 жыл бұрын
@@ShanesCows Amazing, thank you!
@TheAjayspat3 жыл бұрын
Hi Shane.. can we lock fields to be edited
@devgianart79663 жыл бұрын
This error issue locked something is what I'm experiencing right now.
@ShanesCows3 жыл бұрын
It is annoying. I don't have a magic fix for you either.
@dkfresh66563 жыл бұрын
Same issues with using google sheets?
@ShanesCows3 жыл бұрын
I am not sure dk. I have actually never used Google Sheets with Power Apps. 😐
@practicalpowerapps3 жыл бұрын
Thank you Shane for something to refer forum posts to that will actually (hopefully) convince them of this fact. Your facial expression says it all . . .
@ShanesCows3 жыл бұрын
I hope this helps you and the rest of the awesome people that make the forums work. 🐶 You guys are awesome!
@practicalpowerapps3 жыл бұрын
@@ShanesCows Already has had first link done.
@b0rme3 жыл бұрын
I’ll be honest I didn’t even know it was possible to use excel as a data source like this. I’ve only ever used excel to import static data into my apps (this seems to be completely okay??)
@ShanesCows3 жыл бұрын
Approved! 😍
@b0rme3 жыл бұрын
@@ShanesCows I find it easier to create tables this way, instead of hard coding collections. I wonder what is better in terms of performance
@rivernet623 жыл бұрын
I've granted users access to the Lists my App uses, which they need to be able to use the app (duh). But I notice that as a result, they can now also browse to the List and modify the data willy nilly. Is there a way to allow them to run the app, but restrict their ability to browse to the actual data screen?
@jwyatt0433 жыл бұрын
You can't stop them going to SharePoint and seeing and modifying the list, but you can modify your app so they don't see the full list data there, or only see the data that they have modified or created. If you are concerned you can a) turn on version history for list items b) get Flow to notify you of changes c) set up a backup list that users don't have permission to use.
@rivernet623 жыл бұрын
@@jwyatt043 Thank you, Jason!
@ShanesCows3 жыл бұрын
Thanks Jason for the assist!
@jallabalaram4331 Жыл бұрын
After Patch data Into excel data source, Data source not refresh immediately Even using refresh() function
@annadewitt1233 жыл бұрын
Today I wanted to use an excel with a lot of formula as data source, and I learned that I can't. It was very disappointing :( I will watch the video when I go back home, but do you have any recommendation for me solving this problem?
@ShanesCows3 жыл бұрын
Nope. Excel formulas is a no. You are going to have to find a way to get the data without formulas or into another place like SharePoint.
@annadewitt1233 жыл бұрын
@@ShanesCows Thank you for your reply. I have to transfer data in excel to sharepoint like one of your subscriber in comment section, then with calculated column property 😓
@rivernet623 жыл бұрын
Sharepoint (now "Lists") offers calculated columns. I am NOT an expert by any means, but I have made it work. The tough part is finding documentation. Look into it.
@jaded19772 жыл бұрын
I 100% Agree. However, I have end-users who are addicted to Excel and refuse to use anything else. So I built into the app where they upload an excel spreadsheet from the app and read into my SQL database the results. Clunky, but at least I don't have a bunch of addicts walking around the office screaming at me because they cannot use Excel. Sigh.
@ShanesCows2 жыл бұрын
I like it. :)
@TagenigmaLLC3 жыл бұрын
First thing I tried to do is add Sharepoint Excel as a data source. Nope! Won't be using Excel thanks!
@ShanesCows3 жыл бұрын
😍
@zaydarendse28123 жыл бұрын
hehehe...Thanks Shane
@ShanesCows3 жыл бұрын
Glad to help. 😁
@jeroendekk13 жыл бұрын
hahaha the title made me laugh. Because this is so true.
@ShanesCows3 жыл бұрын
I wanted to make it Turrrrrible like Charles Barkley but I decided not enough people would get it. 😂
@benjamincpetersen3 жыл бұрын
Yes Excel is NOT a good data source at all! Although the import Excel as static data works great, but I don't think it technically stays as an Excel file when you do that.
@ShanesCows3 жыл бұрын
Correct. I wanted to cover Import in this video but ran out of time. It is slated for a future video now.