cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laur1RDE24
Level: Powered On

link excel with formula

hello everyone - sorry for my english

on my excel table i put an "index" (number inscribe on a water meter) every week. on my excel i've the column with formula to calculate the consumtion between two "index".

It's easy on excel  and  I know it's uncompatible with powerapps when my excel is linked with my app.

 

What is the solution to calculate this on power apps.

 

PS : when i create my table excel without the calculate column, all the data are visible on powerapps.

When i return on my table excel and i create the column with the calculation, the formula is : [@index]-B42 (for example)  and it work on all the table but after ther is no visible data on power apps . 

 

help me please. 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
mdevaney
Level 8

Re: link excel with formula

@laur1RDE24 

Thank you for sharing the screenshot.

 

You are correct.  The reason your PowerApp data is showing as blank is because of the calculated column.  Remove and calculations from your worksheet (copy + paste special as values).

 

Now go to your app. Create an edit form based on your Excel datasource.  There should be 3 fields: Date (a date picker), Index (a text input called txt_index), Colenne1 (a text input).  The user will input the Date and Index but the app should calculate Colenne1 for the user.

 

Delete the Text Input for Colenne1 and add a Label type control instead.  Place the following code into the label's Text property:

 

Value(txt_index.Text)-First(SortByColumns(your_excel_datasource_name,"Index",Descending)).Index

Don't forget to change the Update property for Colenne1 to reference the label you created.

 

Value(txt_index.Text)

 

Again, the key concept here is always to do your calculations in PowerApps, not in Excel.

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

 

View solution in original post

laur1RDE24
Level: Powered On

Re: link excel with formula

brilliant

just what I needed

after some adjustments, it works

just : it was difficult (for me !!!) to insert the new label type inside the correct datacard. I cheat with one 

and" ; "instead of" ,"

Value(txt_index.Text)-First(SortByColumns(your_excel_datasource_name;"Index";Descending)).Index

 

thanks a lot

View solution in original post

7 REPLIES 7
mdevaney
Level 8

Re: link excel with formula

@laur1RDE24 

Could you please share a screenshot of your app and another screenshot of your spreadsheet?  It would be helpful to visualize what your app looks like and how your data is structured.

 

Community Support Team
Community Support Team

Re: link excel with formula

Hi @laur1RDE24 ,

Do you want to conect to a Excel table with calculation column from an app?

 

Actually, it is an known limit with Excel table data source -- currently, we could not connect to a Excel table wich a Calculation column from a PowerApps app.

 

As an alternative solution, I think the built-in functions within PowerApps could achieve your needs. You could consider use the functions in PowerApps to calculate the consumtion between two "index" directly.

Please remove the Calculation column from your Excel table, instead, add a normal text column to store the consumtion value between two different "Index". Then you could consider create a Edit table using Gallery control within your app to list all records from your Excel table, then within the Gallery, add a Text Input box (TextInput1), set the Default property of the Text Input to following:

IndexColumn - AnotherColumn               /* <-- calculate the consumtion */

After that, add a "Save" button outside the Gallery, set the OnSelect property to following:

ForAll(
           RenameColumns(Gallery1.Allitems, "PrimaryColumn", "PrimaryColumn1"),   /* <-- PrimaryColumn represents the column in your Excel table, which could identify one record uniquely */
           Patch(
                    ExcelTable,
                    LookUp(ExcelTable, PrimaryColumn = PrimaryColumn1),
                    {
                       CalculationTextColumn: TextInput1.Text
                    }
           )
)

More details about creating a Editable table using Gallery control in an app, please check the following video or blog:

https://www.youtube.com/watch?v=41Mpgn7svLs

https://www.youtube.com/watch?v=O2WqhRTUhyw

https://powerapps.microsoft.com/en-us/blog/editable-tables/

 

More detials about functions supported in PowerApps, please check 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.
laur1RDE24
Level: Powered On

Re: link excel with formula

for you mdevaney

Meantime thanks for your interest

pict1.jpg

i try with the
 
 

 

i try the 

Community Support Team
Community Support Team

Re: link excel with formula

Hi @laur1RDE24 ,

Have you taken a try with the solution I provided?

Is the solution I provided above could achieve your needs?

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved. If you have some issues with the solution I provided above, please feel free to reply here.

 

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.
mdevaney
Level 8

Re: link excel with formula

@laur1RDE24 

Thank you for sharing the screenshot.

 

You are correct.  The reason your PowerApp data is showing as blank is because of the calculated column.  Remove and calculations from your worksheet (copy + paste special as values).

 

Now go to your app. Create an edit form based on your Excel datasource.  There should be 3 fields: Date (a date picker), Index (a text input called txt_index), Colenne1 (a text input).  The user will input the Date and Index but the app should calculate Colenne1 for the user.

 

Delete the Text Input for Colenne1 and add a Label type control instead.  Place the following code into the label's Text property:

 

Value(txt_index.Text)-First(SortByColumns(your_excel_datasource_name,"Index",Descending)).Index

Don't forget to change the Update property for Colenne1 to reference the label you created.

 

Value(txt_index.Text)

 

Again, the key concept here is always to do your calculations in PowerApps, not in Excel.

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

 

View solution in original post

laur1RDE24
Level: Powered On

Re: link excel with formula

brilliant

just what I needed

after some adjustments, it works

just : it was difficult (for me !!!) to insert the new label type inside the correct datacard. I cheat with one 

and" ; "instead of" ,"

Value(txt_index.Text)-First(SortByColumns(your_excel_datasource_name;"Index";Descending)).Index

 

thanks a lot

View solution in original post

mdevaney
Level 8

Re: link excel with formula

I’m glad you were able to find the solution. Thanks for sharing your modified version!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 52 members 1,085 guests
Please welcome our newest community members: