(Forum) PQ: Viele CSV-Dateien importieren und dann partiell transponieren

Quelle:  https://supportnet.de/forum/2538461/mehrere-dateien-Tabelle-zusammenfuhren-spalten-aneinander
Stand: 26.12.2024 13:00

Hier der Eröff­nungs­beitrag aus dem Forum:

Mehrere csv Dateien in eine Excel Tabelle zusam­men­führen – Spal­ten aneinan­der rei­hen

Ich habe viele hun­derte csv Dateien (in einem Ord­ner) aus einem Daten­samm­ler, die zusam­men geführt wer­den soll­ten. Die csv Dateien sind alle gle­ich aufge­baut, mit zwei Spal­ten und 450 Rei­hen. Wobei die erste Spalte immer dieselbe ist und die unter­schiedlichen Dat­en sich in Spalte zwei befind­en.

Ich benötige eine Tabelle mit:
der ersten Spalte und aus der (Datei‑1 – Spalte‑1,)
zweite Spalte aus (Datei‑1 – Spalte‑2)
dritte Spalte aus (Datei‑2 – Spalte‑2)
vierte Spalte aus (Datei‑3 – Spalte‑2)
usw.

Hat jemand für dieses Zusam­men­führen von Spal­ten (nicht rei­hen) eine Lösung?

Der Hin­weis darauf, dass das mit VBA gelöst wer­den kann, liegt bere­its vor. Und ich denke, dass der Weg für erfahrene Exe­lan­er dur­chaus gang­bar ist, sofern VBA am Arbeit­splatz erlaubt ist. Mich hat es gereizt, das Prob­lem in Pow­er Query zu lösen.

Da (wie so oft in Excel-Foren) keine Mus­ter­dat­en zur Ver­fü­gung gestellt wor­den sind und auch keine wirk­lich ein­deutige Wun­schlö­sung gegeben ist, bleibt mir nur meine eigene Inter­pre­ta­tion und unnötige Mehrar­beit.

Zu Beginn den einen oder anderen Hin­weis: Ich habe für mein Beispiel 5 kleinere csv-Files erstellt, welche mein­er Mei­n­ung nach den Muster-Vorstel­lun­gen des Fragestellers entsprechen. Diese Dateien find­en Sie hier im Blog auf meinem Serv­er. In dieser zip-Datei ist aus­nahm­sweise auch die fer­tige Ausar­beitung als *.xlsx enthal­ten (es ist ja noch Wei­h­nacht­en… 😉) aber wenn Sie die von mir erstellte­Datei unverän­dert übernehmen wollen, müssen Sie die csv-Dateien unbe­d­ingt unter dem Pfad
C:\Data\1bis5 (csv)\
spe­ich­ern. Und dort dür­fen auch nur diese csv-Files liegen. Wenn Sie ihren eige­nen Spe­icherort ver­wen­den wollen schlage ich Ihnen vor, den Pow­er Query-Import des Verze­ich­niss­es getren­nt von Hand vorzunehmen, damit es nicht zu Kon­flik­ten kommt. Eine Anpas­sung von Hand ist unter Umstän­den recht aufwändig.

Und ja, ich habe (wie recht oft) ver­sucht, das Ganze möglichst gut nachvol­lziehbar zu gestal­ten. Auch wenn es hier und da etwas Per­for­mance kostet. Erfahrene PQ-User wer­den gewiss hier und da einen anderen Weg ein­schla­gen aber mein Ziel ist nun ein­mal, die Trans­parenz in den Vorder­grund zu stellen und damit auch Ein­steigern in der Pow­er Query-Welt die Chance zu bieten, daraus zu ler­nen und darauf auch aufzubauen. Und vielle­icht fra­gen Sie sich ja, warum ich die Abfrage Max Anzahl Daten­sätze je csv erstellt habe. Ange­blich sind es ja immer gle­ich viel Zeilen in den csv-Dateien. „Ange­blich…“ 😉 Aber ich habe zu oft erlebt, dass da doch vielle­icht ein­mal der eine oder andere Aus­reißer dabei ist und dann das Ergeb­nis ver­fälscht ist.

Die hier aufge­führten M-Code­blöcke sind orig­i­nal aus der zur Ver­fü­gung gestell­ten Datei über­tra­gen wor­den. Und Sie wer­den erken­nen, dass der Pfad der zu importieren­den Dateien nicht nur 1 Mal vorkommt…

M-Code der Hilfsprogrammabfragen

// M-Code: 'Beispieldatei'
let
    Quelle = Folder.Files("C:\Data\1bis5 (csv)"),
    #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Content", "Name"}),
    Navigation1 = #"Andere entfernte Spalten"{0}[Content]
in
    Navigation1

// M-Code: 'Parameter1 (Beispieldatei)'

Beispieldatei meta [IsParameterQuery=true, BinaryIdentifier=Beispieldatei, Type="Binary", IsParameterQueryRequired=true]

// M-Code 'fx Datei transformieren'

let
    Quelle = (Parameter1 as binary) => let
        Quelle = csv.Document(Parameter1,[Delimiter=";", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])
    in
        Quelle
in
    Quelle

// 'Beispieldatei transformieren'

let
    Quelle = csv.Document(Parameter1,[Delimiter=";", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Quelle

M-Code des Hauptteils (Andere Abfragen)

// M-Code: 1bis5 csv

let
    Quelle = Folder.Files("C:\Data\1bis5 (csv)"),
    #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Content", "Name"}),
    // Geschieht automatisch durch:
    // Erweitern der Spalte 'Content' durch Klick auf das Doppelpfeil-Symbol
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(#"Andere entfernte Spalten", each [Attributes]?[Hidden]? <> true),
    // Passiert automatisch
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", 
       "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Benutzerdefinierte Funktion aufrufen1",{"Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", 
       Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    // An dieser Stelle die Abfrage duplizieren, um die maximale Anzahl der Datensätze je csv zu berechnen
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte Tabellenspalte1",{{"Column1", type text}, {"Column2", Int64.Type}})
in
    #"Geänderter Typ"

M-Code: 'Max Anzahl Datensätze je csv'

let
    Quelle = Folder.Files("C:\Data\1bis5 (csv)"),
    #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Content", "Name"}),
    // Geschieht automatisch durch:
    // Erweitern der Spalte 'Content' durch Klick auf das Doppelpfeil-Symbol
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(#"Andere entfernte Spalten", each [Attributes]?[Hidden]? <> true),
    // Passiert automatisch
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", 
       "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Benutzerdefinierte Funktion aufrufen1",{"Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "Datei transformieren", 
       Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    // An dieser Stelle die Abfrage duplizieren, um die maximale Anzahl der Datensätze je csv zu berechnen
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte Tabellenspalte1",{{"Column1", type text}, {"Column2", Int64.Type}})
in
    #"Geänderter Typ"

M-Code: 'Nur 1 csv'

let
    Quelle = Folder.Files("C:\Data\1bis5 (csv)"),
    #"Andere entfernte Spalten" = Table.SelectColumns(Quelle,{"Content", "Name"}),
    // Geschieht automatisch durch:
    // Erweitern der Spalte 'Content' durch Klick auf das Doppelpfeil-Symbol
    #"Gefilterte ausgeblendete Dateien1" = Table.SelectRows(#"Andere entfernte Spalten", each [Attributes]?[Hidden]? <> true),
    // Passiert automatisch
    #"Benutzerdefinierte Funktion aufrufen1" = Table.AddColumn(#"Gefilterte ausgeblendete Dateien1", "Datei transformieren", each #"Datei transformieren"([Content])),
    #"Andere entfernte Spalten1" = Table.SelectColumns(#"Benutzerdefinierte Funktion aufrufen1",{"Name", "Datei transformieren"}),
    #"Erweiterte Tabellenspalte1" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", 
       "Datei transformieren", Table.ColumnNames(#"Datei transformieren"(Beispieldatei))),
    // An dieser Stelle die Abfrage duplizieren, um die maximale Anzahl der Datensätze je csv zu berechnen
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Erweiterte Tabellenspalte1",{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Gefilterte Zeilen" = Table.SelectRows(#"Geänderter Typ", each ([Name] = "1.csv")),
    #"Hinzugefügter Index" = Table.AddIndexColumn(#"Gefilterte Zeilen", "Index", 0, 1, Int64.Type),
    #"Zusammengeführte Abfragen" = Table.NestedJoin(#"Hinzugefügter Index", {"Index"}, #"2 bis ## csv", 
       {"Modulo"}, "2 bis ## csv", JoinKind.LeftOuter),
    #"Erweiterte 2 bis ## csv" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", 
       "2 bis ## csv", {"2.csv", "3.csv", "4.csv", "5.csv"}, {"2.csv", "3.csv", "4.csv", "5.csv"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte 2 bis ## csv",{"Index", "Name"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"Column2", "1.csv"}})
in
    #"Umbenannte Spalten"

Und hier, beim Stich­wort last but not least find­en Sie noch einen Hin­weis, wo und wie Sie Antworten auf eventuelle Fra­gen oder auch die aus­gear­beit­ete Datei bekom­men kön­nen. Grundle­gen­des zum The­ma M-Code lesen Sie hier im Blog nach.