Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sandeep
Helper I
Helper I

Change connection to database

HI Team,

 

We have created few dashboards using existing Excel file. Now as a part of long term strategy we have uploaded the excel data into SQL Azure and automated the process of data refresh.

 

Now we would like to use the same .pbix file for dashboard from SQL Azure DB by changing the connection string from Excel source to SQL azure connection string.

 

The Database contains exactly the same column name and table name which was used in Excel.

 

Could you please help? Please let us know if you need more information.

 

Thanks

- Sandeep

4 REPLIES 4

I know for sure that you can't repoint an Excel connection of one type (say SQL) to another type (say Access). You have to delete the table and then re-add it with the new connector. My best guess is that it is the same here but can't be sure. 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thanks MAllington,

 

 

Here is my thoughts:

 

1. Open .pbix file and open in new Power BI Desktop window.

2. Go to Edit query --> go to advance properties --> change connection string 

 

3. Update Database name and Report dataset --> Save and close.

4. Then it should bring back all the dashboard.

 

but it seems, it is breaking the dashboard which is created using excel file which means it's not working.

 

Thanks,

Sandeep

 

@Sandeep 

The way you've described it should work. But there are a lot of steps between changing the connection and your final report so next step should be to identify where things go wrong.

 

1) Check if the query whose connection you've changed returns the expected result. If not: step through the steps to see where the error occurs.

2) If the query runs through it's still possible that the connections to other tables got broken due to format problems (i.e. if you haven't explicitely set the format in the key columns)

 

If you haven't done already, I'd start to check the lines directly following the connection string.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

When the change is in the database type (Excel->SSAS or MS SQL->SSAS  for instance) my way of doing is:

  1. backup the M expression of the old query (from advanced editor)
  2. create a new query that extracts the same dataset from the new datasource with columns names and types equal to the ones in the original query;
  3. replace  the old M expression with the new one.
  4. If things are okay, delete the new query

 

May be it is not the smartest way, but it works for me (and saves me the effort of recreating the relationships)

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.