Power Platform Community Forum Thread Details

Hi everyone,

First of all I would like to give my prior apologies if I have missed any rules regarding this post. I currently has an issue, which seems to be a common issue, namely connected PowerApps with an Excel file with formula in the table. I have found the following post from 2016:

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/PowerApp-unable-to-connect-to-excel-file/td-p/7851

Has any solution been found to this issue and if not, does anyone have a solution to access and update the table. This user seem to try a solution in which two tables exist; Asheet being the simple one connecting the Excel table with PowerApps and Bsheet which is the one with formula that gets updated when Asheet get entries (from PowerApps).

https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/If-Excel-has-a-formula-data-not-showing/m-p/1539/highlight/true#M941

I hope my question is clear, if not I would like to ellaborate and again sorry if I have missed any rules of this forum.

Kind regards,

Anders

Categories:

Building Power Apps

Hi @Iskov123 ,

Do you want to update records within your Bsheet Excel table (which contains calculated field) from PowerApps app?

Firstly, if you want to connect to a Excel sheet from a PowerApps canvas app, the data within the Excel sheet must be formatted as a Excel table already. And the Excel table could not contain a Calculated field, connecting to a Excel table which contains Calculated field from a PowerApps app is not supported within PowerApps currently.

If you would like this feature to be added in PowerApps, please consider submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

So if you want to update records within your BSheet using PowerApps app, you must create a conection to the Excel table within th BSheet from your app.

As an alternative solution, I think it is not necessary to add a Calculated field within your BSheet, instead, you could consider do the calculation within your PowerApps app, then write the calculation result back to your Bsheet (formatted as Excel table already).

On your side, please remove the Calculated field from your BSheet, instead, add a normal Text column to store the calculation result from your app. Then create a connection to your BSheet Excel table from your app, then you could access data in your BSheet Excel table.

More details about generating app based on Excel table, please refer to following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/get-started-create-from-data

You could generate an app based on your ASheet Table, then create a connection to your BSheet table from your generated app. Then you could use the app to create a new entry into your ASheet. If you want to update the BSheet table when a new ASheet Entry is created, please take a try with the following workaround:

Set the OnSuccess property of the Edit form to following:

Patch( // Add records into your BSheet table when new ASheet entry is added
 'BSheetTable',
 Defaults('BSheetTable'),
 {
 CalculationTextField: EditForm1.LastSubmit.Column1 + EditForm1.LastSubmit.Column2 // do the Calculation using PowerApps functions along with the submitted column value from the ASheet Table
 }
);
Back()

Note: You could also use the Patch function to update records in your BSheet table

More details about Patch function in PowerApps, please refer to the following article:

Patch function

More details about the functions supported in PowerApps, please refer to the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/formula-reference

Best regards,