Pre-existing Queries

[Anonymous]

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?

[Icey]

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 · Accepted solution]

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.

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