Loading data from SQL Server using Power Query is fairly straightforward, and the basics are well-documented (see here for example). However there are a few advanced options, not all of which are currently shown in the online help although they are in the latest version of the Library Specification document and are visible inside the Power Query window’s own help, and not all of which are documented in a lot of detail.
Here’s what the Power Query window shows for the Sql.Database function when you show help:
Here are all of the options available:
MaxDegreeOfParallelism does what you would expect, setting the MAXDOP query hint for the SQL query that Power Query generates. For example, the Power Query query:
let
Source = Sql.Database("localhost", "adventure works dw", [MaxDegreeOfParallelism=2]),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
dbo_DimDate
Generates the query:
select [$Ordered].[DateKey],
[$Ordered].[FullDateAlternateKey],
[$Ordered].[DayNumberOfWeek],
[$Ordered].[EnglishDayNameOfWeek],
[$Ordered].[SpanishDayNameOfWeek],
[$Ordered].[FrenchDayNameOfWeek],
[$Ordered].[DayNumberOfMonth],
[$Ordered].[DayNumberOfYear],
[$Ordered].[WeekNumberOfYear],
[$Ordered].[EnglishMonthName],
[$Ordered].[SpanishMonthName],
[$Ordered].[FrenchMonthName],
[$Ordered].[MonthNumberOfYear],
[$Ordered].[CalendarQuarter],
[$Ordered].[CalendarYear],
[$Ordered].[CalendarSemester],
[$Ordered].[FiscalQuarter],
[$Ordered].[FiscalYear],
[$Ordered].[FiscalSemester]
from [dbo].[DimDate] as [$Ordered]
order by [$Ordered].[DateKey]
option(maxdop 2)
[as an aside – yes, the SQL query has an Order By clause in it. Power Query likes all of its tables ordered. It would be nice to have an option to turn off the Order By clause though, I think]
CreateNavigationProperties controls the creation of the navigation properties that allow you to browse from table to table in the Power Query Query Editor. For example, when you connect to a table in SQL Server and Power Query can see foreign key relationships between that table and other table, you’ll see extra columns that allow you to follow these relationships:
The problem is that these columns will appear as useless text columns when you load the data into a table on the worksheet or the Excel Data Model, although of course you can delete them manually using the Remove Columns functionality in the Power Query Query Editor:
Setting CreateNavigationProperties=false will stop these extra columns being created, for example:
let
Source = Sql.Database("localhost", "adventure works dw",[CreateNavigationProperties=false]),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
dbo_DimDate
I believe it should also prevent the automatic creation of relationships between tables in the Excel Data Model, but I can’t work out how to test this.
NavigationPropertyNameGenerator controls how the names for these columns are generated. There’s no public documentation about this at all, and I’m rather pleased with myself for working out how to use it. It’s quite useful because I find the default names that get generated aren’t as clear as they could be in all cases. Here’s an example of how to build more detailed, albeit more verbose, names:
let
//declare a function that combines a list of text using commas
ConcatByComma = Combiner.CombineTextByDelimiter(","),
//declare a name function
MyNameFunction = (p, a) =>
List.Transform(a, each
"Navigate from " & _[SourceTableName] & " to " & _[TargetTableName] &
" via " & ConcatByComma(_[SourceKeys]) & " and " & ConcatByComma(_[TargetKeys])),
//use this name function in Sql.Database
Source = Sql.Database("localhost", "adventure works dw",
[NavigationPropertyNameGenerator=MyNameFunction]),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
dbo_DimDate
The NavigationPropertyNameGenerator option takes a function with two arguments:
- Pattern, which appears to be a list containing all of the names of the columns on the current table
- Arguments, which is a list of records containing information about each of the navigation columns. Each record contains the following fields
- SourceTableName – the name of the source table, ie the table you’re currently on
- TargetTableName – the name of the table to navigate to
- SourceKeys – a list containing the names of the columns on the source table involved in the relationship
- TargetKeys – a list containing the names of the columns on the target table involved in the relationship
- SingleTarget – not quite sure about this, but it appears to be a logical (ie true or false) value indicating whether there is just one target table involved in all relationships
In my example above, I’ve created two functions. The first, ConcatByComma, is a simple combiner that concatenates all of the text in a list using commas (see here for more details on how to do this). The second, MyNameFunction, is the important one – it uses List.Transform to iterate over the list passed to Arguments (called a in my function) and generate the text for each column header.
Query allows you to specify your own SQL query for Power Query to use. Very useful, but there’s not much to say about this that isn’t already covered in the official help on this feature. The only thing that’s important is that if you do use this option it will prevent query folding from taking place for the rest of your query – which could lead to performance problems.
Here’s an example query:
let
Source = Sql.Database("localhost", "adventure works dw",
[Query="select distinct CalendarYear from DimDate"])
in
Source
CommandTimeout allows you to specify a query timeout as a value of type duration. The default timeout is ten minutes. Here’s an example of how to set a new timeout of one day, two hours, three minutes and four seconds using the #duration() intrinsic function:
let
Source = Sql.Database("localhost", "adventure works dw",
[CommandTimeout=#duration(1,2,3,4)]),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data]
in
dbo_DimDate
Thanks for the post. I’m trying to use both CommandTimeout and Query but it seems Sql.Database only wants between 2 and 3 arguments. Using both puts me a 4. Any ideas?
Thanks again!
Command Timeout and Query should be passed as separate fields inside the same Record value, and it’s this Record value that you pass as the third parameter. Something like this should work:
Source = Sql.Database(“localhost”, “adventure works dw”, [Query=”select distinct CalendarYear from DimDate”,CommandTimeout=#duration(1,2,3,4)])
Thank you very much. That would be it.
The option [CreateNavigationProperties=false] in the function access.database works in office 2016 but when I use it in office 365 I get an error. Is it no longer supported?
It still works for me – I’ve just tested it on the latest Office365 build of Excel 2016
That is weird when I issue the command
let
Source = Access.Database(File.Contents(“Z:\Database\AVD_DBMS_be.accdb”), [CreateNavigationProperties=true])
in
#”Source”
I get the following error
Expression.Error: 2 arguments were passed to a function which expects 1.
Details:
Pattern=
Arguments=List
but it works when I use
let
Source = Access.Database(File.Contents(“Z:\Database\AVD_DBMS_be.accdb”))
in
#”Source”
I am using MS Office 365 ProPlus version 16.0.6001.1078
I’m on Office 365 ProPlus version 16.0.0.6965.2053. Maybe it was a regression that was fixed?
Hi Chris
I’ve found this article in an effort to apply dynamic prefix’s to column names with an SQL query.
e.g.
I have the below query
let
Source = Sql.Database(“GOULBDB42”, “PALMSDB”),
#”Navigation 1″ = Source{[Schema = “dbo”, Item = “tblActivityGroup”]}[Data],
Custom = Table.PrefixColumns(#”Navigation 1″, “PALMS_STG_tblActivityGroup”) as table
in
Custom
what I want to achieve is a parameter driven variable for “PALMS_STG_tblActivityGroup” based on the value of Item in the Source function prior with a concatenated string.
e.g. in puesdo code
let
//declare a function that combines a list of text using commas
ConcatByComma = Combiner.CombineTextByDelimiter(“,”),
//declare a name function
MyNameFunction = (p, a) =>
List.Transform(a, each
“Navigate from ” & _[SourceTableName] & ” to ” & _[TargetTableName] &
” via ” & ConcatByComma(_[SourceKeys]) & ” and ” & ConcatByComma(_[TargetKeys])),
//use this name function in Sql.Database
Source = Sql.Database(“GOULBDB42”, “PALMSDB”, [NavigationPropertyNameGenerator=MyNameFunction,CreateNavigationProperties=false] ),
dbo_DimDate = Source{[Schema = “dbo”, Item = “tblActivityGroup”]}[Data],
// set a parameter value for an ETL prefix to “PALMS_STG_”
prefixStg = “PALMS_STG_”
// declare a function to get the dynamic name of the SQL table from the Source function (or elsewhere ??)
TableName = Item //or Name = _[SourceTableName]
Applyprefix = Table.PrefixColumns(dbo_DimDate , prefixStg&TableName) as table
in
Applyprefix
Have you applied your function to a similar use case ?
I’ve tried a few ways of accessing the _[SourceTableName] parameter from your function but I don’t know enough of it’s context to debug. It tells me it can’t convert a table type to text type. But I think I’m further from the solution than a typing error…..
I think I need to access the value and set a separate parameter from the each statement _[SourceTableName] from your additional MyNameFunction but I don’t know how.
Any help or another method to acheive this would be much appreciated mate
Simon
Great Blogpost Chris!,
I have been searching a lot for CommandTimeout or similar other options for this
Source = Folder.Files(“path/to/data”),
What kind of options should I put in this to avoid the publishing crashes.
Great blogpost, it helped me a lot. However I have additional question regarding the server name.
Source = Sql.Database(“localhost”, “adventure works dw”, [Query=”select distinct CalendarYear from DimDate”,CommandTimeout=#duration(1,2,3,4)])
In your example you use “localhost”. I need to be able to switch the server name when I deploy my Analysis Services Tabular model for example from Dev to Test environment.
How can I automatically switch between 2 different servers?