Part 1: Power Apps with Excel, creating tables, lookup columns, and changing column types

[@danishhazim5127]

i have a question: how can I add data from the input boxes in Power Apps into Excel? is it possible? if so thank you for responding

[@andrewhess123]

It's possible but Power Apps needs a Table Name in Excel to write to.

[@habibindraalivito2534]

Question: when i press submit button, it said "network error when using patch function: the requested resource is locked". and btw i use onedrive spreadsheet for the databases

[@andrewhess123]

My guess is the document is currently open? When you try to patch or someone has it open?

[@jametjm1802]

May I ask you about picture , I use to add [image] in header and power app change data type into picture, when I do it with other power app, this time do not convert. Could u have recommend??

[@andrewhess123]

Hi Jametjm, I am a little confused with your question? How to add image to Excel?

[@jametjm1802]

I'm connect table inexcel in Sharepoint / Onedrive with Power app. In the header of table, I add [image] such as Header is Figure >> Figure [image]. In power app, it will see data type like picture and it will keep url in excel and picture's kept in created automate folder in onedrive. My point is Excel in Onedrive almost completed everytime but sharepoin cannot. Do u have recommend?

[@andrewcrompton3444]

Excellent! I've been having some problems with this, and you make it all so easy! Thank you! A question. In your gallery (around the 14 minute mark) you display Completed as a text value. Suppose we wanted to replace this with a checkbox. I would have expected that PowerApps would automatically translate the Excel values TRUE / FALSE into booleans, so the checkbox could directly reference ThisItem.Completed. But this does not work for me.

[@andrewhess123]

Hi Andrew, It would be possible to use a checkbox instead of the true/false text value. Most likely in the "Checked" property of the Checkbox you could write and if statement, If(ThisItem.Completed="true",true,false). This is also going to depend on if the checkbox is modern or not. But the reason is from excel it doesn't know it's a boolean value, it believes its a text value, thats why you use the " ".

[@andrewcrompton3444]

@@andrewhess123 Hi, Andrew. Thanks for the reply. You are right: the checkbox works correctly when I get it to treat the Excel data as text, in the way you describe. I have tried using both modern and classic checkboxes: the behaviour is the same. I have also built a form in the app in order to edit the Excel data. Here, it is possible to simply submit the form using the value of a checkbox, without having to translate it into text. In this case, Powerapps does the translation automatically. It would be useful if MS were to make it so we did not need to treat the Excel data as text when reading it. Maybe they'll do that in due course.

[@andrewhess123]

@@andrewcrompton3444 Nice glad you got it working!

[@AnandTest-n4x]

Question: I am using lookup values on a update form. When I save the look up value, the value gets saved successfully but the form displays the original value instead of what is saved

[@andrewhess123]

Set the Default or DefaultSelectedItem to the LookUp value? Since it is correct probably reseting the default should work.

[@AnandTest-n4x]

@@andrewhess123 Thank you, it worked

[@tulanlan3090]

I am making an app for my thesis and creating some filters using Cascading dropdown based on the first filter but I can not export those to an Excel file. How can I solve this ?

[@andrewhess123]

Hi Tu Lan Lan, you need to patch them most likely.

[@tulanlan3090]

@@andrewhess123 I'll take a look into that. Thanks for your advice.

[@mike9512]

Hopefully, you still read these. You said you don't recommend Excel as a backend, but what else can I use? I'm a student just learning power apps and databases. I would love to use a database, but I'm not a business. I'm one guy storing personal data. I can't afford crazy pricing plans. Is there a better option than Excel?

[@andrewhess123]

SharePoint is the easiest... and if you want to explore more you can do "Dataverse for Teams". Or if you want to learn even more you can sign up for a free subscription to Azure and set up a Dev Azure SQL Server.

[@mike9512]

@@andrewhess123 Thank you for that quick response. I have always had an interest in computers but I'm diving into a lot of stuff that is new and overwhelming because of how many products, languages, etc... are out there. It's hard to know where to focus. Thank you for the suggestion. Take care.

[@andrewhess123]

@@mike9512 Well what do you want to learn? What's your final goal? Is it to make money? Is it to get a job? Is it to improve the world? I would start there 😀. My goal is actually just to learn, I love just learning everything it's actually a problem... 😅 Start with what is free, there's so much free content online and things you can try out.

[@andrewhess123]

@@mike9512 another suggestion is tell ChatGPT what you like to do and where you want to go.. then ask it to give you like 20 suggestions. Then find one of those and go from there 😃

[@dillep6989]

Superb explained.. Thank you sir.. Me beginner able to understand.

[@andrewhess123]

Thank you for watching Dileep

[@B2BEnergy]

Mate please slow this down it’s good !

[@andrewhess123]

Should I recreate this with a new video?

[@B2BEnergy]

@@andrewhess123thank you, I actually got most of it but it was the part you did the look-up. I just can’t get my Look-ups working. I’ve used the Drop Down for the simple list. But I have a Country List of 195 which really needs a Look-Up could you just do a video for creating a Look-up for Countries- please

[@Shh...Kannada]

This is what something i wanted!! 😎 Thanks for making this video.

[@andrewhess123]

Thank you for watching Vikrama!

[@SkMustafijurrahaman786]

Awesome. You just fulfilled my criteria. Thank you ❣

[@andrewhess123]

Nice perfect! Hope you crush your goals!

[@vidsbyme2590]

can power apps take information from two excel sheets to make an emailed payment letter?

[@andrewhess123]

Yes 100%, so can Power Automate

[@diogoasenjo]

Is there any way to edit data directly in a excel sheet inside of Power Apps? I don't want to create or edit a single value, but all the data from my table at the same time, is this possible?

[@andrewhess123]

Possible with limitations by delegation.

[@diogoasenjo]

@@andrewhess123 do you have any video or material about ir? I have a demand here at work that honestly I don't understand. They want do a simulation inside a dashboard of Power BI, but they want to see an excel sheet and edit it lol. It's impossible to do it with BI, I was wondering if with Power App I was able to solve it, but the easiest way was just simply use excel xD

[@ajayrathod7777]

Hi, how can we fetch value in power app text input from attached excel file

[@andrewhess123]

Hi Ajay, you would need to connect to the data source of the excel file table, and then do a LookUp to the exact cell that you want to fetch.

[@ajayrathod7777]

@@andrewhess123 will it work for attached excel file by user?

[@andrewhess123]

@@ajayrathod7777 If you want to do something like this, probably needs to some sort of workflow by Power Automate

[@andrewhess123]

Power Apps uses data sources, it does not take data from attachments and just have the data readily available. To do that you need to have a way for the attached data to be written to a data source. That would most likely be a workflow from Power Automate.

[@ajayrathod7777]

@@andrewhess123 Thanks for your reply,does powerapp is reliable for performance can it handle users easily or it will break ?

[@maheshraghuvar1692]

😳😳 wow!! I was looking something like this from last 3 days finally got it 😇😇

[@andrewhess123]

Great! Thank you for watching Mahesh!

[@samuel-tj4pe]

hey can you do a video where you show how to search excel data and the app gives you all the other data from the row

[@andrewhess123]

Hi Samuel, Yes I can most likely do that. There are some limitations using Excel and Search but can we can talk about that too.

[@CarlosSoares-yb8mp]

Great, my friend! You explain exactly I need! Many thanks!

[@andrewhess123]

Thanks for watching Carlos!

[@pinghwakuang4046]

Very very good video. Thank you so much!!!!!

[@andrewhess123]

Thanks for watching Ping !

[@mahirazabin1212]

This tutorial literally taught me so much!! Exactly what I needed! Thank you loads!!

[@andrewhess123]

Thanks for watching mahiwayyy!

[@kannadahollywood]

Wow great 😊 looking for this type of contents, thank you so much!!! For answering my question. It's great knowledge share thanks again !!!💞💞

[@andrewhess123]

Awesome Manjunath! Glad it is helpful!

[@kannadahollywood]

@@andrewhess123 waiting for upcoming parts !!🤠🤠🙏🙏🙏😍😍😍

[@Sobec8]

Great video Andrew. However, you did not address the date issues between Excel and Power Apps and how to overcome the UTC issue with Power Apps. Any chance you could do a follow up on this?

[@amitabhphatak]

This is what i wanted. To filter the vertical gallery. Thanks!

[@andrewhess123]

Excellent! Thanks for watching!

[@HitZero444]

You indicated that excel is not usually recommended as a backend, why is that?

[@andrewhess123]

Hi Hit, there are limitations with Excel, it is not a relational database. Delegation issues. Excel is the lowest form of a datasource for Power Apps/Power Platform. You lose on security, not as much version history. It's easier for people to break the connection by changing column titles or removing titles. People can break a whole powerapp by accidently moving an excel file. The main reason though, to use Excel is most people know Excel. If you get can get your users out of Excel to SharePoint, thats a plus, if you can get your users out of SharePoint to Dataverse or SQL that is even better.. but then you have to think about $$$

[@HitZero444]

@andrewhess123 great info, thank you for clarifying!

[@saikrishnamandala7667]

Hi Sir, Is it possible to create a ticketing system using Power Apps? If yes, could you please show a demo

[@GofherEstomihiSiregar]

thank you, you're so helpful

[@andrewhess123]

Thank you for watching!

[@RamiMaach]

Hi i made an app out of an excel file but now i want to filter the displayed data to another excel file. Can you make a video about that?

[@rasheedahmad1449]

How to auto genrated id

[@andrewhess123]

You would have to do a LookUp on the largest id and then use the next id, but I would not recommend doing this in Excel, I would use SharePoint at very least to do autogenerated IDs. Else you could probably just do it on the Excel side, it is a table you could auto generate in Excel

[@RajYadav-e2f]

Hi, need your help, how users of power apps can upload Excel and system save data in share point according to their respective head, I have prepared power automate flow but it's not allowed to upload Excel, it saves data from Excel with provided location

[@andrewhess123]

Hi, Use an attachment in a SharePoint list, or here is more examples: www.matthewdevaney.com/power-apps-easiest-way-to-upload-files-to-a-sharepoint-document-library/

[@mohamedawad1299]

Good

[@andrewhess123]

Thank ya Mohamed!

[@mohamedawad1299]

How to place the Add Image button. When pressed, the gallery on the phone opens, the image is selected, and it is linked to the Excel sheet Thanks in advance