1
$\begingroup$

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!

$\endgroup$

1 Answer 1

0
$\begingroup$

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.

  1. 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
  1. 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]),
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.