Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
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.
@BDIAZCUSI , It happens when query error or the column name does not match in the next steps
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |