Forum Discussion
Update db schema en masse, SQL Server Connector
In an existing .pbix file which contains many SQL Server connections, I can adapt to changes in server name or database name (e.g. tables I'm connected to are moved between servers or databases) quite easily for all queries in the file by just changing the source settings ("Data source settings" > "Change Source")
However, if the schema of an object (or many objects) is changed, I'm not seeing any easy way to update that for all queries in my file, short of manually editing this line for each and every query.
Is there a more efficient way to do this that I'm missing? We're considering changing schema on a bunch of objects which are connected to many queries in many .pbix files across the company.
Hi Anonymous ,
A couple of ideas:
1) To retrofit:
- Select all queries in Power Query that you want to change schema, copy, paste into your text editor of choice.
- In text editor, Find & Replace 'Schema="dbo"' with 'Schema="newSchema"'.
- Delete all selected queries from PQ.
- Select all in text editor and paste into PQ query pane.
2) To manage in future:
- In each new PBIX, create a query called 'schema' that is just '="dbo"'.
- For each new table you import that is likely to change schema, change 'Schema="dbo"' to 'Schema=schema'.
- You can now change the schema in all queries if required by changing the value in your new schema query.
Pete
7 Replies
Hi Anonymous ,
A couple of ideas:
1) To retrofit:
- Select all queries in Power Query that you want to change schema, copy, paste into your text editor of choice.
- In text editor, Find & Replace 'Schema="dbo"' with 'Schema="newSchema"'.
- Delete all selected queries from PQ.
- Select all in text editor and paste into PQ query pane.
2) To manage in future:
- In each new PBIX, create a query called 'schema' that is just '="dbo"'.
- For each new table you import that is likely to change schema, change 'Schema="dbo"' to 'Schema=schema'.
- You can now change the schema in all queries if required by changing the value in your new schema query.
Pete
Thank you BA_Pete !! I did not know that just copying from the query pane into a text editor would transfer the m code, and vice versa.
That might be a workable solution to make the best of this messy situation.
Hi BA_Pete ,
I'm testing this out now, and it doesn't quite seem to work as I envisioned.
Copying multiple queries out into a text editor works fine, but Power Query doesn't seem to accept a plain-text paste back into the Query pane.Hi Anonymous ,
Have you tried just clicking on the query pane then hitting Ctrl+V?
Also *might* work if you just paste the whole lot into a single new blank query. Something in my head is telling me that PQ is smart enough to split it all out into separate queries, but not able to test myself right now.
Pete