MySQL ODBC Direct Query Custom Connector

Hi,

i create a MySql ODBC Direct Query Custom Connector following Microsoft Instruction

https://github.com/microsoft/DataConnectors/tree/master/samples/ODBC/SqlODBC

here the VisualStudio code, in RED the piece of code i think generate the problem:

// This file contains your Data Connector logic
section MySql_ODBC_DQ;

[DataSource.Kind="MySql_ODBC_DQ", Publish="MySql_ODBC_DQ.Publish"]
shared MySql_ODBC_DQ.Contents = (dsn as text) as table =>
let
ConnectionString = [
Dsn = dsn
],
Credential = Extension.CurrentCredential(),
CredentialConnectionString =
if (Credential[AuthenticationKind]?) = "UsernamePassword" then 
[ UID = Credential[Username], PWD = Credential[Password] ]
else if (Credential[AuthenticationKind]?) = "Windows" then
[ Trusted_Connection="Yes" ]
else
..., 
OdbcDatasource = Odbc.DataSource(ConnectionString, [

ClientConnectionPooling = true,
HierarchicalNavigation = true,
TolerateConcatOverflow = true,
HideNativeQuery = true,
SoftNumbers = true,
SqlCapabilities = [
SupportsTop = false,
Sql92Conformance = 8,
GroupByCapabilities = 4, // () 0, 1, 2, 3, 4 (SQL_GB_NO_RELATION)
SupportsNumericLiterals = true,
SupportsStringLiterals = true,
SupportsOdbcDateLiterals = true,
SupportsOdbcTimeLiterals = true,
SupportsOdbcTimestampLiterals = true,

SupportsDerivedTable = true,
LimitClauseKind = LimitClauseKind.None
],
SQLGetFunctions = [
SQL_API_SQLBINDPARAMETER = false,
SQL_CONVERT_FUNCTIONS = 0x2
]
])
in
OdbcDatasource;


// Data Source Kind description
MySql_ODBC_DQ = [
Authentication = [
Windows = [],
UsernamePassword = []
],
Label = Extension.LoadString("DataSourceLabel")
];


// Data Source UI publishing description
MySql_ODBC_DQ.Publish = [
Beta = true,
Category = "Other",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = MySql_ODBC_DQ.Icons,
SourceTypeImage = MySql_ODBC_DQ.Icons,
SupportsDirectQuery = true
];


MySql_ODBC_DQ.Icons = [
Icon16 = { Extension.Contents("MySql_ODBC_DQ16.png"), Extension.Contents("MySql_ODBC_DQ20.png"), Extension.Contents("MySql_ODBC_DQ24.png"), Extension.Contents("MySql_ODBC_DQ32.png") },
Icon32 = { Extension.Contents("MySql_ODBC_DQ32.png"), Extension.Contents("MySql_ODBC_DQ40.png"), Extension.Contents("MySql_ODBC_DQ48.png"), Extension.Contents("MySql_ODBC_DQ64.png") }
]; 

 The connector work fine but it generate a DAX query with a TOPN 501 clause, so scrolling for example a table when pass 501 row power bi generate error.

The strange thing is that in power query all rows are loaded

Here the query:

// DAX Query
DEFINE
VAR __DS0Core = 
DISTINCT('aziende'[idazienda])

VAR __DS0PrimaryWindowed = 
TOPN(501, __DS0Core, 'aziende'[idazienda], 1)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'aziende'[idazienda]

Does anyone know a way to extend the TOPN row limit?

_________________________________________________________
Marco Iacaruso
[email protected]
www.linkedin.com/in/marco-iacaruso