cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BDIAZCUSI
Frequent Visitor

Power BI to ESSBASE - "Expression Error.: The key didn't match any rows in the table"

Hello guys, I would like to ask you for help to understand this error. 

I'm connecting power bi to an Essbase cube, using Import Mode and a custom MDX query. 

 

BDIAZCUSI_0-1648563638570.png

 

BDIAZCUSI_1-1648563748592.png

 

The query we are using runs ok in smart view as a custom query.  We create an excel sheet with it, and use it as a report. But now we want to implement power bi but this is happening. 

Any suggestion is welcome. 

 

Thank you

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

Hi @BDIAZCUSI ,

This issue happend when the specific table/view/file is not available which is being used to pull data from.

And you’re probably getting this error because Power BI is trying to connect to a table or worksheet or something in your data source that has been deleted or been renamed.

Here is a blog that introduces related case:

Understanding The “The key didn’t match any rows in the table” Error In Power Query In Power BI Or E...

 

You can also get more information and solutions for similar cases from some threads in the community:

Expression Error.: The key didn't match any rows in the table

how to resolve Power BI error - the key didn't match any rows in the table

Expression.Error: The key didn't match any rows in the table

The key didn't match any rows in the table

Expression.Error: The key did not match any rows in the table

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

Hello @v-luwang-msft . 

Yes, I went through that blog and many more but there is no answer to my case. 

I'm connecting to Essbase through Import Mode using an MDX query. This query runs perfectly in Smart view (Excel component to query Essbase data). I have also modified the query and add it every Dimension reference:

First Version of the query (brings data):

SELECT
{Descendants([Periodo], 4, LEAVES)} ON COLUMNS,
NON EMPTY {Descendants([Personal], 20, LEAVES)}
ON ROWS
WHERE
([E] , [2021] , [Total_Uso], [Total_Contrato], [Total_UbicGeo], [Monto Total $] , [Presupuesto] , [Version_Final] , [Tot_Concepto], [Total_Centro_Costo])

 

Second Version of the query (also brings data):

SELECT
{Descendants([Periodo], 4, LEAVES)} ON COLUMNS,
NON EMPTY {Descendants([Cuenta].[Personal], 20, LEAVES)}
ON ROWS
WHERE
([Negocio].[E] , [Year].[2021] , [Uso].[Total_Uso], [Contrato].[Total_Contrato], [UbicGeo].[Total_UbicGeo], [Medida].[Monto_Tot] ,
[Escenario].[Presupuesto] , [Version].[Version_Final] , [Concepto].[Tot_Concepto], [Centro_Costo].[Total_Centro_Costo])

 

So until know, no solution for these problem. 

Meanwhile, I'm creating a kind of ETL calculation script to extract the data and move it to an excel, and with that file I'm creating a Report. 

but i'ts just a workaround for now. Our users what to be able to create their own reports whenever they want. 

Until now, I am very disappointed with Power Bi conexion to Essbase. :'(

 

If anyone have experienced the same issue and have any idea about what could be happening, it would be apreciated. 

amitchandak
Super User
Super User

@BDIAZCUSI , It happens when query error or the column name does not match in the next steps



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hello, Could you be more specific?. 

The query I'm using currently works and retrieve data when I run it in Smart View (excel) as a custom query. 

Is there any other reason for this error to appear in power bi?. 

 

regards

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors