How to update all Queries in Power Query Editor

[Gasho]

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

Q1

let

    Source = Odbc.DataSource("dsn=ABC112", [HierarchicalNavigation=true]),

    ABC112_Database = Source{[Name="ABC112",Kind="Database"]}[Data],

    BAORAO01_Schema = ABC112_Database{[Name="BAORAO01",Kind="Schema"]}[Data],

    //BAORAO01_Schema = ABC112_Database{[Name=BAORAO02_Silo2,Kind="Schema"]}[Data], // Use Parameter   

    PRITY1_DIM_View = BAORAO01_Schema{[Name="PRITY1_DIM",Kind="View"]}[Data]

in

    PRITY1_DIM_View

Q2

let

    Source = Odbc.DataSource("dsn=ABC112", [HierarchicalNavigation=true]),

    ABC112_Database = Source{[Name="ABC112",Kind="Database"]}[Data],

    BAORAO01_Schema = ABC112_Database{[Name="BAORAO01",Kind="Schema"]}[Data],

    PRITY2_DIM_View = BAORAO01_Schema{[Name="PRITY2_DIM",Kind="View"]}[Data]

in

    PRITY2_DIM_View

Thanks

[v-menakakota]

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.

[Gasho]

I was away and could not respond, reviewing solutions, Thanks

[v-menakakota]

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.

[v-menakakota]

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.

[Omid_Motamedise · Accepted solution]

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.

[MarkLaf]

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

[lbendlin]

I'm getting an error message when dragging multiple tables to the TMDL view?

[Gasho]

How do I invoke TMDL view in Power BI ?

Thanks

[lbendlin]

[Gasho]

Thanks for the reply , I do not have it 😞

[lbendlin]

can you update to a more recent version of Power BI Desktop?

[Gasho]

No it is managed by mine IT org ...

Thanks

[MarkLaf]

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.

[lbendlin]

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.

[nathancwatkins]

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