Erkunden der Get & Transform-Funktionalität von Excel

Zusammenfassung

Im Zeitalter von Data Lakes und Datenbanken im Petabyte-Bereich ist es erschreckend, wie häufig ich noch Daten in Form von CSV-, Text- und Excel-Dateien erhalte. Während sich die moderne Analytik auf die neuesten Fortschritte bei maschinellen Lernalgorithmen konzentriert, ist die tägliche Plackerei der Datenanalyse immer noch ein manueller Prozess, bei dem unterschiedliche Datentypen gefunden, zusammengestellt und gehandhabt werden.

Für den Finanzanalysten kommen Daten oft als Excel-Tabelle an, aber genauso oft sind es ein Datendump in einer CSV-Datei oder eine Abfrage in einer SQL-Datenbank. Manchmal sind die Daten in einem unübersichtlichen Layout angeordnet oder enthalten nicht alle erforderlichen Komponenten für die Analyse. Die Zeit, die mit der Bereinigung dieser Daten verbracht wird, ist wertvolle Zeitverschwendung für den Analysten, doch manchmal wird diese Aufgabe als notwendiges Übel akzeptiert, das toleriert werden muss.

Was bewirkt Get & Transform?

Eine Lösung für dieses häufige Problem ist eigentlich ganz leicht zugänglich: Excel und Power BI verfügen über eine ganze Reihe von Datentransformationstools, die nur wenigen Benutzern bekannt sind, namens Get & Transform (früher bekannt als Power Query). Die Verwendung der eingebetteten ETL-Funktionalität (Extract, Transform and Load) ermöglicht es Finanzanalysten, sich nahtlos mit ihren Datenquellen zu verbinden und schneller zu Erkenntnissen zu gelangen.

Wenn wir Daten zusammenführen, um sie in Excel oder Power BI zu laden, müssen wir normalerweise einige Transformationen an den Daten vornehmen. Einige Beispiele für Datenmanipulation wären:

• Spalten entfernen,
• Filtern der Daten,
• Gruppieren der Daten,
• Pivotieren/Unpivotieren der Daten,
• Strings in Teilstrings aufteilen,
• Extrahieren von Schlüsselwörtern aus Zeichenfolgen,
• Anhängen von Zeilen aus einer anderen Tabelle und
• Verbinden von zwei Dimensionstabellen.

Im folgenden Diagramm sehen wir, dass Get & Transform diese mühsame Rolle der Vorverarbeitung der Daten vor dem Laden übernimmt.

Warum sollten Sie Get & Transform verwenden?

Warum lohnt es sich, den Umgang mit Get & Transform zu erlernen? Nun, wenn ich mir ansehe, wofür ich diese Funktionalität persönlich verwendet habe, hat sie mir einen formbaren Satz von Tools geboten für:

• Laden eines ganzen Ordners mit Textdateien in eine einzige Datentabelle
• Konvertieren exportierter Buchhaltungsdateien in ein verdauliches Layout
• Laden Sie Millionen von Verkaufszeilen direkt in Power Pivot
• Gruppieren Sie tägliche Daten in überschaubare Monatsergebnisse, bevor Sie sie in Excel importieren
• Einfügen von Daten aus einer anderen Tabelle durch Zusammenführen übereinstimmender Spalten

Wenn ich neue Daten erhalte, untersuche ich sie im Allgemeinen mit „Abrufen und transformieren“, bevor ich sie in Power Pivot lade. Auf diese Weise kann ich sehen, welche Transformationen erforderlich sein könnten, und schnell einige Pivots und Gruppierungen an den Daten vornehmen, um einen Rahmen für die Analyse zu formulieren. In vielen Fällen werde ich in diesem Stadium feststellen, dass ich mehr Daten benötige oder dass es Datenprobleme gibt. Durch die Verwendung einer Excel-basierten Plattform kann ich schnell mit meiner Datenquelle iterieren, um diese Datenanomalien zu finden.

Letztendlich hängt die Entscheidung, in Excel zu bleiben oder die Datenanalyse auf eine andere Plattform zu verschieben, von der Zielgruppe und der Wiederholbarkeit und Verbreitung der Analyse ab. Wenn meine Kunden nur Excel verwenden, verwende ich fast immer Get & Transform, um die Daten zu laden, Power Pivot, um die Analyse durchzuführen, und Excel, um die PivotTables und Diagramme zu erstellen. Für den Kunden wird sich dies nahtlos anfühlen, da alles in Excel untergebracht ist.

Wenn mein Kunde jedoch:

1. Möchte ein anderes Visualisierungstool verwenden,
2. Hat mehrere Benutzer, die die Daten aktualisieren werden, oder
3. Muss Modelle für maschinelles Lernen einsetzen,

Dann werde ich „Get & Transform“ ausschließlich für die anfängliche Datenexploration verwenden und dann das schwere Heben in R verlagern.

So greifen Sie in Excel oder Power BI auf Get & Transform zu

In früheren Versionen von Excel war Power Query ein Add-In, das installiert werden konnte, um ETL-Funktionen zu unterstützen. In Excel 2016 und Power BI sind diese Tools jedoch enger integriert. In Excel 2016 können Sie über die Registerkarte „ Daten “ und dann über den Abschnitt „Daten abrufen und transformieren “ darauf zugreifen.

In Power BI ist die Funktionalität auf der Registerkarte „ Startseite “ im Abschnitt „ Externe Daten “ vorhanden.

In diesem Artikel finden meine Beispiele in Power BI statt, aber die Oberfläche ist fast identisch mit der von Excel. Ich werde auf die Unterschiede hinweisen, wenn sie auftreten, sodass das Tutorial für beide Arten von Benutzern sinnvoll sein sollte.

1. Laden von CSV-Dateien

Zur Unterstützung dieses Tutorials habe ich einige Beispiele für Verkaufsdaten für einen fiktiven Einzelhändler erstellt, der Outdoor-Ausrüstung und -Bekleidung verkauft. In jedem dieser Beispiele werden die Daten auf unterschiedliche Weise erzeugt, um realistische Methoden des Datendumps zu demonstrieren.

Als erstes Beispiel sehen wir uns die Daten als großen Datendump in einer CSV-Datei an. Der erschwerende Faktor besteht darin, dass die Daten mit mehreren Spalten dargestellt werden, die verschiedene Geschäfte darstellen. Idealerweise möchten wir die Daten importieren und in ein besser nutzbares Layout umwandeln.

Unten ist ein Screenshot, wie die rohe CSV aussieht:

Warum sollten wir das ändern? Um die Beziehungsmöglichkeiten zu nutzen, die in diesen Anwendungen möglich sind. Wir werden dieses Spiel später in der Diskussion sehen.

Nehmen wir zunächst einmal an, dass wir die Daten als eine „schmalere und höhere“ Struktur sehen müssen und nicht als eine „breitere und kürzere“. Der erste Schritt besteht darin, die CSV-Datei zu laden; Dann beginnen wir damit, die Daten zu „unpivotieren“.

Wie Sie sehen können, ist die endgültige Struktur der Daten schmaler als die ursprünglichen Daten und viel länger. Ein weiterer Punkt ist, dass das Tool auf der rechten Seite beim Klicken auf verschiedene Aktionen eine Liste der angewendeten Schritte generiert, die zum Erstellen der Abfrage verwendet werden. Es ist wichtig zu verstehen, dass dies im Hintergrund vor sich geht, da später noch einmal darauf eingegangen wird.

„Get & Transform“ sieht und verhält sich zwischen Power BI und Excel größtenteils ähnlich. In Excel gibt es jedoch nach dem Klicken auf Schließen und Laden eine zusätzliche Eingabeaufforderung. In der folgenden Abbildung können wir umschalten, ob wir die Daten laden möchten in:

1. Eine Tabelle in Excel,
2. Eine anhand der Daten erstellte PivotTable,
3. Ein anhand der Daten erstelltes PivotChart oder
4. „Nur eine Verbindung herstellen.“

Darüber hinaus haben wir auch die Möglichkeit, diese Daten dem Datenmodell hinzuzufügen oder nicht. Durch Aktivieren dieses Kontrollkästchens werden die Daten in eine Power Pivot-Tabelle geladen. Wenn wir die Daten in Power Pivot analysieren, empfehle ich, „ Nur Verbindung erstellen “ auszuwählen und dann sicherzustellen, dass die Option „ Diese Daten zum Datenmodell hinzufügen “ ausgewählt ist. Wenn die Daten innerhalb der Excel-Zeilenbegrenzung liegen und wir unsere Analyse lieber in Excel durchführen möchten, wählen Sie einfach Tabelle .

Im nächsten Clip werden wir sehen, dass der Grund, warum wir die Daten so formatiert haben, dass sie lang und dünn sind, damit wir die Verkäufe nicht nur nach Geschäft, sondern auch nach Region und Staat analysieren können. Um diese Aufgabe zu erfüllen, importieren wir eine Tabelle, die jedes Geschäft einer Region und einem Staat zuordnet. Wir werden unten sehen, dass wir schnell Berichte erstellen können, die die Verkäufe nach diesen verschiedenen Gruppierungen zeigen.

Sie können sich vorstellen, wie diese Art von Fähigkeit zur Datentransformation in Excel oder Power BI wirkungsvoll auf alle Fälle angewendet werden kann, in denen wir dynamische Gruppierungen von Daten haben, wie zum Beispiel:

• Zusammenfassung täglicher Daten in Wochen, Monate und Quartale;
• Gruppierung von Verkaufspersonal in Abteilungen und Regionen; oder
• Zuordnung von SKUs zu Produkttypen.

Während sich dieser Artikel mit CSV- und anderen Excel-Dateien befasst, befasst sich Get & Transform mit einer Vielzahl von Datentypen. Sobald eine Abfrage erstellt wurde, kann sie im Laufe der Zeit aktualisiert werden, wenn sich die Daten ändern.

2. Umgang mit Textzeichenfolgen

Um die Fähigkeit von Get & Transform zu demonstrieren, Zeichenfolgen zu manipulieren, habe ich einen weiteren Datensatz erstellt, der eine Textdatei nachahmt, die Buchhaltungstransaktionen aus dem Hauptbuch (GL) einer Firma zeigt.

Beachten Sie, wie die Kontonummer und der Name in derselben Zeichenfolge erscheinen? In Power BI können wir die Kontonummer und den Namen mühelos in separate Felder zerlegen.

In diesem Video können Sie sehen, dass das Tool nach dem Teilen der Spalte angenommen hat, dass die neue linke Seite des Kontofelds eine Zahl sein sollte, und einen Schritt „Changed Type1“ erstellt. Da wir dieses Feld letztendlich als Zeichenfolge haben möchten, können wir den Schritt manuell unter den angewendeten Schritten löschen.

Als Nächstes nehmen wir dieselben Daten und erstellen einen Kontenplan mit Zuordnungen zu Kontokategorien.

Warum sollten wir all diese Schritte durchlaufen, um ein paar Kontonummern zuzuordnen? Ein echtes Hauptbuch kann Hunderte oder sogar Tausende von Konten umfassen. Diese schnelle Zuordnungsabfrage würde, wie wir gezeigt haben, ohne zusätzliche Arbeit auf diese Ebene skalieren.

3. Arbeiten mit verschiedenen Datenquellen

Get & Transform unterstützt viele verschiedene Datenquellen. Obwohl die Liste nicht vollständig ist, finden Sie unten einige Beispiele:

Persönlich habe ich nur etwa die Hälfte der Verbindungen auf der obigen Liste ausprobiert. Jeder der von mir verwendeten Steckverbinder war ziemlich robust; Ich bin ohne lästigen Arbeitsaufwand von Rohdaten zu Erkenntnissen gelangt. Ebenso wichtig ist, dass es als Validator zwischen unterschiedlichen Datenquellen dient und sicherstellt, dass die Endergebnisse ein standardisiertes Maß an Qualitätskontrolle aufweisen.

4. Personalisierungscode mit der M-Sprache

Im Hintergrund generiert Get & Transform jedes Mal Code, wenn wir auf eine Schaltfläche im Tool klicken oder eine Auswahl treffen. Nachfolgend finden Sie ein Beispiel dafür, wie Sie auf den Code für die von uns erstellte Kontozuordnungsabfrage zugreifen würden:

Der Code verwendet eine funktionale Sprache namens M, die für grundlegende Anwendungsfälle automatisch generiert wird. Für kompliziertere Datenverarbeitung können wir jedoch unseren eigenen Code bearbeiten und schreiben. In den meisten Fällen werde ich immer nur geringfügige Änderungen an diesem Code vornehmen. Bei komplizierteren Transformationen kann ich den größten Teil des Codes von Grund auf neu schreiben, um temporäre Tabellen bereitzustellen oder um kompliziertere Joins auszuführen.

Die Grenzen von Get & Transform

Excel stößt tendenziell an seine Grenzen, wenn Sie versuchen, mehr als eine Million Zeilen zu exportieren. In den Fällen, in denen ich Millionen von Zeilen mit Get & Transform transformiert habe, ist die einzige Möglichkeit, nicht gruppierte Zeilen zu versenden, über langwierige Hacks oder Problemumgehungen. Ich habe auch festgestellt, dass Get & Transform-Abfragen bei der Bereitstellung für mehrere Benutzer instabil sein können, insbesondere wenn Sie mehrere Datenquellen und Verknüpfungen verwenden. In diesen Fällen werde ich immer R verwenden, um das duplizierbare Datenwrangling bereitzustellen. Schließlich ist Excel nicht für eine fortgeschrittenere Datenmodellierung konzipiert. Sie können ziemlich schnell lineare Regressionen durchführen, aber darüber hinaus müssen Sie eine strengere Plattform verwenden.

Abgesehen davon finde ich, dass Excel das ist, womit sich die meisten meiner Kunden am wohlsten fühlen. Excel ist immer noch das wichtigste Werkzeug im Arsenal eines Finanzanalysten. Durch die Integration der Funktion „Get & Transform“ werden Excel und Power BI durch die Bandbreite der Datenquellen, die sie akzeptieren können, noch leistungsfähiger.