Changing power query data source across tables

[fausttiger007]

Situation:
My model was imported from a BIM in an Azure DevOps repo... which was created in visual studio pro.

Original model uses a different SQL DB and AAS instances then the new one.
i.e. had Old Dev SQL w/ Old Dev AAS
creating new Dev and Prod instances of both... so AAS and SQL sources are different for original and newer versions. (Old Dev will be retired after working migration and dev/prod devops setup)

I've read some of the discussion around Legacy vs Power Query, but being new to TE3 maybe need clarification on options available.

All tables use a power query to load the data source...

i.e. let source= SQL/server;database
xxxxxx
yyyyyy

Although I changed the primary data source to the new server/database... I see that all tables still have the prior server/database defined in their Partition data source powerquery source statement.

Do I have to manually edit every table to adjust? Or there is some global method, replace, etc. ?

As during every PROD deploy I'm going to have to change the value from DEV to PROD database values, this can be done in a parameterization replacement script during Release, or how accomplished?

Is there an issue importing new tables with Power Query loading? Or editing them later? Although I'm looking at TE3 workflows, I see mention of TE2 users still using VS Pro to import tables? I'm new to TE.

Thanks, Tony

[fausttiger007]

I think I see part of the root of my issue... having done more power query in power bi instead of AAS Tabular model.
I see that the Source=xxxxx in Tabular Model from AAS is actually a Name, which defaulted to the SQL server and database name with periods replaced by dots..
i.e.
actual source
'SQL/xxxx.database.windows.net;dm' becomes
name
'SQL/xxxx database windows net;dm' (spaces instead of periods).

The same name is used in the Power Query Source=xxxx

I changed the Address and database name in properties of the Data Source (data sources folder)...
but when I renamed the source 'name' itself, it did Not change that name in all the Table partition power query Sources... and hence all the data connections to the tables broke.
(there was a warning in Tabular Editor about changing the name potentially breaking some formulas)

correcting now.

so the question becomes:

1. is there a way to reName a data source and have that change reflected everywhere? (a name based on a different server/db is misleading)
2. then during deployment we simply script the the server and database for that name to be replaced in its single place?

Tony

[fausttiger007]

Solved.
For our (single) Structured Data Source...
Azure Analysis Services deployed (not SSAS or Power BI Premium deploy)...

As I'm using the Analysis Services API to do our data refreshes (ProcessFull called from Azure Function)... I also had to extend the Release script to redundantly set database, server, and credential{username, password} directly, as well same values in ConnectionString... hence additional items added to script run at Dev or Prod deploy task in Release.

On following script...Remove line breaks for actual execution by single echo statement (breaks added for readability)

START SCRIPT

echo (Model.DataSources[0] as StructuredDataSource).ConnectionString = Environment.GetEnvironmentVariable("SQLDWConnectionString");

(Model.DataSources[0] as StructuredDataSource).Password = Environment.GetEnvironmentVariable("SQLDWPassword");

(Model.DataSources[0] as StructuredDataSource).Username = "$(SQLDWUsername)";

(Model.DataSources[0] as StructuredDataSource).Server = "$(SQLDWServer)";

(Model.DataSources[0] as StructuredDataSource).Database = "$(SQLDWDatabase)"; > SetConnectionStringFromEnv.cs

END SCRIPT

This requires some extra variables, of which I put Password and ConnectionString in environment variables for security. The others (username, server and database name) I did not secure further.
I'll probably put those variables into the azure keyvault as we have most secure items in our environment.

BTW, I also apparantly have SchemaCheck working with (single) Structured DataSource.

Tony