SAP ODBC.DataSource SupportsDerivedTable

[pbi_user_rdg]

I need to configure ODBC connection to store data in DirectQuery mode in PBI. I have two problems:

1. Per MS documentation, SupportsDerivedTable option can be used in ODBC.DataSource in M-Query to make the query a DirectQuery mode.

Here's my M-Query:

let
Source= Odbc.DataSource("dsn=localhost SQL Server", [SupportsDerivedTable = true])
in
Source

I get this error:

Expression.Error: The provided options are not valid.
Details:
[List]

2. Where do I enter my SQL statement in ODBC.DataSource in M-Query? The current statement (without SupportsDerivedTable) gives a list of SQL objects. 

Thank you!

[pbi_user_rdg]

Thank you.. I thought it could be referenced in M-query. 

[pbi_user_rdg]

Thank you @Anonymous! 
How can one use the options available only as extensibility context?
Here's my M-query that does not store the table in DirectQuery mode:

let
Source =
Odbc.DataSource(
"dsn=localhost SQL Server",
[HierarchicalNavigation=true, SqlCapabilities=[SupportsDerivedTable = true]
]
),
#"DW_Database" =
Source{
[Name="dbname",Kind="Database"]
}[Data],
dbo_Schema =
#"DW_Database"{
[Name="dbo",Kind="Schema"]
}[Data],
DimDate_Table =
dbo_Schema{[Name="DimDate",Kind="Table"]}[Data]
in
DimDate_Table

[Anonymous · Accepted solution]

Hi @pbi_user_rdg ,

According to the official document—— The supported options records fields fall into two categories—

1. Are public and always available. The record can contain the following fields:

HierarchicalNavigation 

CreateNavigationProperties 

ConnectionTimeout 

CommandTimeout 

SqlCompatibleWindowsAuth 

So for example:

Source = Odbc.DataSource("dsn=Test", [ConnectionTimeout=#duration(0,0,10,0)])

2. Are only available in an extensibility context.

Since SupportsDerivedTable option is not public ,it is used in Overriding SqlCapabilities, you could not directly use it.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.