Microsoft Power Query-Tutorial zur Behebung gemischter Probleme mit der festen Spaltenbreite

Mudassir: Heute haben wir ein sehr interessantes Problem, mit dem wir arbeiten müssen. Das Problem mit dieser Datei besteht darin, dass sie fest durch Spalten getrennt ist und ich nicht weiß, wie ich das mit Microsoft Power Query lösen kann. Das vollständige Video dieses Tutorials können Sie unten in diesem Blog ansehen.

Erstens war es für mich nicht einfach, die Spalten dynamisch zu löschen. Zweitens haben wir in diesem Bericht eine Tabelle mit einer anderen Spaltenbreite und dann eine andere Tabelle mit einer anderen Spaltenbreite.

Wenn ich also oben dynamisch ein Trennzeichen anwende, könnte ich die Daten nicht sauber abrufen. Ich möchte alle diese Daten aus der zweiten Tabelle und meine Produktnummern aus der ersten Tabelle erhalten. Ich möchte auch die Jobnummer in jeder einzelnen Zeile aller Tabellen.

Ich habe versucht, das Problem selbst zu lösen, aber da es etwas mit der Stromabfrage zu tun hat, brauchte ich Hilfe von Melissa. Ich dachte, dass es mindestens zwei Tage dauern würde, aber es gelang ihr sofort, eine Lösung zu finden.

Melissa zeigt uns, wie sie dieses komplizierte Problem gelöst hat. Ich denke, dass die meisten Menschen mit solchen Problemen zu kämpfen haben und nach Wegen suchen, sie zu lösen.

Melissa: Der erste Tipp: Wenn Sie eine Datei mit fester Länge betrachten, können Sie zur Registerkarte „Ansicht “ gehen und die Option „ Monospaced“ aktivieren .

Wir können sehen, dass es sich um eine Schriftart mit fester Länge handelt. Wir können auch die Kopfzeilen, Anfangstabellen und Untertabellen sehen. Dies sind die Teile, an denen wir interessiert sind und aus denen wir extrahieren möchten.

Stellen Sie außerdem sicher, dass Ihre Bearbeitungsleiste aktiviert ist. Es ist immer gut, es auf Ihrem Bildschirm sichtbar zu haben, da wir es häufig verwenden, um geringfügige Änderungen an der Eingabe vorzunehmen.

Ich habe einen Parameter für den Dateispeicherort erstellt, an dem ich die CSV-Datei gespeichert habe. Ich habe das als Staging-Datei eingebracht und in meinen Dateispeicherortparameter eingefügt. Dann habe ich eine Referenz erstellt und werde von dieser Referenz ausgehen. Das ist es also, was wir gerade in Microsoft Power Query betrachten.

Inhaltsverzeichnis

• Hinzufügen einer Indexspalte
• Erstellen einer Pufferliste in Microsoft Power Query
• Kopfzeilen von den Zeilen trennen
• Entfernen der Nullen und Leerzeichen in Microsoft Power Query
• Aufteilen der Abschnitte
• Kürzen der Textzeichenfolgen in Microsoft Power Query
• Abschluss

Hinzufügen einer Indexspalte

Wenn ich mit der Arbeit an einer Datei wie dieser beginne, muss ich normalerweise die Anforderungen des Kunden kennen. Ich frage, was der Kunde braucht und worauf er achten muss.

In diesem Fall benötigen wir die Artikelnummer und die Auftragsnummer aus den Kopfzeilen und dann alle Details, die zu dieser spezifischen Kopfzeile gehören.

Wir brauchen einen Schlüssel, um diese Dinge wieder zusammenzubringen. Wenn jedoch kein Schlüssel vorhanden ist, füge ich eine Indexspalte hinzu . Ich klicke auf das Minitabellensymbol, wähle Indexspalte hinzufügen und füge dann From 0 hinzu .

Ich werde später eine Logik mit Listen verwenden, die den 0-basierten Index haben. Wenn Ihr Index bei Null beginnt, bedeutet dies tatsächlich, dass Sie auf dieselbe Zeile verweisen können. Andernfalls müssen Sie 1 subtrahieren, um zu dieser 0-basierten Position zu gelangen.

Dann müssen wir die Position finden, an der sich unsere Header befinden, was uns recht einfach gelingt, da diese Header in der gesamten Datei ständig wiederholt werden.

Kopieren wir zunächst diesen Wert:

Fügen Sie eine neue leere Abfrage hinzu, fügen Sie diese ein und nennen Sie sie HeaderID .

Ich werde den gleichen Vorgang für die Untertabellen durchführen. Ich kopiere diese Textzeichenfolge, erstelle eine weitere leere Abfrage und füge diesen Wert ein. Dies wird die Zeichenfolge sein, die wir bei der Suche nach detaillierten Zeilen verwenden werden.

Wenn dieser Prozess irgendwie die Kopfzeile einer dieser Tabellen ändert, muss ich nur eine der Textzeichenfolgen ändern und die Datei funktioniert wieder.

Ich muss nicht wirklich in den M-Code eintauchen, um nach der gesuchten Zeichenfolge zu suchen. Wir können dies einfach als Parameter verwenden.

Lassen Sie uns die Last für diese beiden Abfragen aktivieren.

Erstellen einer Pufferliste in Microsoft Power Query

Das erste, was ich tun werde, ist, Spalte1 in eine Liste umzuwandeln, indem ich sie einmal referenziere und in den Speicher lade. Auf diese Weise muss ich die Datei nicht wiederholt aufrufen.

Ich öffne den erweiterten Editor und platziere ihn ganz oben. Wenn Sie die Benutzeroberfläche zum Erstellen Ihres Codes verwenden, wird auf den vorherigen Schritt verwiesen.

Wenn Sie einen Pufferschritt an einer anderen Stelle in Ihrem Code platzieren und später eine Änderung vornehmen möchten, hilft Ihnen dies dabei, die Änderungen an dem Schritt vorzunehmen, den Sie manuell erstellen.

Ich nenne diese BufferList und verweise auf Column1. Um es in den Speicher zu laden, füge ich einen List.Buffer- Schritt hinzu.

Hier ist meine Variable ganz oben. Ich kann immer wieder darauf verweisen.

Das erste, was ich bestimmen möchte, ist, wo meine Kopfzeilen beginnen, da ich einen Schlüssel benötige, um diese Kopfzeilenabschnitte beizubehalten und einen einzigen Wert für alle diese Zeilen zu erhalten. Dazu füge ich eine benutzerdefinierte Spalte hinzu und nenne sie Header .

Ich schreibe Folgendes: Wenn Spalte1 unserer Header-ID entspricht, möchte ich, dass meine Indexnummer null ist.

Als Ergebnis wurde der Text gefunden und 5 und 23 zurückgegeben.

Ich brauche diesen Wert in allen Zeilen, also muss ich ihn ausfüllen. Sie können zum Ausfüllen einfach mit der rechten Maustaste klicken, Sie können aber auch eine sehr einfache Syntax verwenden und diese in die Bearbeitungsleiste einfügen.

In diesem Fall habe ich Table.FillDown hinzugefügt und in der Textzeichenfolge angegeben, welche Spalte wir ausfüllen möchten (Header).

Jetzt haben wir das für alle Zeilen ausgefüllt. Wir haben einen Schlüssel für alle Kopfabschnitte und alle Zeilenabschnitte, da sie alle diesen Wert gemeinsam haben.

Kopfzeilen von den Zeilen trennen

Der nächste Schritt besteht darin, die Überschriften von den Zeilen zu trennen. Ich füge eine weitere benutzerdefinierte Spalte hinzu und nenne sie Temp . Dieses Mal werden wir etwas Ausführlicheres machen und die BufferList nutzen , die ich zuvor erstellt habe.

Wir werden einige Listenfunktionen verwenden, um jede Position zu überprüfen und herauszufinden, ob eine Übereinstimmung mit dem Index besteht.

Ich beginne mit einer if-Anweisung und verwende List.Contains , um nach einer bestimmten Position in der BufferList zu suchen und auf die Abfrage HeaderID zu verweisen .

Wir möchten es über die gesamte Länge der Datei finden und dann die Position des Elements innerhalb der Liste zurückgeben. Wenn es mit dem Index übereinstimmt, haben wir eine Übereinstimmung für diese bestimmte Zeile.

Dann möchte ich einen Wert zurückgeben, um den Header zu identifizieren. In diesem Fall gebe ich nur ein H zurück. Ich kopiere die Syntax, damit ich sie nicht noch einmal schreiben muss.

Wir müssen auch den Zeilenabschnitt identifizieren. Wenn die Liste nicht die HeaderID , sondern die DetailID enthält , befinden wir uns in einem Zeilenabschnitt.

Wenn Column1 eine leere Textzeichenfolge ist, möchte ich, dass sie leer bleibt . Wenn das nicht der Fall ist, möchte ich, dass es null ist .

Diese Opposition hat die Kopfzeile erhalten und ein H zurückgegeben, und dann hat sie eine detaillierte Zeile gefunden und ein R zurückgegeben. Dann hat sie Nullen für alle Elemente zurückgegeben, die in diesem Zeilenabschnitt gemeinsam genutzt werden.

Diese Leerzeichen oder Nullen sind wichtig, da sie das Ausfüllen ermöglichen. Das Ausfüllen wird nicht über diese leeren Zellen verschoben, sodass wir diese später entfernen können.

Wir machen dies in der Bearbeitungsleiste und verwenden erneut Table.FillDown . Es benötigt eine Liste mit dem Spaltennamen, der unsere Temp- Spalte ist.

Jetzt haben wir die H- und R-Werte überall in dieser Spalte wiederholt, was bedeutet, dass wir die Überschriften tatsächlich von den detaillierten Abschnitten trennen können.

Sie können es auch über die Benutzeroberfläche ausfüllen, wenn Sie den Code nicht schreiben möchten. Sie können einfach mit der rechten Maustaste klicken und „ Füllen“ und dann „Nach unten“ auswählen .

Entfernen der Nullen und Leerzeichen in Microsoft Power Query

Jetzt, wo wir dieses Recht haben, können wir die Dinge entfernen, die wir nicht brauchen. Alles, was null ist oder ein Leerzeichen enthält, sind die Zeilen, die wir nicht benötigen und entfernt werden müssen. Diese können wir durch Filterung beseitigen.

Aufteilen der Abschnitte

Sobald wir diese Leerzeichen und Nullen entfernen, bleibt uns alles übrig, was wir brauchen. An dieser Stelle können wir die Abschnitte einfach aufteilen. Wir können uns auf die Kopfzeilen konzentrieren und sie auswählen, da sie einen anderen Abstand als alle Detailzeilen haben (die ebenfalls einen anderen Abstand haben).

Ich füge einen neuen Schritt in der Bearbeitungsleiste hinzu, der es mir ermöglicht, einen weiteren Filter für dieselbe Spalte zu erstellen. In diesem Fall behalte ich einfach alle Kopfzeilenabschnitte bei.

Jetzt habe ich all diese Kopfzeilen hier.

Ich kann Column1 auswählen , zur Bearbeitungsleiste gehen, Spalte teilen auswählen und dann nach Positionen teilen.

Dann lassen Sie die Power-Abfrage selbst dies herausfinden. Es werden einige Positionen vorgeschlagen. Klicken Sie auf OK, um diese Positionen zu akzeptieren.

Die einzigen Dinge, die uns in den Kopfzeilen interessieren, sind der Artikel und die Auftragsnummer .

In der Bearbeitungsleiste hier kann ich diese mit Item und Job # umbenennen . Dies erspart mir einen weiteren Schritt zum Umbenennen der Spalte.

Nach diesem Schritt muss ich nur noch den Artikel auswählen , die Job-Nr. auswählen und natürlich unseren Header-Schlüssel auswählen . Dann werde ich alle anderen Spalten entfernen, da ich diese nicht mehr benötige.

Das wird das Ergebnis sein. Wir müssen noch die Werte bereinigen und das Textelement und die Bindestriche entfernen. Alles, was wir wollen, sind die Werte dazwischen.

Also öffnen wir dies und heben die Auswahl der Bindestriche und Elemente auf.

Jetzt sind alle Header fertig.

Den gleichen Vorgang müssen wir auch für DetailID durchführen . Ich muss diese Schritte umbenennen, damit ich später leichter darauf zurückgreifen kann.

Wir kehren zur ursprünglichen Abfrage zurück, die wir gestartet haben. Wir haben mit gefilterten Zeilen im Bereich „Angewandte Schritte“ begonnen.

Ich werde das kopieren und zu meinem Filter hinzufügen. Dieses Mal wähle ich nicht H, sondern R aus.

Dann wähle ich Spalte1 aus, gehe zur Spalte „Teilen“, teile sie nach Positionen auf und lasse es dann durch die Leistungsabfrage herausfinden.

Dies legt die Leistungsabfrage nahe. Probieren wir es mal aus.

Das sieht eigentlich ziemlich gut aus. Sogar die Gesamtreihenaufteilung ist perfekt. Natürlich gibt es viele Leerzeichen, weil wir diese Einrückung hatten.

Kürzen der Textzeichenfolgen in Microsoft Power Query

Ich wähle die erste Spalte aus und drücke dann die Abwärts- und Umschalttaste, um bis zur Spalte 1.10 auszuwählen. Gehen Sie zu „Transformieren“ , wählen Sie „Format“ und dann „Trimmen“ . Durch das Trimmen werden nur die überschüssigen Leerzeichen vor oder am Ende der Zeichenfolge entfernt, nicht aber dazwischen.

Als Nächstes können wir einfach die Überschriften hochstufen, sodass ich nicht alle Überschriften oder Titel für diese Spalten eingeben muss. Im Split-Schritt habe ich zwei Spalten umbenannt. Bei 10 Spalten ist das natürlich etwas mühsam.

Wir müssen auch diese überschüssigen Werte loswerden. Da wir Summen haben, muss ich eine dieser letzten drei Spalten verwenden, da sie die einzigen Zeilen sind, in denen die zusätzlichen Werte irgendwo dazwischen liegen. Dann deaktivieren wir die Leerzeichen, Bindestriche und Texte.

Dann entferne ich die unnötigen Spalten, sodass nur noch eine Tabelle mit nur den Überschriften und nur den Details übrig bleibt. Wir brauchen einen Schlüssel, um diese Abschnitte wieder zusammenzuführen.

Dazu können wir eine Selbstzusammenführung verwenden, sodass wir die Tabelle mit sich selbst zusammenführen können, um diese Informationen wieder zusammenzuführen. Wählen Sie auf der Registerkarte „Startseite “ die Option „ Zusammenführen “ und dann „Spalte 5“ und dieselbe Abfrage aus.

Anstelle von AllDetails möchte ich AllHeaders als meine anfängliche Tabelle, mit der ich zusammenführen möchte.

Dadurch wurden alle Informationen aus der Kopftabelle mit einer einzigen Zeile für jeden Artikel und jede Auftragsnummer zurückgebracht.

Wir haben einen Schlüssel zum Zusammenführen mit den Detailzeilen verwendet. Wenn ich hier im Leerraum zur Seite drücke, sehen wir eine Vorschau aller Zeilen, die zu Kopfzeile 5 gehören.

Wir entfernen hier die letzte Spalte und sind dann mit der Korrektur der gemischten festen Spaltenbreite in Microsoft Power Query fertig.

Abschluss

In diesem Tutorial haben wir eine Möglichkeit gefunden, gemischte Probleme mit festen Spaltenbreiten mithilfe von Microsoft Power Query zu lösen. Wenn Ihnen der Inhalt dieses speziellen Tutorials gefallen hat, vergessen Sie bitte nicht, den LuckyTemplates-TV-Kanal zu abonnieren.

Wir veröffentlichen ständig eine große Menge an Inhalten von mir selbst und einer Reihe von Inhaltserstellern, die sich alle dafür einsetzen, die Art und Weise zu verbessern, wie Sie LuckyTemplates und die Power Platform nutzen.

Melissa