MoreBeerMorePower

Power Platform中心だけど、ノーコード/ローコード系を書いてます。

Working with Excel file in Power Apps

f:id:mofumofu_dance:20210430162404p:plain

"Excel is not a database"

This argument was also true when using Excel files as a data source in Power Apps, because the following important functionalities were not supported in this case :

  1. Simultaneous editing by multiple users - Any operation will be refused when other user open file
  2. Delegable data source - App cannot access record exceeding 500 (default delegation limit)

Recently, Power Apps team announced improvements for Excel files as a data source in following post.

powerapps.microsoft.com

In conclusion, this improvement of Excel Online connector allows that Excel files can be a strong candidate as a data source for Power Apps.

Prerequisite

In order to take advantage of the update, you need to add a data source in the Data pane in Power Apps.

f:id:mofumofu_dance:20210430145515p:plain

Note : If you start from data with "Excel Online", the app will connect Excel table through OneDrive connector, not Excel Online connector.

f:id:mofumofu_dance:20210430145944p:plain

Key update #1 : Simultaneous editing by multiple users

With (old) OneDrive connector, it is not available to create/update data from Power Apps while opening file, but Excel connector allows you editing data with multiple users simultaneously!!

In following tweet, 2 users create records from Power Apps and another 1 user opening Excel file on web browser.

This resolved one of the problems, the file will be no longer locked by opening file :)

Key update #2 : Delegation support

As is well known in the context of Power Automate, the Excel online connector allows you to set up a Filter and OrderBy query for data retrieval.

f:id:mofumofu_dance:20210430153758p:plain

If this is also available in Power Apps, it means that delegation will be partially supported - related to Filter(....) and Sort(...) functions.

Filter query

With new Excel Online connector, Filter operation will work well, and can retrieve data exceeding delegation limit 500!!!

f:id:mofumofu_dance:20210430154530p:plain

Note : Same as Power Automate action, Filter(...) function with single condition using =, <>, StartsWith, EndsWith and Search(...) function are supported.

OrderBy query

In Power Apps, OrderBy query is written as Sort(...) or SortByColumns(...). For both functions, only single expression is supported in Excel Online connector.

If you set multiple sort condition by SortByColumns, the following error will be displayed and no data will be shown in Gallery control.

f:id:mofumofu_dance:20210430155925p:plain

Bonus : Excel Table containing formula is supported

Now we have BIG bonus by Excel online connector.

As it has been already pointed out in following blog post by Eickhel, Excel online connector in Power Automate can get table data containing calculated columns.

https://powernimbus.com/2019/09/getting-excel-calculated-data-in-powerapps-using-flow-regexp-method/

In Power Apps, it is now possible to retrieve data from tables containing calculated columns as well!

f:id:mofumofu_dance:20210430161247p:plain

Maker can now leverage Excel functions for data operation in Power Apps without use of flow or Graph API!!

This is not yet documented though, you can try it :)

Summary

There has been long discussion about using Excel file as data source in Power Apps. With help of new Excel Online connector, it allows us...

  1. Document library Excel file support
  2. Simultaneous editing by multiple user
  3. Evaluation of cells containing formulas
  4. Simple delegation support

Of course, it depends on the properties of your business, but I think it's time to revisit about using Excel files as a data source with help of Excel Online connector.

*IMO, it can be good candidate :)

f:id:mofumofu_dance:20210430162306p:plain