Exceldateien mit Query zusammenführen

[el-rettev]

Hallo,

erstmal vielen Dank für die Hilfe, die ich hier bereits erfahren habe. Ich weiß nicht, wie viele Heulkrämpfe ich sonst bekommen hätte.
Aktuell stehe ich vor einer, für mich, nicht lösbaren Aufgabe.
Mit Power Query bin ich leider noch nicht so fit.
Ich möchte einige Hundert Dateien zusammenführen. Bekomme sie aber nicht sauber in den richtigen Aufbau.
Bin seit Stunden am Spalten verschieben, Pivotieren, Entpivotieren....ich bekomme es nicht hin und verzweifel mittlerweile.
Ich bekomme die Leerzellen und die Datenzellen nicht sauber in den gewünschten Aufbau verschoben.
2 Musterdateien für die Zusammenführung füge ich bei, sowie eine Datei, wie der Aufbau als Ergebnis aussehen sollte.
Wäre klasse, wenn man mir eine kleine Anleitung gibt, damit ich die Zusammenführung selbst an den Dateien durchführen kann.
Bin für jede Hilfe dankbar. 

Gruß

[Warkings]

Sehe ich das richtig, die einzelnen Dateien stellen jeweils eine Zeile (also ein Artikel) in der Zieldatei dar? Es kommt nicht vor, dass in einer Datei zwei Artikel drin sind.
Und die Daten stehen immer genau in der gleichen Zelle, wie in den Beispieldateien?

Und die Info in den Spalten A/B sind fest, in der Zieldatei sind das ja genau due Splatenüberschriften.

Wenn das so ist, wo ist das Problem? Rein Fleißarbeit mit Powerquere, jeweils ein Drilldown auf die einzelnen Zellen machen und in einer Tabelle zusammenführen

[el-rettev]

Ja genau, eine Datei steht für einen Artikel. Der Aufbau der Dateien ist immer gleich.
Die Zieltabelle spiegel den Aufbau, wie ich es in Power Query dargestellt haben möchte.
Es gibt keine bestimmte Datei mit den Überschriften wo die Daten hinein extrahiert werden sollen.

Was ich bisher versucht habe:
Die Spalte mit Artikelbezeichnung ganz nach links. Dann nach unten ausfüllen.
Dann versuche ich die "Anzahl" wie die anderen Bezeichnungen in Spalte D zu bekommen. Die Werte sollen dann ebenfalls in eine Spalte untereinander.
Dann die Bezeichnungen als Überschriften und die Werte die untereinander sind, sollen nebeneinander.
Ich hab als Screenshot meine Ausgangslage dargestellt.

Die Drilldowns habe ich ausgeführt jetzt zum Test ausgeführt.
Aber vllt. sehe ich den Wald vor lauter Bäumen nicht mehr. 
Ich weiss dann nicht, wie es weiter gehen soll. Wie ich was wo hinschieben muss.

[Warkings]

Anbei mein Vorschlag, so wie ich es verstanden habe, kann man sicher optimieren
Du musst den Pfad anpassen.

PS Ich empfehle M IS FOR (DATA) MONKEY: A Guide to the M Language in Excel Power Query : Puls, Ken, Escobar, Miguel: Amazon.de: Bücher

[el-rettev]

Erstmal vielen Dank für deine Hilfe. Das ist genau das Ziel, was benötigt wird.
Da ich gerade die Info von meinen Kollegen bekommen habe, dass es ab dem Jahreswechsel eine Änderung in der Struktur der Dateien gab, würde ich deinen Weg gerne individuell nachbauen können.
Hast du die Geduld mir da die wesentlichen Schritte noch zu erklären wie man zum Endziel gelangt?
Ich wäre dir super dankbar!!!!!

[Warkings]

Zwei Antworten dazu
1. Nimm meinen Literaturtipp oben, speziell Kapitel 4 (also recht früh in dem Buch) behandelt das Thema "4 Importing All Files in a Folder"
2. In der Datei ist die "überflüssige" Query tblInput. Die ist die Grundlage für die Funktion fxInput, die beim Einlesen der Dateien verwendet wird. Dort siehst Du, wie ich "fleißig" auf die jeweiligen Zellen in der Excel-Tabelle einen Drilldown gemacht habe und danach in eine Tabelle überführe. Das könnte man wahrscheinlich noch optimieren

Grundsätzlich habe ich dies hier gemacht Import data from a folder with multiple files (Power Query) (microsoft.com)
Und weil ich die ganzen Hilfsfunktionen, die Powerquery dabei erstellt, überflüssig finde, habe ich die wirklich wichtige selber gemacht, nämlich fxInput

[el-rettev]

Code:

Option Explicit
Const strSheetQ As String = "ComTool" ' Die Tabelle wird ausgelesen
Const strSheetZ As String = "Sheet1" ' Die Tabelle in dieser Datei
Const strCellQ1 As String = "M6" ' Die Zelle wird ausgelesen
Const strCellQ2 As String = "D4"
Const strCellQ3 As String = "D5"
Const strCellQ4 As String = "G5"
Const strCellQ5 As String = "H115"
Const strCellQ6 As String = "K115"
Const strCellQ7 As String = "Q115"

Public Sub Files_Read()
    Dim stCalc As Integer
    Dim strDir As String
    Dim objFSO As Object
    Dim objDir As Object
    On Error GoTo Fin
    With Application
        .ScreenUpdating = False
        .AskToUpdateLinks = False
        .EnableEvents = False
        stCalc = .Calculation
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    strDir = ThisWorkbook.Path  ' Datei im gleichen Ordner wie Auswertungsdateien
    Set objDir = objFSO.GetFolder(strDir)
    'dirInfo objDir, "*.xls", True ' Mit Unterordner
    dirInfo objDir, "*.xls"
Fin:
    With Application
        .ScreenUpdating = True
        .AskToUpdateLinks = True
        .EnableEvents = True
        .Calculation = stCalc
        .DisplayAlerts = True
    End With
    Set objDir = Nothing
    Set objFSO = Nothing
End Sub

Public Sub dirInfo(ByVal objCurrentDir As Object, ByVal strName As String, _
    Optional ByVal blnTMP As Boolean = False)
    Dim objWorkbook As Workbook
    Dim strFormula As String
    Dim lngLastRow As Long
    Dim varTMP As Variant
    For Each varTMP In objCurrentDir.Files
        If varTMP.Name Like strName And varTMP.Name <> ThisWorkbook.Name Then
            With ThisWorkbook.Worksheets(strSheetZ)
                lngLastRow = IIf(Len(.Cells(.Rows.Count, 2)), _
                    .Rows.Count, .Cells(.Rows.Count, 2).End(xlUp).Row) + 1
                With .Cells(lngLastRow, 2)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ1
                    .Offset(0, -1).Value = varTMP.Name
                End With
                With .Cells(lngLastRow, 3)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ2
                End With
                With .Cells(lngLastRow, 4)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ3
                End With
                With .Cells(lngLastRow, 5)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ4
                End With
                With .Cells(lngLastRow, 6)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ5
                End With
                With .Cells(lngLastRow, 7)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ6
                End With
                With .Cells(lngLastRow, 8)
                    .Formula = "='" & Mid(varTMP.Path, 1, _
                        InStrRev(varTMP.Path, "\")) & "[" & _
                        Mid(varTMP.Path, InStrRev(varTMP.Path, "\") + 1) & "]" & _
                        strSheetQ & "'!" & strCellQ7
                End With
                .UsedRange.Value = .UsedRange.Value
            End With
        End If
    Next varTMP
    If blnTMP = True Then
        For Each varTMP In objCurrentDir.SubFolders
            dirInfo varTMP, strName
        Next varTMP
    End If
    Set objWorkbook = Nothing
End Sub

Hallo.

Mit deiner Hilfe hätte ich einige Dateien bearbeiten können, da ich nur den Zielpfad hätte ändern müssen.
Daher vielen lieben Dank für deine Hilfe.
Da sich herausstellte, dass sich das auszulesene Formular mit der Zeit geändert hat, hätte ich den Query anpassen müssen.
Leider bin ich dort nicht ganz hintergestiegen. Zumindest nicht in der kurzen Zeit.
Hab mir nochmal Youtube Videos von Thehos, Gharani und Excelsfun angeschaut. Aber da sind die Beispiele dann doch immer sehr mustergültig. Die Videos heißen dann "cleaning messy Data". Aber die "messy Data" ist sauber in Spalten und dann doch nicht so "messy". Da wird dann nur mal eine leere Zeile entfernt, das Datumsformat angepasst und das wars.
Meine Recherche hat dann noch einen Code aus dem Jahr 2013 zum Vorschein gebracht. 
Den wollte ich noch posten, falls den jemand gebrauchen kann. Muss noch mit den individuellen Tabellennamen angepasst werden und als .xls muss ggf. die Version auf .xlsx umgeschrieben werden.