Complex Excel Workbook into Power BI + PowerApps

[Humanity]

We have an excel book with 10s of thousands of calculations run to generate output from input into a row.

So I made a canvas app, and now must translate all the math over.  What is the most effective way to translate this over?

[Anonymous]

Import your excel wokr book into power bi and it should recognize the calculaitons 

[Humanity]

@Anonymous 

It turns all the tables into static text while it imports.  So you get the calculation results but not the calculations.

Unless you know of some way around that?

[Anonymous]

can you send an example of the calculation you are importing ? 

[Humanity]

@Anonymous 

Here are some examples.

=IF($F20="",0,IF(Insulation!AS6="",0,Insulation!AS6))

=BF34*BD34

Y31: =IF($F31="",0,SUM(BR31:BU31)-SUM(AQ31:AT31)-(IF(OR(CE31=0,CE31=""),0,(CH31*CG31/CE31)/BI31))-IF(OR(BI31=0,BI31=""),0,(BJ31-CG31)/BI31))

AI34: =IF($F34="",0,IF((BF34-CD34)*Baseline!$K$75+IF(OR(CE34=0,CE34=""),0,(CD34*CH34)/CE34)-(AG34)-(AH34)<0,0.01,(BF34-CD34)*Baseline!$K$75+IF(OR(CE34=0,CE34=""),0,(CD34*CH34)/CE34)-(AG34)-(AH34)))

[Anonymous]

The calculations dont seem that intense so I would duplicate the measures in DAX or just have the excel workbook do all the work and connect your power bi report to the output from excel.  

[Humanity]

I'm trying to avoid DAX because long term we are translating everything to SQL down the road.

Do you happen to know how to connect an Excel book to Power BI in a way that we can strill write to it using PowerApps input fields?

[Anonymous]

Hi @Humanity,

If these formulas are designed on the excel data table, you cna try to use the 'import' feature to import the whole workbook to power bi desktop. (it include current formulas)

Import Excel workbooks into Power BI Desktop 

If not, I think you need to manually convert these formulas with power bi Dax expressions logic. (since power bi data mode table not include column/row index, so these excel DAX functions not fully works on power bi desktop side)

Regards,

Xiaoxin Sheng