none
Connect to various ODBC data sources using the same query RRS feed

  • Question

  • Hi,<o:p></o:p>

    I have some MySQL Servers where Databases run. They are all using one unified data structure. So one query will work for all databases without any modification needed.<o:p></o:p>

    I would like connect with ODBCs to all my databases which use a custom query, but without maintaining copies of the same query for each single data source. I would be great if all data sources use only 1 stored query (maybe as the whole query in one variable stored in an Excel cell), so I don't have to apply changes in the query for every single data source every time.<o:p></o:p>

    Is there a way to do this with Power Query?<o:p></o:p>

    If it’s important, I'm using Power Query 2.27.4163.242 and the MySQL ODBC 3.51 Driver.<o:p></o:p>

    Thanks for reading this,<o:p></o:p>

    Felix<o:p></o:p>

    Wednesday, June 15, 2016 3:55 PM

Answers

  • You can maintain a table in Excel with all of the server paths in a column, and create a query from the table. Then turn the single query into a custom function where the server path is a parameter. Finally, add a custom column in the sources query that passes the server path as an argument into custom function. In the example below, I have turned a DimDate query into a custom function query, which takes a server path as an argument:

    (source as text) =>
    let
        Source = Sql.Databases(source),
        AdventureWorksDW2012 = Source{[Name="AdventureWorksDW2012"]}[Data],
        dbo_DimDate = AdventureWorksDW2012{[Schema="dbo",Item="DimDate"]}[Data]
    in
        dbo_DimDate

    In this case, the database name and table name are fixed, but I could add additional columns to the Excel table to specify a different database name and table name - then add additional parameters to the custom function.

    Below is the query created from the Excel table, which has a single column called ServerPath. Note the AddedCustom step.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"ServerPath", type text}}),
        AddedCustom = Table.AddColumn(ChangedType, "Custom", each DimDate([ServerPath])),
        ExpandedCustom = Table.ExpandTableColumn(AddedCustom, "Custom", {"DateKey",   "FullDateAlternateKey",  ... })
    in
        ExpandedCustom


    Wednesday, June 15, 2016 6:01 PM