Changing data source with another structure

[Anonymous]

Hi guys!

I have a Pbix file connected via Direct Query to DataBase "A" (SQL DataBase) .

Let's say (to simplify) that DataBase "A" has just a table with 20 columns and just 14 of its colums are used in the model (measures, visualization,...) the others 6 colums are not being used.

The thing is when I try to "Transform Data -> Data source Settings -> Change source" and try to connect this Pbix file to another SQL Database with 21 columns (ALMOST the same structure) , I have an error and cannot see any data.

Why I need this? I'm working in a company that usually add new fields in some tables (for example, a new field in the Customer Table) and I'm cooking some reports and I realized that when IT guy creates a new field, I cannot connect the file as usual. What I'm doing now is rebuild the report again, it's a mess!

Thank your for reading this and thank your for your help!

[az38]

Hi @Anonymous 

why dont you just describe only these useful 14 fields in the SELECT..from SQL-statement?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[mwegener]

Hi @Anonymous ,

I always change the connection string in the Power Query editor.

We have parameterized them in our templates.

To reduce the points of change, I use the "One Data Source" approach.

https://powerpivotpro.com/2017/05/power-query-magic-part-1-always-good-references/

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Thank you for your answer @mwegener. 

Unfortunately It seems it's not available in DirectQuery mode connection. (It's really interesting for importation mode, though)

[mwegener · Accepted solution]

Hi @Anonymous ,

parameters are also available in direct query mode.

I recommend to activate this option.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Priceless help. Thank you, it was just what I needed!

I'm not advanced PowerBI user, trying to improve my skills day after day. Thank you!