Umwandeln des Kalendertyp-Layouts in ein Tabellenformat in LuckyTemplates mithilfe des Abfrageeditors

Die Analyse der uns vorliegenden Daten kann schwierig sein, wenn sie nicht korrekt angeordnet sind. In diesem Tutorial erkläre ich, wie man vertikal und horizontal wiederholte Daten in ein Tabellenformat umwandelt. Wir tun dies, damit wir es einfacher mit LuckyTemplates analysieren können. Sie können sich das vollständige Video dieses Tutorials unten in diesem Blog ansehen.

Dieses Tutorial wurde erstellt, um die Frage eines  Mitglieds des zu beantworten  . Das Mitglied fragte nach einer Möglichkeit, seine Daten von einem kalenderartigen Layout in ein Tabellenformat umzuwandeln.

Bevor wir beginnen, untersuchen wir zunächst die uns vorliegenden Daten.

Hier haben wir ein Datenlayout, bei dem die Tage horizontal und die Wochen vertikal angeordnet sind. Diese Art der Anordnung ist den meisten von uns bekannt, da wir so die Daten leicht verstehen können.

Diese Art von Layout ist jedoch schwieriger zu analysieren, da viele Spalten und Zeilen berücksichtigt werden müssen.

Nachdem dies gesagt ist, wandeln wir die Daten in ein geeignetes Tabellenformat um, in dem wir am Ende eine Spalte für Datumsangaben, eine Spalte für die Bewegungen und eine weitere Spalte für die Namen erhalten.

Inhaltsverzeichnis

• Bereinigen von Daten für ein ordnungsgemäßes Tabellenformat
• Die Tische zerschlagen
• Identifizieren der Liste für die neuen Tabellen für ein ordnungsgemäßes Tabellenformat
• Bestimmen, welche Werte in die Tabelle gehören
• Gruppieren der Daten für ein geeignetes Tabellenformat
• Anhängen der einzelnen Tabellen
• Abschluss

Bereinigen von Daten für ein ordnungsgemäßes Tabellenformat

Zur Vereinfachung transformieren wir unsere Daten im Power Query-Editor . Aber bevor wir das tun, stellen wir sicher, dass die Formelleiste auf Ihrem Bildschirm sichtbar ist. Ist dies nicht der Fall, gehen Sie zur Registerkarte „Ansicht“ und aktivieren Sie das Kontrollkästchen neben der Formelleiste.

Der erste Schritt besteht darin, die unnötigen Werte in unseren Daten zu entfernen. Entfernen wir die erste Spalte und die oberen beiden Zeilen unserer Daten, da wir sie nicht wirklich benötigen. Klicken Sie dazu auf die Registerkarte „Zeilen entfernen“, klicken Sie auf „Oberste Zeilen entfernen“, geben Sie 2 ein und klicken Sie dann auf „OK“.

Um nun die erste Spalte zu entfernen, gehen Sie zur Registerkarte „Spalten auswählen“ und deaktivieren Sie dann Spalte 1, indem Sie das Kontrollkästchen daneben deaktivieren.

Entfernen wir auch die Zeilenindizes 14 bis 16, da sie vollständig nur mit Nullwerten gefüllt sind. Gehen Sie erneut zur Registerkarte „Zeilen entfernen“ und klicken Sie dann auf „Leere Zeilen entfernen“ .

Ein weiteres Problem besteht darin, dass die Datumswerte jedes Mal eine Spalte überspringen. Den Datumsangaben folgt immer ein Nullwert, wie unten hervorgehoben. Dies geschieht nicht nur in der obersten Reihe, sondern auch in den darunter liegenden Reihen.

Um diese Lücken zu schließen, transponieren wir zunächst unsere Datentabelle, indem wir zur  Registerkarte „Transformieren“ gehen und dann auf die Schaltfläche „Transponieren“ klicken.

Der Tisch sollte nun so aussehen, als wäre er nach der Aktion auf die Seite gedreht worden.

Wählen Sie als Nächstes alle Spalten aus, die die Datumsangaben enthalten, klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift und wählen Sie dann „Nach unten ausfüllen“ aus.

Dadurch sollten die Daten dupliziert und die Lücken in den ausgewählten Spalten gefüllt werden.

Die Tische zerschlagen

Wenn wir uns die Daten ansehen, die wir jetzt haben, sehen wir, dass es vier Tabellen nebeneinander gibt. Die Spalten 1 bis 7 bilden eine Tabelle, die Spalten 8 bis 13 bilden eine weitere Tabelle und so weiter.

Um diese in eine einzige Tabelle anzuhängen, müssen wir eine grundlegende Unpivot- Operation durchführen, um sie in ein geeignetes Tabellenformat umzuwandeln. Aber da die Tische nebeneinander liegen, können wir das nicht einfach so machen. Wir müssen diesen Tisch auseinanderbrechen und sie wieder zu kleineren Tischen zusammenfügen.

Dazu erstellen wir die erste Formel für dieses Tutorial. Klicken wir auf die Schaltfläche „fx“ und fügen dann die Formel hinzu, die wir verwenden möchten. Wir werden die Table.ToColumns -Operation verwenden. Dadurch wird eine Liste verschachtelter Listen mit Spaltenwerten aus einer Tabelle erstellt. Jede Spalte wird in eine Liste umgewandelt.

So habe ich die Formel aufgestellt.

Die Formel erstellt eine Liste der Spalten, die wir zuvor hatten. Der erste Wert der Liste enthält Spalte 1, der zweite Wert enthält Spalte 2 und so weiter.

Identifizieren der Liste für die neuen Tabellen für ein ordnungsgemäßes Tabellenformat

Jetzt sollten wir herausfinden, welche Werte in der Liste Datumswerte enthalten. Sobald wir wissen, wo sich diese Daten befinden, können wir bestimmen, wo jede unserer separaten Tabellen beginnt oder endet.

Dazu wandeln wir die Liste zunächst wieder in eine Tabelle um, indem wir oben links auf die Schaltfläche „Zu Tabelle“ klicken.

Fügen Sie dann daneben eine Indexspalte hinzu.

Als nächstes fügen wir neben der Indexspalte eine weitere Spalte hinzu. Mithilfe dieser Spalte können wir ermitteln, welche Werte in Spalte 1 die Datumswerte enthalten. Nennen wir diese Spalte Index2.

Dazu integrieren wir eine Formel in Index2, die den Indexwert von Spalte1 zurückgibt, wenn diese ein Datum enthält.

Wir werden die verwenden und dann auf Spalte1 verweisen. Die Klammern dienen als Operator; Das Hinzufügen von 0 zwischen diesen Klammern bedeutet, dass wir den ersten Wert aus der Liste erhalten. Dann stellen wir fest, ob es sich um ein Datum handelt. Wenn ja, möchte ich, dass die Formel die Indexnummer zurückgibt. Wenn nicht, möchte ich überhaupt keinen Wert zurückgeben.

Da der erste Wert in Spalte1 ein Datum enthält, sollte der erste Wert in Index2 seine Indexnummer 0 enthalten. Da außerdem der zweite Wert in Spalte1 kein Datum enthält, sollte der zweite Wert in Index2 nur einen Nullwert enthalten.

So habe ich die Formel für Index2 eingerichtet. Stellen Sie einfach sicher, dass Ihre Formel keine Syntaxfehler enthält.

Danach sollte unsere Index2-Spalte nun den Index der Werte in Spalte1 enthalten, die das Datum enthält. Aufgrund der von uns eingerichteten Formel werden auch einige Nullwerte enthalten sein.

Bestimmen, welche Werte in die Tabelle gehören

Nun geben diese Indexwerte in Index2 die Anfänge und Enden der einzelnen Tabellen an, die wir haben. Das heißt, 0 markiert den Anfang der ersten Tabelle, 7 markiert das Ende der ersten Tabelle und den Anfang der zweiten Tabelle und so weiter.

Um uns dabei zu helfen, herauszufinden, welche Werte in Spalte 1 in dieselbe Tabelle gehören, wenden wir in unserer neuen Spalte eine Ausfüllaktion an.

Nach Durchführung der Aktion sollten nun die Nullwerte durch Nullen, Siebener, Dreizehner und Zwanziger ersetzt werden. Diese Zahlen zeigen an, dass sie zur selben Tabelle gehören. Das heißt, die Nullen bilden eine Tabelle, die Siebener eine andere Tabelle und so weiter.

Gruppieren der Daten für ein geeignetes Tabellenformat

Da wir bereits wissen, welche Werte in Spalte 1 in dieselbe Tabelle gehören, gruppieren wir sie nun. Dazu verwenden wir die Operation „Gruppieren nach“.

Dadurch haben wir am Ende nur noch 4 Werte in unserer Index2-Spalte. Beachten Sie, dass sich neben unserer Spalte „Index2“ eine weitere Spalte mit dem Namen „Count“ befindet. Diese Spalte zählt die Anzahl der Zeilen in jedem unserer Werte in der Spalte Index2.

Das ist jedoch nicht wirklich das, was wir tun wollen. Was wir tun müssen, ist eine neue Tabelle mit den vorhandenen Spalten zu erstellen.

Dazu verwenden wir die Operation Table.FromColumns , verweisen auf die Spalte, die unsere Listenwerte enthält, nämlich Column1, und ändern dann den Rückgabetyp in eine Tabelle.

Es ist wichtig sicherzustellen, dass wir in unserer Formel auf die richtige Spalte verwiesen haben. Überprüfen Sie, ob Spalte1 wirklich die Listenwerte enthält.

Dadurch werden alle Werte in Spalte1 mit einem Index2-Wert von 0 in eine Tabelle eingefügt, diejenigen mit dem Index2-Wert von 7 in eine andere Tabelle und so weiter.

Wir haben jeden Datensatz auf den Wert 0 überprüft und dann den Listenwert aus Spalte1 abgerufen. Schließlich haben wir diese Listen mithilfe dieses M-Codes zu einer einzigen Tabelle zusammengefügt.

Wir schnappten uns die ersten 7 Spalten und fügten sie zu einer einzigen Tabelle zusammen, schnappten uns die nächsten paar Spalten, fügten sie zu einer anderen Tabelle zusammen und so weiter.

Anhängen der einzelnen Tabellen

Nachdem wir sie nun bereits gruppiert haben, versuchen wir, alle Tabellen anzuhängen, indem wir einen weiteren Schritt hinzufügen. Drücken wir erneut die FX- Taste, um eine neue Formel hinzuzufügen.

Die Table.Combine- Operation führt eine Liste von Tabellen zusammen und erstellt daraus dann eine einzelne Tabelle. Hier verweisen wir auf die Spalte, die unsere Tabellenliste enthält, nämlich die Count-Spalte.

Wir haben jetzt eine Tabelle, in der alle verschachtelten Tabellen wieder zu einer großen einzigen Tabelle zusammengefügt werden.

Nachdem wir alle Tabellen angehängt haben, können wir nun die letzten Schritte ausführen, um unsere Daten in ein richtiges Tabellenformat umzuwandeln. Wählen Sie die ersten beiden Spalten Ihrer Tabelle aus und klicken Sie dann auf der Registerkarte „Transformieren“ auf die Schaltfläche „Andere Spalten entpivotieren“ .

Entfernen Sie anschließend die Attributspalte, da wir sie nicht wirklich benötigen. Gehen Sie zu „Spalten auswählen“ und deaktivieren Sie dann die Attributspalte.

Legen Sie schließlich die richtigen Typen aller Spalten fest und beschriften Sie sie ordnungsgemäß.

Doppelklicken Sie auf die Spaltenüberschrift jeder Spalte, um deren Namen entsprechend zu ändern.

Ändern Sie den Typ der ersten Spalte in „Datum“, da sie unsere Datumswerte enthält.

Wählen Sie dann die zweite und dritte Spalte aus und legen Sie deren Typen auf Text fest.

Alles erledigt! So sollte unser richtiges Tabellenformat aussehen.

Abschluss

In diesem Tutorial haben wir einige Daten, die in einem Kalenderlayout dargestellt wurden, in ein richtiges Tabellenformat umgewandelt. Wir haben zunächst unsere Daten bereinigt, sie in kleinere Tabellen aufgeteilt, die zusammengehörigen Tabellen gruppiert und sie am Ende alle angehängt.

Dieses Tutorial bietet Ihnen viel Wissen darüber, wie Sie Ihre Daten für eine einfachere Analyse richtig modellieren.

Es ist sehr wichtig, die hier verwendeten Konzepte zu verstehen, da sie auch auf viele andere Arten angewendet werden können.

Wenn Ihnen die Konzepte immer noch nicht klar sind, kann es hilfreich sein, die folgenden Links zu überprüfen.

Viel Spaß beim Durcharbeiten!

Melissa