ActiveWorkbook.Queries.Add Name:=
"Übernachtungen_1_1"
, Formula:= _
"let"
& Chr(13) &
""
& Chr(10) & _
" Quelle = Excel.CurrentWorkbook(){[Name="
"Tabelle1"
"]}[Content],"
& Chr(13) &
""
& Chr(10) & _
" #"
"Gefilterte Zeilen"
" = Table.SelectRows(Quelle, each ([Monat Jahr Text] = "
""
& Monat &
""
") and ([Zahlungsart] <> "
"Selbstzahler"
")),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Duplizierte Spalte"
" = Table.DuplicateColumn(#"
"Gefilterte Zeilen"
", "
"Monat Jahr Zahl"
", "
"Monat Jahr Zahl - Kopie"
"),"
& Chr(13) &
""
& Chr(10) &
" #"
"Duplizierte Spalt"
& _
" e1"
" = Table.DuplicateColumn(#"
"Duplizierte Spalte"
", "
"Klient"
", "
"Klient - Kopie"
"),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Duplizierte Spalte2"
" = Table.DuplicateColumn(#"
"Duplizierte Spalte1"
", "
"Zahlungsart"
", "
"Zahlungsart - Kopie"
"),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Zusammengeführte Spalten"
" = Table.CombineColumns(Table.TransformColumnTypes(#"
"Duplizierte Spalte2"
", {{"
"Monat Jahr Zahl - Kopie"
", type text}}, "
"d"
&
"e-CH"
"),{"
"Monat Jahr Zahl - Kopie"
", "
"Klient - Kopie"
", "
"Zahlungsart - Kopie"
"},Combiner.CombineTextByDelimiter("
"_"
", QuoteStyle.None),"
"Zusammengeführt"
"),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Duplizierte Spalte3"
" = Table.DuplicateColumn(#"
"Zusammengeführte Spalten"
", "
"Verrechnungskey"
", "
"Verrechnungskey - Kopie"
"),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Duplizierte Spalte4"
" = Table.DuplicateColumn(#"
"Duplizierte Sp"
&
"alte3"
", "
"Betrag"
", "
"Betrag - Kopie"
"),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Zusammengeführte Spalten1"
" = Table.CombineColumns(Table.TransformColumnTypes(#"
"Duplizierte Spalte4"
", {{"
"Betrag - Kopie"
", type text}}, "
"de-CH"
"),{"
"Verrechnungskey - Kopie"
", "
"Betrag - Kopie"
"},Combiner.CombineTextByDelimiter("
"_"
", QuoteStyle.None),"
"Zusammengeführt.1"
"),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Neu angeordnete Spalten"
" = Table.ReorderColumns(#"
"Zusammengeführte Spalten1"
",{"
"Zusammengeführt"
", "
"Verrechnungskey"
", "
"Zusammengeführt.1"
", "
"Klient"
", "
"Betrag"
", "
"Listenfeldzeile"
", "
"Monat Text"
", "
"Monat Zahl"
", "
"Jahr"
", "
"Monat Jahr Zahl"
", "
"Monat Jahr Text"
", "
"Datum"
", "
"Zahlungsart"
", "
"Schulden"
", "
"Gutschein"
", "
"Bemerkungen2"
"}),"
& Chr(13) &
""
& Chr(10) & _
" #"
"Entfernte Spalten"
" = Table.RemoveColumns(#"
"Neu angeordnete Spalten"
",{"
"Listenfeldzeile"
", "
"Monat Text"
", "
"Monat Zahl"
", "
"Jahr"
", "
"Monat Jahr Zahl"
", "
"Monat Jahr Text"
", "
"Datum"
", "
"Zahlungsart"
", "
"Schulden"
", "
"Gutschein"
", "
"Bemerkungen2"
"})"
& Chr(13) &
""
& Chr(10) & _
"in"
& Chr(13) &
""
& Chr(10) & _
" #"
"Entfernte Spalten"
""