Forum Discussion
Power BI doesn't load Excel formulas properly
Hello Anonymous
i was not able to reproduce the error. Power BI should access the Value-Object of a cell of Excel. So the result after a calculation. So the only thing I can thinking of is that the recalculation of the Excel is completely turned off.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
thank you guys for your feedback and help.
I dug deeper into the problem and maybe got the basic issue. The incorrect values in Power BI or Power Query show only for columns from Excel where is the formula (INDEX MATCH) but...because the source file in excel is formatted as a TABLE, the column (for example Sales Channel) shows Error - excel inconsistent calculated column formula - even though I have the same formula across all column. When I correct this Error - Restore to calculated column formula - Power Query loads the data properly.
Unfortunately, once I save, close and reopen the Excel file, there is a problem - inconsistent calculated column formula and after Refresh, Power Query again loads the column values incorrectly - shows only 1st value in a whole column. (In excel there is still the error, but the formula works properly and results are OK).
So, I would say the problem is, Power Query cannot load the data from Table column once there is an inconsistent calculated column formula.
Did you guys have the problem with loading columns with this inconsistent formula error?
Thank you for your help
BR
David
Hello Anonymous
I was not able to reproduce this error with an inconsistent calculated formula. What version of PowerBI/Excel do you use? Try to recreate the column.
Jimmy
Hi guys, thank you very much for your help... Unfortunately, I cannot just share the original data I use...and when I tried to simulate a similar dataset, the error doesn't show. I created Excel source file with one calculated column and intentionally changed the formulas in the column to create inconsistent column formula error. When I loaded the data into Power BI the affected columns loaded properly. I will try to add at least printscreens to show you the situation. As you mention, one of the solution is to create another table and then connect them with ID. I also used workaround with new Calculated column. However, it's a pity I cannot create everything in Excel and I have to keep some of the outputs and calculations in Excel and some in Power BI. Our IT Dept approved Power BI Desktop 2.74.5619.862 32-bit. I told them there is a new version released...
Thank you for your help
Best
David
Printscreens here:
https://www.dropbox.com/s/y2qwfug6xmhl6lg/Excel_Formula.JPG?dl=0
https://www.dropbox.com/s/85dsh2t2aw0zy8f/Excel1.JPG?dl=0
https://www.dropbox.com/s/pzs4pd79fihe1o9/PWBI1.JPG?dl=0
https://www.dropbox.com/s/unqecla82ysufis/PWBI2.JPG?dl=0