How to publish pbix file on service with ODBC connection

Aakash Roy 0

Hi everyone,

I'm currently working on a project that involves using Power BI to create interactive dashboards for our business data. We have a MySQL database that stores a significant amount of information, and I'd like to connect Power BI to this database using ODBC.

I know that this is possible, but I'm not quite sure how to set it up. Can someone guide me through the steps to establish a MySQL ODBC connection for Power BI on the service? I already have :

Configure the ODBC driver for MySQL on my machine.

Create a data source connection within Power BI that uses the MySQL ODBC driver.

The problem I am facing when publishing the report on the PowerBI service is, that it gets disconnected at the Gateway. I already am using the same ODBC driver which is installed on my on-premise gateway. The other thing is I can create a successful connection on the Gateway manually but am unable to get it connected to the report I published.

If you've had experience with this or have any helpful tips, please share your knowledge. Any step-by-step instructions, best practices, or recommendations for avoiding common pitfalls would be greatly appreciated.

Thank you in advance for your assistance!

GeethaThatipatri-MSFT 29,482 Reputation points Microsoft Employee

2023-11-01T01:19:49.2+00:00

@Aakash Roy Welcome to Microsoft Q&A thanks for posting your question

Can you please confirm if you are trying to connect Azure Mysql to Power BI?

Regards

Geetha
Aakash Roy 0 Reputation points

2023-11-01T09:04:08.1433333+00:00
Hi @GeethaThatipatri-MSFT , Thanks for responding swiftly. I am actually connecting to the Aws Aurora SQL database. I am now able to connect to it on the PowerBI service but the problem is I cannot make it a Direct Query. I need the table as a Direct query. I followed the below link to achieve the result:
LINK
Created the below like M query but getting an error:

let ConnectionString = "driver={MySQL ODBC 5.1 Driver};server=auroramaster-cluster.amazonaws.com;database=my_web", Source = Odbc.DataSource(ConnectionString, [HierarchicalNavigation=true]), CustomSQL = "SELECT * FROM my_web.table1", CustomQuery = Odbc.Query(Source, CustomSQL) in CustomQuery

And the error I am getting is mentioned below:

Expression.Error: ODBC: The connection string is invalid. The argument 'connectionString' was of type Table, but we expected Text or Record. Details: [Table]

Please let me know if you need any other details.

Thanks again for helping me with this issue.

Share via

How to publish pbix file on service with ODBC connection

Your answer