"HDR=No " indicates the opposite."IMEX=1 " tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. "HDR=Yes " indicates that the first row contains column names, not data. OLEDB Provider=.4.0 Data Source=C:\MyExcel.xls Extended Properties="Excel 8.0 HDR=Yes IMEX=1" Some reports that Excel 2003 need the exta OLEDB section in the beginning of the string. Try this one if the one above is not working. Provider=.4.0 Data Source=C:\MyExcel.xls Įxtended Properties="Excel 8.0 HDR=Yes IMEX=1" It's early days but it looks promising."HDR=Yes " indicates that the first row contains column names, not data. I'm now experimenting with running the queries in SQL to pre-filter the data I need for the app, letting SQL do the hard work. This is surprisingly easy to connect up to PowerApps and gives you the full power of a SQL database using SQL Server Management Studio. However, I also found this limiting (especially as there was no easy way to back up the data) and I've now moved on to using SQL hosted in Azure. WARNING, I found that working with large excel tables could end up being unstable and very easy to corrupt them (not sure if this was just me or if others have had a similar problem?).Īfter having a few excel data tables corrupt (data lost), I moved on to using the CDS (building my own tables to hold my data) this was more reliable. You can also create child collections from collections to further pre-filter your data.) (Tip: If you are using the same data in multiple places (regardless of data source), using a collection significantly improves app performance as the data is cached, rather making repeat calls to the data source. There is a ForAll() function in PowerApps that acts like a ForEach() / Loop() function in SQL queries to allow you to update multiple lines at the same time but I haven't managed to get this to work reliably (yet).
With a little bit of work you can display the data using a gallery to look like a table, adding a button to update the data at the end of each row. Once you have the collection you can use it within the app like you would normally to display the information and then use the Patch() function to update the excel table - aligning the collection fields to the excel table fields. filtering on a date range or some other field in the excel table to narrow down the results.
The only way I found to achieve this was by pre-filtering using a collection e.g.