Forum Discussion

gazumpt's avatar
gazumpt
Frequent Visitor
9 years ago

MYSQL connection - changing database source requires M query

Hi,   I am developing Power BI reports off a Dev MYSQL Database and now want to point the reports at the UAT database. Both databases have the same tables/structure and are hosted on the same serve...
Need Help
Tips and Tricks
gazumpt's avatar
gazumpt
Frequent Visitor
9 years ago

Hi Anonymous, 

 

Thanks again for your help with this. However, I cannot provide screenshots as the data is sensitive. 

I can tell you that the issue is exactly the same using parameters as without using parameters. 

For example, I created a database parameter with Dev and UAT. 

I set the parameter to Dev and import the data without issue. The table query in Advanced Editor is below - 

let

    Source = MySQL.Database("000.000.00.00", "Dev", [ReturnSingleDatabase=true]),

    Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]

in

    Dev_ Table1

 

Then I change the parameter value to UAT and apply the changes. The data fails to refresh, giving the error 'The key didn't match any rows in the table.'

Looking at the table query, we can see why - 

let

    Source = MySQL.Database("000.000.00.00", "UAT", [ReturnSingleDatabase=true]),

    Dev_Table1 = Source{[Schema="Dev",Item=" Table1 "]}[Data]

in

    Dev_ Table1

 

The database name has been changed to UAT, but the schema name and table prefix remains on Dev. 

 

Just to mention, I am also working with SSRS on this project and can change mysql databases without issue because it does not prefix each table with the database/schema name.

With Power BI, however, I have to update each table query.

 

Thanks

Anonymous's avatar
Anonymous
Not applicable
9 years ago

gazumpt,

After creating the databasename parameter in your Query Editor, change the code in your Advanced Editor to the following:

let
    Source = MySQL.Database("000.000.00.00", ""&databasename&"", [ReturnSingleDatabase=true]),
    Table1 = Source{[Schema=""&databasename&"",Item=" Table1 "]}[Data]
in
    Table1


This way, you don't need to manually edit the schema name in Advanced Editor.

Regards,
Lydia

  • ServiceAcntGvg's avatar
    ServiceAcntGvg
    Regular Visitor
    7 years ago

    Hello,

     

    I do face this issue. This one is the critical issue for me. When ever i do changes to reports on demo and to make same changes to the production reports, i will be renaming the table names which is painfull to do every time since the schema name is not dynamic and also the table name prefixed by the schema name. i have a parameter for Server and Database name but for schema i don't have any. In advanced editor source schema is not dynamically changing as and when db/server changes. How to over come this.

     

    Is their any thing like passing schema name through parameter and also disable the prefixing schema name with table name?

     

    Thanks and Regards

    Pallavi