How to accomodate different database / schema names when writing M queries in Power BI?

Question

How to accomodate different database / schema names when writing M queries in Power BI?

Jeremy

2019年9月10日 20:15

I am working with Power BI and I need to setup a template with some input parameters, one of them being the name of the database to get data from.

To do this, I have setup a parameter (called project_database) so that users can specify the database to be used in an Query via the Parameter Manager in Power Query Editor.

I wrote a M Query that collects data from a database like so (with project_database being the input parameter):

let
    Source = MySQL.Database("127.0.0.1", project_database, [ReturnSingleDatabase=true]),
    working = Source{[Schema=project_database,Item="example_table"]}[Data],
in
    working

The problem is, this query will generate a result table with all the generated column names being based off of the actual database name (what project_database resolves to.)

For example, if the database name was xyz, the generated column names would be xyz.id, xyz.fielda, xyz.fieldb etc...)

How do I properly reference columns throughout the M query if I cannot rely on the schema portion of their names remaining consistent, since Power BI generates them based off of the schema name?

I attempted referencing the column names by

project_database  ".field"

But this is insanely tedious to do throughout the every M query. There must be a better approach?

Thanks!

Topic powerbi

Category Data Science

Jeremy · Accepted Answer · 2019年9月10日 20:15

There is no public documentation on generating column names... So hopefully I can help shed some light here.

When creating the data source with MySQL.Database, you can actually pass in a function that will be used to generate the column names, NavigationPropertyNameGenerator

First, we need to create the function that will generate these names. Basically, I want the exact same behaviour as the default except to not change the name depending on the schema / selected db.

Create a function to generate names, I created a blank query and used the below code (which will replace all instances of "abc." to "xyz.", where abc is the actual name of the schema.)

let
    ListAccumulateFunction = (state, current) =>
    let
        effectiveName = Text.Replace(current, project_database & ".", "xyz."),
        occurances = List.Count(List.FindText(state{1}, effectiveName)),
        newName = effectiveName & (if occurances > 0 then " " & Text.From(occurances + 1) else ""),
        newNamedList = List.Combine({ {effectiveName}, state{1} }),
        newColumnsList = List.Combine({state{0}, {newName} })
    in
        {newColumnsList, newNamedList},

    ColumnNameGenerator = (p, a) =>
    let 
        working = List.Transform(a, each _[TargetTableName] & " (" & Text.Combine(List.Distinct(_[SourceKeys])) & ") "),
        renamedDuplicates = List.Accumulate(working, {{}, {}}, ListAccumulateFunction)
    in
        renamedDuplicates{0}

in
    ColumnNameGenerator

Now we can use this function in throughout the project in data sources as the name generator

Source = MySQL.Database("127.0.0.1", project_database, [ReturnSingleDatabase=true, NavigationPropertyNameGenerator=ColumnNameGenerator]),

How to accomodate different database / schema names when writing M queries in Power BI?

About