Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Power BI doesn't load Excel formulas properly



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. 

The columns with formulas are showing the same value in a whole column - I believe the first one. 


I will try to add an example:


Table 1:

ID#          Company          Sales Channel

005          RadarX              Europe (FORMULA = INDEX (Sales Channel) MATCH (ID#, ID# from Table 2)) 

006          ProtonZ            USA 

007          MasterB            ASIA 


Power BI: 

ID#          Company          Sales Channel

005          RadarX              Europe

006          ProtonZ             Europe

007          MasterB             Europe 


Do you have any ideas why do I have this error? Thank you for your help.






Community Support
Community Support

Hi @DavidMr ,


If it only happened with INDEX MATCH function, We can try to create a calculated column after create relationship between two tables as a workaround:



Sales Channel = RELATED('Table 2'[Sales Channel])





If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that we have shared?


Best regards,


Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III
Super User III

Hello @DavidMr 


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


Hello, @jimmy @Mariusz 


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





Hello @DavidMr 


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.



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






Printscreens here:



Super User II
Super User II

Hi @DavidMr 


You can, load all the tables to the Query Editor and use Merge queries to join all the tables.


Best Regards,

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.


Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors