Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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,
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
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
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