Anhängen mehrerer Blätter in Excel an LuckyTemplates

In diesem Tutorial wird erläutert, wie Sie eine Excel-Datei mit mehreren Blättern in eine LuckyTemplates-Tabelle importieren und öffnen.

Diese Daten-Excel-Datei wird für dieses Tutorial verwendet.

Die Datei enthält 60 Datenblätter mit demselben Layout.

Das Ziel dieses Tutorials besteht darin, alle Excel-Tabellen in LuckyTemplates zu laden und die Bond-Informationen als eine Tabelle abzurufen. Sie müssen den Sicherheitsticker abrufen und ihn in einer separaten Spalte hinzufügen. Hängen Sie sie dann alle in einer einzigen Tabelle zusammen, die die Benutzeroberfläche nutzt, um den benötigten Code zu generieren.

Inhaltsverzeichnis

• Importieren der Excel-Tabellen in LuckyTemplates
• Den Sicherheitsticker von Excel nach LuckyTemplates bringen
• Leere Zeilen entfernen
• Eine Abfrage duplizieren
• Zuweisen von Datentypen zu Spalten
• Hinzufügen einer Sicherheitsticker-Spalte von Excel zu LuckyTemplates
• Erstellen einer benutzerdefinierten Spalte für die Anleiheabfrage
• Abschluss

Importieren der Excel-Tabellen in LuckyTemplates

Öffnen Sie zunächst den Power Query-Editor und erstellen Sie mithilfe der Option „Parameter verwalten“ einen Parameter zum Speichern des Dateispeicherorts .

Klicken Sie im Feld „Parameter verwalten“ auf „Neu“ und geben Sie „ FileLocation“ als Parameternamen ein. Als nächstes legen Sie den Datentyp auf Text und die vorgeschlagenen Werte auf Beliebiger Wert fest . Kopieren Sie den Dateipfad der Excel-Datei und fügen Sie ihn in die Felder „Aktueller Wert“ ein.

Nachdem Sie auf „OK“ geklickt haben, klicken Sie auf „Neue Quelle“ und wählen Sie „Excel-Arbeitsmappe“ aus .

Wählen Sie als Nächstes die Excel-Datei aus und öffnen Sie sie. Sobald dies erledigt ist, öffnet sich der Navigator-Bildschirm und listet alle Blätter in der Excel-Datei auf.

Wählen Sie das erste Blatt aus und drücken Sie OK. Danach sehen Sie die Tabelle im Power Query-Editor.

Als nächstes müssen Sie den fest codierten Dateipfad in den Dateiparameter ändern. Öffnen Sie das Fenster „Erweiterter Editor“ und ändern Sie den Dateipfad im Quellcode in „FileLocation“ .

Am besten liegen Ihre Daten in Excel-Tabellen vor, da die Grenzen oder der Datenbereich definiert werden, bevor Sie sie in Power Query übernehmen . Da die Datei Blätter und keine Tabellen enthält, besteht die Gefahr, dass leere Spalten und leere Zeilen eingefügt werden, sodass Sie das klären müssen.

Den Sicherheitsticker von Excel nach LuckyTemplates bringen

Zu wissen, dass das Layout der Blätter festgelegt ist, hilft beim Erstellen einer Lösung, insbesondere wenn Sie die Benutzeroberfläche maximieren und zum Generieren des Codes verwenden möchten. Eine Anforderung besteht beispielsweise darin, eine Spalte hinzuzufügen, die den Wertpapierticker enthält. Wenn Sie sich die Daten ansehen, können Sie den Ticker sehen.

Mithilfe der Benutzeroberfläche können Sie den Wert des Tickers ermitteln. Klicken Sie mit der rechten Maustaste auf die Zelle und wählen Sie Als neue Abfrage hinzufügen aus .

In der Bearbeitungsleiste sehen Sie, dass auf den Tabellennamen eine auf Null basierende Zeilennummer in geschweiften Klammern folgt. Die geschweiften Klammern werden Positionsindexoperatoren genannt . Außerdem sehen Sie den Feldnamen in eckigen Klammern, die als Feldzugriffsoperatoren bezeichnet werden .

Mit diesen Codes können Sie nun den Wert extrahieren. Gehen Sie zurück zur Bond-Tabelle und rufen Sie die Bond-Daten ab. Entfernen Sie zunächst die obersten acht Reihen. Klicken Sie auf Zeilen entfernen und wählen Sie Obere Zeilen entfernen aus .

Geben Sie als Nächstes 8 in das Feld „Anzahl der Zeilen“ ein und drücken Sie OK.

Klicken Sie dann auf „Erste Zeile als Überschriften verwenden“, um die Überschriften festzulegen.

Sobald dies erledigt ist, sieht der Bond-Tisch nun so aus.

Direkt unter der Kopfzeile sehen Sie eine dünne Linie, die die Spaltenqualität darstellt. Von dort aus können Sie erkennen, dass die Spalten eine ganze Reihe von Leerzeichen enthalten. Das bedeutet, dass die Datei viele leere Zeilen enthielt.

Leere Zeilen entfernen

Um die leeren Zeilen zu entfernen, klicken Sie auf „Zeilen entfernen“ und wählen Sie „ Leere Zeilen entfernen“ aus .

Diese Transformation generiert diese Syntax:

Record.FieldValues ​​ruft alle Werte aus der aktuellen Zeile in der Tabelle als Liste ab. List.RemoveMatchingItems entfernt alle Werte in der ersten Liste, die eine Übereinstimmung in der zweiten Liste haben. Die zweite Liste enthält nur eine leere Textzeichenfolge oder eine Null. Dies sind die Werte, die aus der ersten Liste ausgeschlossen werden.

Wenn alle leeren Textzeichenfolgen und Nullen aus der Liste mit den Datensatzfeldwerten entfernt wurden, sollte die Liste leer sein und List.IsEmpty wird als True ausgewertet. Dann behält Table.SelectRows die Trues bei.

Sie sollten nicht mit einer Tabelle enden, die nur leere Zeilen enthält. Aus diesem Grund wird das Schlüsselwort not vor List.IsEmpty eingefügt . Das gibt dann eine Tabelle zurück, die nicht leere Zeilen enthält.

Neben leeren Zeilen müssen Sie auch leere Spalten entfernen. Schauen Sie sich vorher aber an, was Power Query generiert, wenn Sie eine Spalte entfernen. Wählen Sie die vierte Spalte aus und klicken Sie auf Spalten entfernen .

Nachdem Sie die Transformation durchgeführt haben, sehen Sie diese Syntax in der Bearbeitungsleiste.

Es ruft die Funktion „Table.RemoveColumns“ auf , verweist dann auf den vorherigen Schritt im Bereich „Angewandte Schritte“ und übergibt ihn als erstes Argument. Die Transformation übergibt außerdem eine Liste mit den Spaltennamen der Spalten, die Sie entfernen möchten.

Eine Abfrage duplizieren

Duplizieren Sie nun die Abfrage und wählen Sie im Bereich „Angewandte Schritte“ den Schritt „Header heraufstufen“ aus. Klicken Sie anschließend mit der rechten Maustaste auf diesen Schritt und wählen Sie „Bis zum Ende löschen“ aus .

Denken Sie daran, dass Sie den Positionsindexoperator verwenden können, um eine auf Null basierende Zeilennummer in einer Reihe geschweifter Klammern zu übergeben. Behalten Sie also nur die erste Zeile bei, indem Sie in der Bearbeitungsleiste in zwei geschweiften Klammern 0 eingeben.

Sobald Sie fertig sind, sieht der Bond-Tisch so aus.

Von dort aus können Sie einen Teil der Logik wiederverwenden, die durch den Schritt „Leere Zeilen entfernt“ erstellt wurde, um den Datensatz in eine Liste umzuwandeln und die Null zu entfernen. Gehen Sie zurück zur Bond-Abfrage und wählen Sie den Schritt „Leere Zeilen entfernen“ aus. Als nächstes kopieren Sie diesen M-Code.

Gehen Sie zurück zur duplizierten Abfrage und fügen Sie den Code in die Bearbeitungsleiste ein. Ordnen Sie dann einige Zeichenfolgen an, damit die Formel wie folgt aussieht.

Sie haben nun eine Liste mit Spaltennamen erstellt, die Sie behalten möchten. Benennen Sie die Abfrage in Column Names um .

Kehren Sie dann zur Bond-Abfrage zurück. Da Sie eine Abfrage erstellt haben, die alle Spalten enthält, die Sie behalten möchten, müssen Sie alle Spalten auflisten, die Sie in der Funktion Table.RemoveColumns ausschließen möchten .

Ändern Sie {Column4} mit derselben Syntax, die aus den Schritten „Leere Zeilen entfernt“ kopiert wurde. Ändern Sie auch {“”, null} in ColumnNames .

Anschließend müssen Sie eine Liste mit den tatsächlichen Spaltennamen aus der Bond-Tabelle übergeben. Ändern Sie Record.FieldValues(_) in Table.ColumnNames() . Geben Sie „#“Removed Blank Rows“ in die Klammern ein, um ein Tabellenreferenzargument zu übergeben.

Zuweisen von Datentypen zu Spalten

Als nächstes müssen Sie den Spalten entsprechende Datentypen zuweisen. Klicken Sie für die Spalte „Datum“ auf das Symbol neben der Überschrift und wählen Sie „Datum“ aus.

Wählen Sie für die Spalten PX_LAST und YLD_YTM_MID den Datentyp „Dezimalzahl“ aus.

Mit diesen drei Abfragen haben Sie alle Bausteine ​​erstellt, die Sie zum Entwerfen einer Lösung verwenden können, die alle Blätter in der Excel- Datei berücksichtigt. Dazu müssen Sie die Bond-Abfrage duplizieren und alle Schritte außer dem Quellschritt im Bereich „Angewandte Schritte“ löschen.

Hinzufügen einer Sicherheitsticker-Spalte von Excel zu LuckyTemplates

Im Schritt „Quelle“ können Sie alle Daten in der Excel-Datei sehen. Anstatt für jedes Blatt eine separate Abfrage zu erstellen, können Sie die Bond-Abfrage verwenden und die verschachtelten Tabellen in der Datenspalte umwandeln.

Fügen Sie zunächst den Sicherheitsticker hinzu. Wenn Sie auf den Leerraum in der Zelle einer Tabelle klicken, wird eine Vorschau des Inhalts dieser Tabelle angezeigt.

Sie müssen mithilfe einer Anweisung eine Logik erstellen, um den Sicherheitsticker abzurufen. Gehen Sie zur Abfrage Spalte2 und kopieren Sie die Adresse des Sicherheitstickers aus der Bearbeitungsleiste.

Gehen Sie als Nächstes zurück zur Bond-Abfrage und fügen Sie eine benutzerdefinierte Spalte hinzu.

Benennen Sie die Spalte Security Ticker und schreiben Sie den folgenden M-Code.

Die Formel enthält eine IF- Anweisung, die besagt, dass, wenn das Wort „Sicherheit“ in Spalte 1 gefunden wird, der Wert der Zelle aus Spalte 2 bereitgestellt wird, die daneben liegt. Andernfalls wird eine Null bereitgestellt.

Nachdem Sie auf OK geklickt haben, wird der Tabelle eine neue Spalte mit den Sicherheitsticker hinzugefügt.

Klicken Sie auf die Dropdown-Schaltfläche der Spalte „Sicherheitsticker“ und deaktivieren Sie „Null“, um alle Nullen in der Spalte zu entfernen.

Danach stehen Ihnen alle Informationen zu den Anleihen aus allen Blättern zur Verfügung. Sie müssen lediglich die in der Bond(2)-Abfrage durchgeführten Transformationen wiederholen und sie auf die verschachtelten Tabellen in der Datenspalte anwenden.

Erstellen einer benutzerdefinierten Spalte für die Anleiheabfrage

Gehen Sie zur Bond(2)-Abfrage, öffnen Sie das Fenster „Erweiterter Editor“ und kopieren Sie den folgenden Code:

Gehen Sie als Nächstes zurück zur Bond-Abfrage und erstellen Sie eine weitere benutzerdefinierte Spalte. Da Sie mehrere Transformationen über mehrere Schritte hinweg anwenden müssen, müssen Sie eine let- Anweisung verwenden. Geben Sie also „let“ ein und fügen Sie den Code aus dem erweiterten Editor ein.

Ändern Sie dann Bond_Sheet in Data , um die Tabelle in der Datenspalte umzuwandeln.

Nachdem Sie auf OK geklickt haben, wird der Tabelle eine neue Spalte hinzugefügt.

Wenn Sie auf den Leerraum in einer beliebigen Zelle dieser Spalte klicken, werden die Daten aus der Bond(2)-Abfrage angezeigt.

Sie benötigen lediglich die letzten beiden Spalten der Bond-Abfrage. Wählen Sie also sowohl die Spalten „Sicherheitsticker“ als auch „Benutzerdefiniert“ aus und klicken Sie im Menüband „Spalten entfernen“ auf „Andere Spalten entfernen“ .

Erweitern Sie die Spalte „Benutzerdefiniert“ und deaktivieren Sie das Kontrollkästchen „Originalnamen als Präfix verwenden“.

Sobald Sie fertig sind, validieren Sie die Tabelle. Wählen Sie unterhalb der Tabelle die Option „Spaltenprofilierung basierend auf dem gesamten Datensatz“ aus .

Nach dieser Transformation tritt in der Datenspalte ein Fehler auf.

Untersuchen Sie den Fehler, indem Sie auf die Spalte „Daten“ klicken und im Menüband „Zeilen beibehalten“ die Option „Fehler beibehalten“ auswählen.

Wenn Sie in der Datenspalte auf den Fehlerwert klicken, können Sie die folgende Fehlermeldung lesen:

Um die Probleme zu beheben, entfernen Sie zunächst den Schritt „Kept Errors“. Klicken Sie auf die Spalte „Daten“ und wählen Sie im Menüband „Zeilen entfernen“ die Option „ Fehler entfernen “ aus.

Setzen Sie dann die Spaltenprofilierungsoption wieder auf die obersten 1000 Zeilen. Und das ist es!

Abschluss

Mit dieser benutzeroberflächengesteuerten Lösung können Sie mehrere Blätter aus einer Excel-Datei an LuckyTemplates anhängen. Anstatt 60 separate Abfragen zu erstellen und alle Transformationen wiederholt durchzuführen, kann diese Lösung eine einzige Abfrage erstellen, die alle Transformationen durchführt. Nutzen und maximieren Sie diese Lösung, um einen großartigen Datenbericht zu erstellen .

Melissa