cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Created PowerApp based on Excelfile with Formula

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...

 

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-sho...

 

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

1 REPLY 1
Community Support
Community Support

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,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (4,197)