Error when loading a new query – OLE DB or ODBC error 10 keys were specified, but 5 values were provided

27 Sep Error when loading a new query – OLE DB or ODBC error 10 keys were specified, but 5 values were provided

Posted at 20:45h in Uncategorised by
4 Likes

OLE DB or ODBC error: [Expression Error] NN keys were specified, but N values were provided.

<English Tea>

I recently had an issue in Power BI when trying to add in an additional query.  The screenshot of the error message is displayed below.

 

 

 

 

 

 

 

 

The error isn’t hugely helpful, and it took a long time to resolve it.  In the end it turned out to be an issue with the date table, in my case it was because I was using the following formula to create the date table.

Date =

VAR MinYear = 2019

VAR MaxYear = 2025

RETURN

ADDCOLUMNS (

    FILTER (

        CALENDARAUTO()

        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

    ),

I changed the code to the following, removing the CALENDARAUTO() code, and replaced it with the following…

Date =

VAR MaxYear = YEAR(TODAY()) + 1

RETURN

ADDCOLUMNS (

    CALENDAR(DATE(2019,1,1), Date(MaxYear,12,31)),

 

Whilst the code is not functionally the exact same, it works for my customer (and is better IMHO), and very importantly, removed the error.

Ben.

Tags: