cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidMr
Regular Visitor

Power BI doesn't load Excel formulas properly

Hello, 

 

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:

Excel:

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.

 

BR 

David 

 

 

6 REPLIES 6
v-lid-msft
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])

 

 

1.jpg2.jpg

 


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.
Jimmy801
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

Jimmy

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

 

BR
David 

 

 

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.

 

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

 

 

Mariusz
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.

https://www.youtube.com/watch?v=dTdFt9AvDHA&t=211s

 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

 

Helpful resources

Announcements
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

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.