Power BI doesn't load Excel formulas properly

Question

Hello,&nbsp;&nbsp;I would like to ask a question related to Power BI or Queries Editor. I have a database in Excel with one large table. In this table, some of the columns consist of data or values and some columns are formulas. Example of one formula is INDEX MATCH. This INDEX MATCH takes values from different table in different sheet of SAME Excel. Everything works fine in Excel however, it doesn't work in Power BI or Queries Editor.&nbsp;The columns with formulas are showing the same value in a whole column - I believe the first one.&nbsp;&nbsp;I will try to add an example:Excel:Table 1:ID#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sales Channel005&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RadarX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Europe (FORMULA = INDEX (Sales Channel) MATCH (ID#, ID# from Table 2))&nbsp;006&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ProtonZ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USA&nbsp;007&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MasterB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ASIA&nbsp;&nbsp;Power BI:&nbsp;ID#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Company&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sales Channel005&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RadarX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Europe006&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ProtonZ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Europe007&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MasterB&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Europe&nbsp;&nbsp;Do you have any ideas why do I have this error? Thank you for your help.&nbsp;BR&nbsp;David&nbsp;&nbsp;&nbsp;

anonymous · Answer

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...&nbsp;Thank you for your help&nbsp;Best&nbsp;David&nbsp;Printscreens here:&nbsp;&nbsp;https://www.dropbox.com/s/y2qwfug6xmhl6lg/Excel_Formula.JPG?dl=0&nbsp;https://www.dropbox.com/s/85dsh2t2aw0zy8f/Excel1.JPG?dl=0&nbsp;https://www.dropbox.com/s/pzs4pd79fihe1o9/PWBI1.JPG?dl=0&nbsp;https://www.dropbox.com/s/unqecla82ysufis/PWBI2.JPG?dl=0&nbsp;&nbsp;

jimmy801 · Answer

Hello&nbsp;Anonymous&nbsp;
&nbsp;
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.
&nbsp;
Jimmy

mariusz · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
You can, load all the tables to the Query Editor and use Merge queries to join all the tables.
https://www.youtube.com/watch?v=dTdFt9AvDHA&amp;t=211s
&nbsp;
Best Regards,MariuszIf this post helps, then please consider Accepting it as the solution.Please feel free to connect with me. LinkedIn 
&nbsp;

jimmy801 · Answer

Hello&nbsp;Anonymous&nbsp;
&nbsp;
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.
&nbsp;
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 tooHave funJimmy

anonymous · Answer

Hello, jimmy Mariusz&nbsp;&nbsp;thank you guys for your feedback and help.&nbsp;&nbsp;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 -&nbsp;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.&nbsp;&nbsp;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).&nbsp;&nbsp;So, I would say the problem is, Power Query cannot load the data from Table column once there is an inconsistent calculated column formula.&nbsp;&nbsp;Did you guys have the problem with loading columns with this inconsistent formula error?Thank you for your help&nbsp;BRDavid&nbsp;&nbsp;&nbsp;

Forum Discussion

Power BI doesn't load Excel formulas properly

6 Replies

Related content

If formula

formula

DAX Formula Help

Filters not working properly

DAX Formula