Dimensionstabelle in einer Textdatei: Power Query-Lösung

In diesem Blog-Beitrag gehe ich auf Problem Nr. 2 aus dem laufenden Problem der Woche im ein, wo wir besprochen haben, wie man unordentliche Daten in eine saubere Dimensionstabelle sortiert. Dies ist die zweite Auflage dieser neuen Initiative, die wir bei LuckyTemplates veranstalten. Diese Serie liegt mir besonders am Herzen, weil sie jedem die Möglichkeit gibt, regelmäßig mehr zu üben. Das vollständige Video dieses Tutorials können Sie unten in diesem Blog ansehen.

Jeden ersten Mittwoch im Monat gibt es eine DAX-Challenge und am dritten Mittwoch eine Power-Query-Challenge .

Dies ist eine großartige Gelegenheit, diese Sprachen zu erforschen, zu entdecken und Neues darüber zu lernen, die Sie in LuckyTemplates nutzen müssen.

Im LuckyTemplates-Forum finden Sie eine Kategorie zum Problem der Woche.

Das Hauptaugenmerk liegt auf dem Prozess und nicht so sehr auf dem Ergebnis.

Wenn es Ihnen gelingt, ein Problem in kleinere Teile zu zerlegen, können Sie jedes Problem, mit dem Sie konfrontiert sind, lösen.

Melissa de Korte

Brian sprach zuvor über eine Technik namens Rubber Duck Debugging . Wenn Sie das verpasst haben, schauen Sie sich unbedingt sein Video an. Es kann Ihnen helfen, wenn Sie bei diesen Herausforderungen nicht weiterkommen.

Betrachten wir zunächst die anstehende Aufgabe. Wir haben eine unordentliche Textdatei, die wir in eine richtige Dimensionstabelle umwandeln müssen.

Wenn ich mir nun Daten wie diese ansehe, geht es größtenteils um die Textbereinigung, das Entfernen unerwünschter Zeichen, das Trimmen, die Großschreibung von Wörtern und so weiter.

Wir müssen aber auch alle Zeilen aus diesen Stapeldaten abrufen und sie für jedes Land in eine einzelne Zeile umwandeln. Ich nenne es Stapeldaten, weil alle Feldnamen für jedes Land in einer einzigen Spalte wiederholt werden.

Ich habe einige Erfahrung mit Excel und eine der leistungsstärksten Funktionen sind meiner Meinung nach Pivot-Tabellen .

Inhaltsverzeichnis

• Pivot-Tabellen in Excel
• Verwenden von Power Query zum Erstellen einer Dimensionstabelle
• Benutzerdefinierte Textreinigungsfunktion
• Die Daten drehen
• Abschluss

Pivot-Tabellen in Excel

Mit Pivot-Tabellen kann ich Daten Segment für Segment anzeigen. Je nachdem, was Sie im Zeilenabschnitt platzieren, fasst eine Pivot-Tabelle jedes Vorkommen dieses Segments in einer einzigen Zeile zusammen.

Sie können dies dann noch weiter aufteilen, indem Sie Felder in den Spaltenbereich ziehen.

Ich habe ein Beispiel erstellt, wie das aussieht. Hier haben wir im Wesentlichen die gleichen Daten wie in der Textdatei.

Und auf der nächsten Seite habe ich eine leere Pivot-Tabelle erstellt.

Was ich Ihnen nicht gezeigt habe, ist, dass ich hier drei Spalten anstelle der zwei habe, die ich Ihnen bereits gezeigt habe.

Ich werde mein Segment in die Zeilen ziehen.

Da Spalte 1 die Feldnamen enthielt, ziehe ich diese in den Spaltenabschnitt.

Spalte 2 enthielt alle Feldwerte, also ziehe ich sie in den Wertebereich.

Hier sehen wir, dass die Pivot-Tabellen keine Textzeichenfolgen verarbeiten können. Es zählt sie, zeigt aber, dass wir für jedes Feld einen einzigen Wert haben.

Werfen wir nun einen Blick auf das Segment, das ich erstellt habe. Also gehe ich zurück zu den Daten und blende meine Spalte ein. Sie können sehen, dass es sich lediglich um eine Indexnummer handelt, die jeden einzelnen Datenblock identifiziert, der noch übereinander gestapelt ist.

Für mich wird die Schlüsseltransformation darin bestehen, die Daten wieder in das Tabellenformat umzuwandeln.

Im Forum habe ich andere Möglichkeiten gesehen, damit umzugehen. Es gibt neben der Verwendung einer Pivot-Aktion noch andere Möglichkeiten, zu einem Tabellenformat zu gelangen, und diese funktionieren genauso gut. Wenn Sie daran interessiert sind, besuchen Sie das Forum und beginnen Sie mit der Erkundung.

Verwenden von Power Query zum Erstellen einer Dimensionstabelle

Lassen Sie uns die Leistungsabfrage durchgehen und meine Lösung überprüfen.

Persönlich finde ich, dass die Benutzeroberfläche den Großteil des M-Codes hervorragend für uns schreibt. Daher tendiere ich dazu, meine Abfragen so weit wie möglich über die Benutzeroberfläche zu gestalten.

Sobald die Abfrage das tut, was ich tun soll, gehe ich in den erweiterten Editor und untersuche den M-Code, um zu sehen, ob ich ihn ändern kann. Mal sehen, wie das aussieht.

Dies ist meine Basisgruppe, die über die Benutzeroberfläche erstellt wird.

Sie können sehen, dass die angewendeten Schritte auf der rechten Seite viele Schritte enthalten.

Das ist an sich kein Problem, aber wenn man sich nur diese Schritte ansieht, erkennt man, dass es viele Transformationen gibt, die sich zusammenfassen lassen.

Öffnen wir den erweiterten Editor.

Wir sehen, dass diese Abfrage 31 Schritte hat.

Ich habe dieser Abfrage mit den 31 Schritten auch einige Kommentare hinzugefügt, sie jedoch in Abschnitte unterteilt.

Als erstes habe ich den Schritt „Typ ändern“ entfernt. Ich schlage vor, eine benutzerdefinierte Funktion zu erstellen, um alle diese Texttransformationsschritte durchzuführen.

Es gibt hier viele Kommentare, aber es gibt nur zwei Dinge, die ich in diesem Blogbeitrag hervorheben möchte. Erstens gibt es die benutzerdefinierte Funktion zur Textbereinigung .

Der zweite Schritt ist der Pivot-Schritt, um diese Art von Daten wieder in ein ordnungsgemäßes Tabellenformat umzuwandeln .

Benutzerdefinierte Textreinigungsfunktion

Kehren wir zu den frühen Phasen der Erstellung meiner Abfrage zurück, wo ich alle diese gruppierten Schritte zum Bereinigen dieser Texte hatte: Spalte 1 und die zusammengeführte Spalte.

Ich habe auch eine zusätzliche benutzerdefinierte Spalte hinzugefügt. Sein einziger Zweck besteht darin, meine benutzerdefinierte Textbereinigungsfunktion aufzubauen . Ich habe das in der zusammengeführten Spalte aufgerufen.

Auf diese Weise muss ich die Funktion nicht auf einmal schreiben, sondern baue sie Schritt für Schritt auf und füge eine neue Transformation hinzu, nachdem ich die Ergebnisse des vorherigen Schritts überprüft habe.

Schauen wir uns den M-Code für die Textreinigungsfunktion an.

Wie Sie sehen, habe ich mehrere Schritte. Als ich diese Textfunktion erstellte, wechselte ich zwischen den Abfragen hin und her, um mir die Ergebnisse anzusehen und zu sehen, was ich erstellen und was als nächstes korrigieren sollte.

Mit diesem Ergebnis habe ich alle Transformationen durchgeführt, die ich brauchte. Einige der M-Funktionen, die ich hier verwendet habe, wurden von der Benutzeroberfläche bereitgestellt, als ich meine erste Abfrage erstellt habe, z. B. der Text. Trimmfunktion. Die anderen genutzten Funktionen waren jedoch nicht vorhanden.

Wenn Sie mit ihnen nicht vertraut sind, können Sie alle M-Funktionen im M-Formel-Leitfaden online nachschlagen . Dies ist der Link , zu dem Sie gehen müssen.

Sie finden einen Abschnitt, der sich mit Leistungsabfragen und Funktionen befasst.

Wenn Sie nach unten scrollen, finden Sie den Abschnitt zur Textfunktion. Jeder Abschnitt beginnt mit einer Übersicht. Es gibt eine Liste aller Textfunktionen in der Power-Query- und Formel-M-Sprache.

Wenn Sie nach einer bestimmten Transformation suchen, können Sie diese hier nachschlagen.

Die Daten drehen

Der zweite Teil, den ich beim Erstellen dieser Dimensionstabelle hervorheben wollte, ist die Pivotierung der Daten selbst. Schauen wir uns auch das genauer an.

Ich begann mit dem Hinzufügen eines Index. Ich habe diesen Index aktualisiert, um die Datenblöcke richtig zu segmentieren. Dazu habe ich die Indexnummer für jede Zeile zurückgegeben, in der sich in Spalte 1 der Text „Land“ befand, und diesen Wert dann eingetragen.

Alles, was wir tun müssen, ist, die Daten selbst zu schwenken. Auf der Registerkarte „Transformieren“ finden Sie die Pivot-Spalte. Klicken Sie bei ausgewählter Spalte 1 auf „Pivot-Spalte“.

Als neuer Spaltenname werden die Werte von Spalte 1 verwendet. Außerdem möchte es wissen, wo sich die Werte für diese Feldnamen befinden. Diese finden Sie in unserer zusammengeführten Spalte.

Wenn Excel mit Textwerten umgehen kann, kann Power Query dies aufgrund seiner erweiterten Optionseinstellung auch. Wir müssen lediglich „Nicht aggregieren“ auswählen , damit Textwerte verarbeitet werden können.

Sobald wir auf OK klicken, können wir sehen, dass unsere Daten geschwenkt wurden.

Abschluss

Das Bild oben ist das Endergebnis der Abfrage. Ich hoffe, es hat Ihnen gefallen, wie wir die unübersichtlichen Daten in der bereitgestellten Textdatei umgestaltet und in eine saubere Dimensionstabelle umgewandelt haben , die für die Analyse geeignet ist.

Wenn Ihnen dieser Blogbeitrag gefallen hat, abonnieren Sie bitte den LuckyTemplates-Kanal, damit Sie keine neuen Inhalte verpassen.

Ich hoffe, wir sehen uns alle bei zukünftigen Herausforderungen zum Thema „Problem der Woche“.

Melissa