Running the same query with new data sources

[Reed9050]

I have 100 different worksheets (1 for each cost center) with forecast values all in the same spot, but in an unusable format. I have written a query that formats everything nicely, but due to the changes that are made to the data, each sheet must be run through the query separately. I am trying to automate it so I am not spending several hours every period duplicating the query and changing the source, but cannot find a good way to do so. My initial idea is to have all the sheets in one workbook and have VBA duplicate the query and change source name for each cost center listed on a locations sheet. Is it possible to make a loop in vba that alters M code? Or is there a solution in PQ that I have not found on google.

Here is the source part of my M code and what I would like to change:

Source = Excel.Workbook(File.Contents("M:\Forecast Sales Projections - Q1.xlsx"), null, true),
#"Location 1_Sheet" = Source{[Item="[COLOR=rgb(61, 142, 185)]Cost Center 1[/COLOR]",Kind="Sheet"]}[Data],

Source = Excel.Workbook(File.Contents("M:\Forecast Sales Projections - Q1.xlsx"), null, true),
#"Location 1_Sheet" = Source{[Item="[COLOR=rgb(61, 142, 185)]Cost Center 2[/COLOR]",Kind="Sheet"]}[Data],

I want VBA to duplicate the query, and then change the Blue text. I assume that #"Location 1_Sheet" does not matter, I append all of the queries at the end so I am not worried about query/sheet names.

Thanks!

 

[sandy666]

Afaik, you cannot use vba with Power Query

 

[anvg]

Hello

Afaik, you cannot use vba with Power Query

Why not? If we have 2016 or higher...

Public Sub createPQueryCopy()
    Dim firstQuery As WorkbookQuery
    Dim nextQuery As WorkbookQuery
    Dim pSheet As Worksheet
    Set firstQuery = ThisWorkbook.Queries(1)
    Set nextQuery = ThisWorkbook.Queries.Add("Query_Next", Replace$(firstQuery.Formula, "123", "345"), "This is a vba created power query query")
    Set pSheet = ThisWorkbook.Worksheets.Add
    With pSheet.ListObjects.Add( _
        xlSrcExternal, _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Query_Next;Extended Properties=""""", _
        Destination:=pSheet.Range("A1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = "SELECT * FROM [Query_Next]"
        .ListObject.DisplayName = "Query_Next"
        .Refresh
    End With
End Sub

 

[sandy666]

Why not? If we have 2016 or higher...

it's copy Query only but not changing M-code

 

[sandy666]

If I want use it more then one time I am getting error

so I would prefer right click on Query - Duplicate - Edit - change what you want to change
btw. I can duplicate many times not only once

 

[macfuller]

Can you clarify the end result? You have 100 cost centers - do you want all the data in a single table and use a slicer to pick the cost center?
since you want to modify the source for each cost center I assume the rest of the data is formatted the same in each worksheet. Could you write vba to consolidate the data into a single table on a new sheet and import that?
you say each sheet must be run through the query separately... does that mean 100 cost centers create 100 tables in the data model, or are you wanting to append each individual sheet query into a single table?

 

[sandy666]

based on your title: "Running the same query with new data sources" maybe use parameter to change Cost Center 1 to Cost Center 2 via Data Validation list