Advanced Options For Loading Data From SQL Server With Power Query

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:

image

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:

image

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:

image

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

image

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

8 responses

  1. 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)])

  2. 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?

      • 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

  3. 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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: