In General: I'm trying to create a sub that runs multiple queries. All of these queries are exactly similar except for one word: Column1. Therefore I'd like to use a loop and replace Column1 with a function GetColumnAct(x). The function should return Column1 when running the loop the first time, Column2 when running it the second time and so on...
Here's the code:
Sub ColumnLoop()
For x = 1 To 5
ActiveWorkbook.Queries.Add Name:=GetColumnAct(x), Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Excel.Workbook(File.Contents(""C:\Users\felix\OneDrive\Dokumente\GIZ Consultancy\Technisch\Teststruktur\FV_MA_Abfragen\Aktuelle Abfrage NEU\MA_Daten_AktuelleAbfrageNEU.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " Tabelle1_Sheet = Quelle{[Item=""Tabelle1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Höher gestufte Header"" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]" & _
")," & Chr(13) & "" & Chr(10) & " #""Spalte nach Trennzeichen teilen"" = Table.SplitColumn(#""Höher gestufte Header"", ""Mitarbeiter (eMail) TN-Projekt"", Splitter.SplitTextByDelimiter("","", QuoteStyle.Csv), {""MA.1"", ""MA.2"", ""MA.3""})," & Chr(13) & "" & Chr(10) & " #""Zusammengeführte Abfragen"" = Table.NestedJoin(#""Spalte nach Trennzeichen teilen"", {""TN Projekt""}, Tabelle1, {""TN Projekt""}, ""Tabelle1"", JoinKind.FullOuter)," & Chr(13) & "" & Chr(10) & " #""Erweiterte Tabelle1"" = Table.ExpandTableColumn(#""Zusammengeführte Abfragen"", ""Tabelle1"", {""TN Projekt"", ""Fachverbunds Nr."", ""TN Bezeichnung"", ""Fachverbundsbezeichnung"", ""MA.1"", ""MA.2"", ""MA.3""}, {""Tabelle1.TN Projekt"", ""Tabelle1.Fachverbunds Nr."", ""Tabelle1.TN Bezeichnung"", ""Tabelle1.Fachverbundsbezeichnung"", ""Tabelle1.MA.1"", ""Tabelle1.MA.2"", ""Tabelle1.MA.3""})," & Chr(13) & "" & Chr(10) & " #""Entfernte Spalten"" = Table.RemoveColumns(#""Erweiterte Tabelle1"",{""Tabelle1.TN Projekt"", ""Tabelle1.Fachverbunds Nr."", ""Tabelle1.TN Bezeichnung"", ""Tabelle1.Fachverbundsbezeichnung""})," & Chr(13) & "" & Chr(10) & " #""Tiefer gestufte Header"" = Table.Demote" & _
"Headers(#""Entfernte Spalten"")," & Chr(13) & "" & Chr(10) & " #""Transponierte Tabelle"" = Table.Transpose(#""Tiefer gestufte Header"")," & Chr(13) & "" & Chr(10) & " ColumnNext = #""Transponierte Tabelle"" [GetColumnAct(x)]," & Chr(13) & "" & Chr(10) & " #""In Tabelle konvertiert"" = Table.FromList(ColumnNext, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & " #""Entfernte Duplikate"" = Table.Distinct(#""In Tabelle konvertiert"")," & Chr(13) & "" & Chr(10) & " #""" & _
"Entfernte leere Zeilen"" = Table.SelectRows(#""Entfernte Duplikate"", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"""", null})))," & Chr(13) & "" & Chr(10) & " #""Transponierte Tabelle1"" = Table.Transpose(#""Entfernte leere Zeilen"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Transponierte Tabelle1"""
Next x
End Sub
And the function is this:
Public Function GetColumnAct(ColNumber) As String
Dim ColumnAct As String
ColumnAct = "Column" & ColNumber
GetColumnAct = ColumnAct
End Function
The only problem is the inclusion of the function GetColumnAct(x) in the formula. While it works for the Argument Name:=GetColumnAct(x), (at the beginning) it doesn't work when included in Formula:=... (at the end).
Does anybody know why this is so? Maybe I'm just getting the syntax wrong? I'm super thankful for any help provided as I'm stuck on this mini thing the whole day now...
Here's the error message I get in advanced editor (power query): invalid identifier
thanks again to everybody who looked into this!
I've now found the answer!
Instead of
#""Transponierte Tabelle"" [GetColumnAct(x)]," & Chr(13) & ""
I needed to write
#""Transponierte Tabelle"" [" & GetColumnAct(x) & "]," & Chr(13) & ""
Somebody told me at another website, so glad I've got it now!