ich habe mir meinen Plan bei Tasks als Beispieldatei dreimal herunter geladen und jeweils Kleinigkeiten verändert (Eigentlich sind Beispieldateien Aufgabe des Fragestellers). Diese habe ich jeweils mit vorangestelltem Tagesdatum im Format JJMMTT in einem festen Ordner gespeichert. Anschließend habe ich in Powerquery das Verzeichnis eingelesen, die Dateien nach eindeutigem Namensschema (hier einfach .xlsx) gefiltert, unnötige Spalten gelöscht, den Inhalt der Datei in einer Hilfsspalte in eine Exceldatei gewandelt, aus dem Dateinamen das Datum ausgelesen und in eine Spalte "Stand" eingefügt, erneut unnötige Spalten gelöscht, den Inhalt der Hilfsspalte extrahiert, die Überschriften aus der ersten Datenzeile ausgelesen, eine Spalte gesucht, die sich mit einem Datumsformat interpretieren lässt und dort ein Datumsformat eingestellt. Das führt dazu, dass doppelte Überschriften einen Fehlerwert erzeugen, den man anschließend wieder ausfiltern kann, abschließend noch das Feld mit dem Stand wieder umbenannt:
Code:
let
Quelle = Folder.Files("C:\Daten\OfficeLoesung\PQTasks"),
#"Gefilterte Zeilen1" = Table.SelectRows(Quelle, each [Extension] = ".xlsx"),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Gefilterte Zeilen1", "Stand", each Date.FromText("20"&Text.Start([Name],6))),
#"Andere entfernte Spalten" = Table.SelectColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Content", "Stand"}),
#"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Andere entfernte Spalten", "ExcelDatei", each Excel.Workbook([Content])),
#"Andere entfernte Spalten1" = Table.SelectColumns(#"Hinzugefügte benutzerdefinierte Spalte1",{"Stand", "ExcelDatei"}),
#"Erweiterte ExcelDatei" = Table.ExpandTableColumn(#"Andere entfernte Spalten1", "ExcelDatei", {"Name", "Data", "Kind"}, {"ExcelDatei.Name", "ExcelDatei.Data", "ExcelDatei.Kind"}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Erweiterte ExcelDatei", each ([ExcelDatei.Kind] = "Sheet") and ([ExcelDatei.Name] = "Aufgaben")),
#"Andere entfernte Spalten2" = Table.SelectColumns(#"Gefilterte Zeilen",{"Stand", "ExcelDatei.Data"}),
#"Erweiterte ExcelDatei.Data" = Table.ExpandTableColumn(#"Andere entfernte Spalten2", "ExcelDatei.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18"}, {"ExcelDatei.Data.Column1", "ExcelDatei.Data.Column2", "ExcelDatei.Data.Column3", "ExcelDatei.Data.Column4", "ExcelDatei.Data.Column5", "ExcelDatei.Data.Column6", "ExcelDatei.Data.Column7", "ExcelDatei.Data.Column8", "ExcelDatei.Data.Column9", "ExcelDatei.Data.Column10", "ExcelDatei.Data.Column11", "ExcelDatei.Data.Column12", "ExcelDatei.Data.Column13", "ExcelDatei.Data.Column14", "ExcelDatei.Data.Column15", "ExcelDatei.Data.Column16", "ExcelDatei.Data.Column17", "ExcelDatei.Data.Column18"}),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Erweiterte ExcelDatei.Data", [PromoteAllScalars=true]),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"01.02.2023", type date}, {"Vorgangsnummer ", type text}, {"Aufgabenname ", type text}, {"Bucket-Name ", type text}, {"Status", type text}, {"Priorität", type text}, {"Zugewiesen an ", type text}, {"Erstellt von", type text}, {"Erstellungsdatum", type text}, {"Startdatum", type text}, {"Fälligkeitsdatum", type text}, {"Ist wiederkehrend", type text}, {"Verspätet", type text}, {"Abgeschlossen am", type text}, {"Abgeschlossen von", type text}, {" Abgeschlossene Checklistenpunkte", type text}, {"Checklistenpunkte", type text}, {"Bezeichnungen", type text}, {"Beschreibung", type text}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"01.02.2023", "Datum"}}),
#"Geänderter Typ1" = Table.TransformColumnTypes(#"Umbenannte Spalten",{{"Erstellungsdatum", type date}}),
#"Entfernte Fehler" = Table.RemoveRowsWithErrors(#"Geänderter Typ1", {"Erstellungsdatum"})
in
#"Entfernte Fehler"
Potentiell problematisch können aus dem Hochstufen der Überschriften oder aus der Durchnummerierung der Spalten entstehen; Dies ließe sich mit etwas mehr Aufwand aber auch noch umgehen.
Das ganze lädst du anschließend in das Datenmodell, ergänzt deine Informationen mit weiteren Tabellen (zum Beispiel, um den Bearbeitungsstand in einen Wert zu wandeln) und erzeugst aus PowerPivot eine Pivottabelle mit allen benötigten Informationen (zum Beispiel Vorgangsnummer, Aufgabenname in den Zeilen; Datum in den Spalten; Bearbeitungsstand als Zahl in die Werte). Du kannst dabei natürlich auch mit den Datumsfeldern rechnen, um herauszufinden, welche Vorgänge schon länger fällig oder unbearbeitet sind.
