DataConnectors
DataConnectors copied to clipboard
Filter retrieves all data within sources when incorrect column names are used to filter on.
We produce a SQL engine with ADO.NET provider which already runs with Power BI on approximately 60 platforms such as an online accountancy product Exact Online, on which we use the XML and REST API. We are happy to be able to create connectors now so we can improve the user experience of our users.
The bug
Reproduction scenario from Power BI:
- Get Data
- Choose Invantive for Exact Online:
- Run a query with a non-existing column in the filter. In the following query, Name doesn't exist (actually there was a typo):
let
Source = Invantive.Schema(),
meTable = Source{[Name = "Me", ItemKind="Table"]}[Data]
in
meTable
Expected result:
I would have expected an error message telling the Key can't be found, just like you get when running the query from Visual Studio in the M Query Output.
It seems that Power BI just ignores the Name filter and retrieves [Data] from all tables in the schema.
PQ code used:
shared Invantive.Database = () as table =>
let
ConnectionString = GetConnectionString(),
AdoDotNetDataSource = AdoDotNet.DataSource("Invantive", ConnectionString, []),
Database = AdoDotNetDataSource{[CollectionName = "Tables"]}[Data]()
in
Database;
[DataSource.Kind="Invantive", Publish="Invantive.UI"]
shared Invantive.Schema = () as table =>
let
Source = Invantive.Database(),
smallSet = Table.SelectColumns(Source,{"table_catalog", "table_schema", "table_name"}),
rename1 = Table.RenameColumns(smallSet,{"table_catalog", "Catalog"}),
rename2 = Table.RenameColumns(rename1,{"table_schema", "Schema"}),
rename3 = Table.RenameColumns(rename2,{"table_name", "TableName"}), // here was the typo! It should have been Name
addItemKind = Table.AddColumn(rename3, "ItemKind", each "Table"),
adddItemName = Table.AddColumn(addItemKind, "ItemName", each "Table"),
addLeaf = Table.AddColumn(adddItemName, "IsLeaf", each true),
addDataTables = Table.AddColumn(addLeaf, "Data", each GetDataFor([Catalog], [Schema], [Name]), type table),
NavigationSchema = Table.ToNavigationTable(addDataTables, { "Catalog", "Schema", "TableName" }, "TableName", "Data", "ItemKind", "ItemName", "IsLeaf")
in
NavigationSchema
;