Modeling my CDS database, I am also developing my PowerApps BUT Excel add in has been of a GREAT benefit meanwhile, for importing data (I prefer Publish data over the Import for many reason those I can post about separately).
I even think that for some reasons, including delegation limits (!), I may find myself preferring working on Excel rather than PowerApps! Of course after adding some business rules, security rules, Flows etc, with these, Excel can be a very nice way to manipulate your nice CDS data!
However, Excel Add in for CDS needs lot of improvements, here I list some: -
- If the excel book table (entity) is filtered and saved, closed, then reopened, you will get an unnecessary error, that you need to clear filter before you can Refresh, Edit and Publish Data. When your excel book has few or lot of tables, it is a hassle to find which table has the filter to clear 🙂 Please add to CDS the power to clear filters upon opening the excel book with CDS add in. Simple.
- In look up columns, you get the data from other entities in the right pan, ALL data, organized in columns order that I never understand. It works but it is headache! Please enhance Excel add in to be reading lookup fields from VIEWS those I can create, organize columns and even make filters outside excel (at view level). For Instance, to choose a Default contact for an account, Why i have to see all contacts even they are not related to the selected account? wrong.
- some default conditional formatting or at least column headers would be nice to reflect column features such as required, etc.
- Direct the pointer to the first cell of the row created after you hit New
- For Business Rules, create a message to make a hint (created by the developer) for the user, that message then should be available in PowerApps and Excel!
- Make CDS a datasource in Excel PowerQuery, this is a separate IDea that is under review, i swear it is a must
- find a way to represent the Look up column by a single column rather than 2.
Thanks @Yahya, will review.