Answered

link excel with formula

(0) Share

Report

Posted on by laur1RDE24

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (2)

mdevaney 29,989 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
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,

Was this reply helpful? Yes No
laur1RDE24 16 on at

Like (0)

Report

for you mdevaney
Meantime thanks for your interest
i try with the v-xida-msft solution ...

i try the

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report

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,

Was this reply helpful? Yes No
Verified answer

mdevaney 29,989 Moderator on at

Like (2)

Report
@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."

Was this reply helpful? Yes No
Verified answer

laur1RDE24 16 on at

Like (0)

Report

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

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No