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

[BDIAZCUSI]

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. 

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

[v-luwang-msft]

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

[BDIAZCUSI]

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]

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

[BDIAZCUSI]

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