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.
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!
Solved! Go to 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.
Thank you.. I thought it could be referenced in M-query.
Thank you @v-eqin-msft!
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
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.
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.