cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Pre-existing Queries

Hello All

 

I inherited a Power BI Report from someone who left a few years ago. The current report pulls from an SQL database that is "On-Prem."

 

We are eventually going to discontinue the On-Prem source and pull the same type of data from the same database which is being moved to Azure.

 

I noticed the current Report has a ton of queries the person created and it will be difficult to trace each one to figure out what they were doing. What is the correct way to do this when moving from On-Prem to Azure----do I just point the report to the new data source in Azure? Being that it is the same database, should all the existing queries in the report run as normal when we pull in the new data?

1 ACCEPTED SOLUTION
edhans
Super User
Super User

You may have to alter the SOURCE and NAVIGATION steps, but after that, it should work ok. For example:

let
    Source = Sql.Database("ServerName", "DatabaseName"),
    dbo_TheTableIWant_SQL = Source{[Schema="dbo",Item="TheTableIWant_SQL"]}[Data],

Those are the first two lines in each SQL table that is pulled into Power BI from On-Prem.

Connect to one of these tables manually using the Azure SQL Connector. That will give you that M code. Then you can just modify the Source and Table step. Note that the step here is called "dbo_TheTableIWant_SQL" - but in the Query steps it is called "Navigation."
There is nothing magical about the name of the step, as long as if you change it you also change the 3rd step to reference the new name you give it.

 

To quickly find these queries you need to change:

  1. In the Query editor, select the first one.
  2. Scroll down and Shift-Click the 2nd one.
  3. Press CTRL-C
  4. In a good text editor like NotePad++, select Paste.

You can quicly look for any queries that have the Sql.Database() function to know which ones to convert. 

 

As a quick test though, I just did an Azure SQL connection to my on-prem database and it worked, so I think the same Sql.Database() function is used, so you might be able to just use the Data Source Settings icon, input the new server name, keep the DB the same, and click Ok.

edhans_0-1600294817485.png

Worst case though is the manual process above. Far FAR easire than converting from Excel to SQL, or SharePoint Lists to Azure SQL.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please let us know whether what @edhans mentioned is helpful.

 

If it is, please accept his reply as solution to your question so that people who may have the same question can get the solution directly.

 

If not, please give us more details.

 

 

Best Regards,

Icey

edhans
Super User
Super User

You may have to alter the SOURCE and NAVIGATION steps, but after that, it should work ok. For example:

let
    Source = Sql.Database("ServerName", "DatabaseName"),
    dbo_TheTableIWant_SQL = Source{[Schema="dbo",Item="TheTableIWant_SQL"]}[Data],

Those are the first two lines in each SQL table that is pulled into Power BI from On-Prem.

Connect to one of these tables manually using the Azure SQL Connector. That will give you that M code. Then you can just modify the Source and Table step. Note that the step here is called "dbo_TheTableIWant_SQL" - but in the Query steps it is called "Navigation."
There is nothing magical about the name of the step, as long as if you change it you also change the 3rd step to reference the new name you give it.

 

To quickly find these queries you need to change:

  1. In the Query editor, select the first one.
  2. Scroll down and Shift-Click the 2nd one.
  3. Press CTRL-C
  4. In a good text editor like NotePad++, select Paste.

You can quicly look for any queries that have the Sql.Database() function to know which ones to convert. 

 

As a quick test though, I just did an Azure SQL connection to my on-prem database and it worked, so I think the same Sql.Database() function is used, so you might be able to just use the Data Source Settings icon, input the new server name, keep the DB the same, and click Ok.

edhans_0-1600294817485.png

Worst case though is the manual process above. Far FAR easire than converting from Excel to SQL, or SharePoint Lists to Azure SQL.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors