Forum Discussion
How to update all Queries in Power Query Editor
In Power Query Editor, I have bunch of Queries/Views under Queries [10] that I would like to modify with one shot to replace schema 01 with schema 02 under Naviagation
I created Parameter BAORAO02_Silo2 = BAORAO02, and it works on single query, but how can I insert that paremeter in all my queries without editing them one by one
e.g. from Advanced Editor
You can create a Parameter for Schema Name
Name: SchemaName
Value: "BAORAO02_Silo2" (or any other schema name)Or you can create a Central Function to Retrieve the Schema
Go to Power Query Editor → New Query → Blank Query
Open Advanced Editor and paste:
(schemaName as text) =>
let
Source = Odbc.DataSource("dsn=ABC112", [HierarchicalNavigation=true]),
ABC112_Database = Source{[Name="ABC112",Kind="Database"]}[Data],
Selected_Schema = ABC112_Database{[Name=schemaName,Kind="Schema"]}[Data]
in
Selected_Schema
Rename this query to GetSchema.
15 Replies
I believe you can do this in TMDL View (preview). Once enabled and opened up:
1) select all applicable queries
2) drag into the script window, which will drop all the query definitions into the editor including M script
3) make whatever changes you want (editor provides ootb find/replace)
4) hit Apply at the top
5) refresh
E.g. here is the TMDL for a super simple table. I added the highlighted via TMDL, hit Apply, hit refresh, and change flowed through
I'm getting an error message when dragging multiple tables to the TMDL view?
I'm definitely no expert and have only just started tooling around a bit with TMDL, so not sure what might be the issue.
I was able to duplicate a similar-looking set of errors by having my top table definition at a different indentation than my others.
Perhaps try dropping in fewer at a time to pinpoint what is triggering the indent messup?
It is in preview, so it could just have trouble performing with models beyond the very simple and small one I just tested with.
You can create a Parameter for Schema Name
Name: SchemaName
Value: "BAORAO02_Silo2" (or any other schema name)Or you can create a Central Function to Retrieve the Schema
Go to Power Query Editor → New Query → Blank Query
Open Advanced Editor and paste:
(schemaName as text) =>
let
Source = Odbc.DataSource("dsn=ABC112", [HierarchicalNavigation=true]),
ABC112_Database = Source{[Name="ABC112",Kind="Database"]}[Data],
Selected_Schema = ABC112_Database{[Name=schemaName,Kind="Schema"]}[Data]
in
Selected_Schema
Rename this query to GetSchema.You can just select all of the queries, Copy them, Paste them into notepad,, Five and replace whatever you're looking for, and then copy and paste back into Power Query.
--Nate
The correct way is to use parameters for that.
The McGyver way is to copy all queries, paste them into Notepad++ or better, mass replace the value, then delete them in Power Query and paste them back from the editor.
Hi Gasho ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Gasho ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.Thank you.
I was away and could not respond, reviewing solutions, Thanks
Hi Gasho ,
Could you please confirm whether you have reviewed the solutions provided above.I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
