Canvas App > Excel Table > Excel Calculator > Excel Table > Canvas App

(0) Share

Report

Posted on by Humanity

278

2 Excel Workbooks

1 Excel Table Workbook

1 Excel Calculations Workbook

I saw it mentioned it's possible to connect PowerApps to the Excel Table Workbook, then have that workbook connect to a separate excel workbook with calculations, which takes the Table's new data, calculates & then feeds back just the totals to the original Excel Table Workbook that updates Powerapps with calculation output.

Does this actually work? If yes, what kind of connection do I need between books? Do I need to use flows for this to work?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

eka24 20,925 on at

Like (0)

Report
Copy link

Link copied!

First of all to connect your Excel follow this video;
https://youtu.be/-Z_13J02RPU

On the issue of calculation, you cannot connect calculated columns in the excel to Powerapps. To overcome that, I suggest;
Either
1. Do the calculations in Powerapps and push or submit the results of the calculations to excel
Or
2. If for example the calculations are on table1, Connect Table2 to Powerapps and rather Link Table2 to table1 such that whatever data is received from Powerapps in Table2, it will be automatically calculated in table1
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Humanity 278 on at

Like (0)

Report
Copy link

Link copied!

Thank you @eka24 ,

However unfortunately I need PowerApps to receive the calculations totals somehow. Even an elaborate workaround would do, as long as it's automated.

Here are some of the ways I thought it may work:

- Use flows to copy the data from Table 2(The calculations) and translate it into a table that can be read
- Use Power BI to read the excel & output the results. Since you can import an entire excel workbook in Power BI, including the results of calculations. Just not the formulas themselves.
- Use a 3rd party service to translate Table 2 ( I just emailed https://www.ipushpull.com/ to see what they say.)
- SQL somehow reads Table 2 and updates a mirror copy of it

We would all love to translate the excel workbooks to SQL or PowerBI, but there are thousands of calculations in each one. So a need for a workaround here is crucial.

Was this reply helpful? Yes No
eka24 20,925 on at

Like (0)

Report
Copy link

Link copied!

If your Excel is connected to power bi, then rather show the Power bi in Powerapps;
https://youtu.be/sCGLItIwXyU
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Humanity 278 on at

Like (0)

Report
Copy link

Link copied!

@eka24

Is there a good place to find Microsoft Power BI freelancers for this?

Was this reply helpful? Yes No
eka24 20,925 on at

Like (0)

Report
Copy link

Link copied!

Post a fresh or New post and request for freelancer, you will get response
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Humanity 278 on at

Like (0)

Report
Copy link

Link copied!

@eka24

Thank you. I posted up in Power BI to see if someone there is up for it.

Was this reply helpful? Yes No
eka24 20,925 on at

Like (0)

Report
Copy link

Link copied!

If the reply resolve the issue, kindly mark it as a solution
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Humanity 278 on at

Like (0)

Report
Copy link

Link copied!

I won't abandon the post. I'm waiting to see if anyone replies to the post, & have an email out to someone who may be able to give me detailed answers as to how to do it. If I find the complete solution, I will post the explanation up for everyone with graphics.

Was this reply helpful? Yes No
Verified answer

Humanity 278 on at

Like (0)

Report
Copy link

Link copied!

@eka24

Found it !!!

It is because OneDrive does not allow for workbooks to communicate to one another in the cloud. And if you do a write to an Excel from PowerApps, then it freezes you from being able to do read immediately afterwards. Which is by all means the dumbest thing I have ever experienced in my life to date. But now we know. OneDrive made room for Google to take market share. Certainly a very nice gesture.

All you have to do is use Google & your workbook instantly works.

Was this reply helpful? Yes No