Erstellen von Werten für jedes Datum mithilfe der Power Query-Formel

In diesem Tutorial erfahren Sie, wie Sie mithilfe der Power Query -Formel in LuckyTemplates Werte für jeden Tag erstellen .

Das Erstellen von Werten für Datumsangaben ist eine wichtige Technik, die Sie in LuckyTemplates erlernen sollten. Es kann Ihnen die Daten liefern, die Sie im Laufe der Tage in Ihrem Modell sehen möchten. Diese Technik deckt auch die Lücken zwischen den Spalten ab.

Dies ist ein großartiges Tool, wenn es in Ihren Datumstabellen an Informationen mangelt.

Hier finden Sie eine Schritt-für-Schritt-Anleitung: Sie können sich das vollständige Video dieses Tutorials unten in diesem Blog ansehen.

Inhaltsverzeichnis

• Legen Sie zusätzliche Werte fest
• Erstellen Sie die Filterbedingung für die IDs
• Fügen Sie die Filterbedingung für die Daten hinzu
• Überprüfen und sortieren Sie die Datumsspalte
• Verwenden Sie die Table.FirstN-Funktion
• Ermitteln Sie die Tagesdifferenz zwischen zwei Datumswerten
• Bestimmen Sie einen Gewichtsschritt
• Die Lücke füllen
• Holen Sie sich die Gewichtsprognose von Datteln
• Abschluss

Legen Sie zusätzliche Werte fest

Bei Datensätzen, die IDs, Daten und Gewichte enthalten, müssen Sie die Lücke zwischen diesen Messungen füllen. Dies kann durch die Erstellung eines Datumsbereichs erreicht werden, um die Differenz über die Tage zu verteilen.

Gehen Sie dazu zu Power Query, um Ihre Tabelle anzuzeigen.

Sie müssen eine Kopie Ihrer Daten in den Speicher laden. Dazu müssen Sie das Table.Buffer -Maß verwenden.

Wenn Sie auf TableBuffer klicken, sehen Sie, was die Kennzahl bewirkt.

Gehen Sie als Nächstes zurück zu Ihrem Datensatz und klicken Sie auf den FX in der Bearbeitungsleiste. Sie müssen den Table.Buffer in der Kennzahl hinzufügen.

Benennen Sie diesen Schritt dann in BufferedTable um .

Fügen Sie als Nächstes eine benutzerdefinierte Spalte hinzu.

Erstellen Sie die Filterbedingung für die IDs

Ändern Sie in dieser Power Query-Formel den referenzierten Schritt von BufferedTable in RenameColumns , da BufferedTable zuerst überprüft werden sollte.

Um die BufferedTable zu überprüfen, verwenden Sie Table.SelectRows , das eine Tabelle zurückgibt, die der ausgewählten Bedingung entspricht.

Verwenden Sie also für jeden Ausdruck in der Formel Table.SelectRows und dann BufferedTable.

Verwenden Sie als Filterbedingung eine benutzerdefinierte Endfunktion und geben Sie BT für Buffered Table ein . Überprüfen Sie die ID-Spalte, ob sie mit der ID aus dem aktuellen Datensatz übereinstimmt. Greifen Sie als Nächstes auf den aktuellen Datensatz zu, indem Sie den Unterstrich als Zugriffsoperator verwenden, und verweisen Sie dann auf dieselbe ID-Spalte.

Dadurch erhalten Sie eine gefilterte Tabelle, die nur die IDs enthält, die mit dem aktuellen Datensatz übereinstimmen. 

Fügen Sie die Filterbedingung für die Daten hinzu

Sie müssen die Datumsspalte filtern, da Sie nur die Daten benötigen, die mit dem aktuellen Datensatz übereinstimmen.

Verwenden Sie diese Funktionen, um die BT-Datumsspalte zu überprüfen:

Diese Spalte muss größer oder gleich dem Datum des aktuellen Datensatzes sein.

Damit enthält die Tabelle dann zwei statt drei Zeilen:

Überprüfen und sortieren Sie die Datumsspalte

Sie müssen mithilfe von Table.Sort überprüfen, ob die Sortierung der Datumsspalte korrekt ist . Die Funktion Table.Sort sortiert eine Tabelle nach den von Ihnen gewünschten Kriterien nach einer oder mehreren Spalten.

Die Syntax in diesem Beispiel ist ziemlich kompliziert, sodass Sie einen Drilldown zu einem Tabellenthema durchführen müssen.

Sie müssen mit der rechten Maustaste auf eine Tabelle klicken und sie als neue Abfrage hinzufügen.

Wenn Sie darauf klicken, erhalten Sie eine benutzerdefinierte Abfrage, die diese Tabelle enthält.

Klicken Sie anschließend auf die Datumsspalte und wählen Sie „Aufsteigend sortieren“.

Kopieren Sie die gesamte Formel:

Gehen Sie zurück zu Ihrer Hauptabfrage und fügen Sie sie in die Formelleiste ein:

Sie müssen den Verweis auf Table.Sort entfernen, da die Tabelle, die den Parameter übergeben muss, das Ergebnis von Table.SelectRows sein sollte.

Wenn Sie fertig sind, fügen Sie die Sortierbedingungen wieder hinzu. Nachdem Sie die Änderungen übernommen haben, ist Ihre Tabelle nun korrekt nach Datum sortiert.

Verwenden Sie die Table.FirstN-Funktion

Für diesen Schritt benötigen Sie nur 2 statt 3 Datensätze.

Um dieses Problem zu beheben, kehren Sie zur benutzerdefinierten Abfrage zurück. Klicken Sie auf „Zeilen beibehalten“ und dann auf „Oberste Zeilen beibehalten“.

Geben Sie 2 in das Feld für die Anzahl der Zeilen ein.

Sie werden feststellen, dass die Funktion Table.FirstN in der Bearbeitungsleiste angezeigt wird. Table.FirstN gibt abhängig vom Wert von countOrCondition die erste(n) Zeile(n) der Tabelle zurück .

Fügen Sie diese Funktion zusammen mit der Anzahl der benötigten Zeilen in die Hauptabfrage ein. Fügen Sie am Ende die Funktionstyptabelle ein .

Jedes Tabellenobjekt im Satz hat jetzt entweder 1 oder 2 Zeilen.

Ermitteln Sie die Tagesdifferenz zwischen zwei Datumswerten

Der nächste Schritt besteht darin, die Anzahl der Tage zwischen den Datumswerten zu ermitteln und das Datum zu extrahieren, das im zweiten Datensatz enthalten war.

Beginnen Sie mit dem Hinzufügen einer benutzerdefinierten Spalte. Nennen Sie es Tage . Befolgen Sie dann diese Power Query-Formel:

Die Syntax stellt sicher, dass eine Tabelle zwei Datensätze enthält. Wenn es nur 1 gibt, beträgt die Tagesdifferenz 0, da es keinen nächsten Datensatz gibt. Wenn es 2 gibt, können Sie die Differenz aus den Datumswerten ermitteln.

Fügen Sie als Nächstes die Typnummer wie folgt in die Formel ein.

Wie Sie sehen, beträgt die Differenz zwischen dem 14. September 2017 und dem 4. Oktober 2018 385 Tage.

Bestimmen Sie einen Gewichtsschritt

Als nächstes müssen Sie berechnen, wie hoch das Gewicht für jeden Tag sein sollte.

Sie müssen erneut eine benutzerdefinierte Spalte hinzufügen, sie WeightStep nennen und diese Power Query-Formel kopieren:

Die Syntax prüft, ob die Tabelle mehr als eine Zeile enthält, subtrahiert die Gewichtung vom aktuellen Datensatz und dividiert diese Differenz durch die Anzahl der Tage.

Fügen Sie erneut die Typnummer in die Formel ein, damit Sie das Gewicht jedes Tages sehen können.

Die Lücke füllen

Da Sie nun die Anzahl der Tage kennen, können Sie eine Liste mit Daten erstellen, um die Lücke im Datensatz zu schließen.

Fügen Sie zunächst eine weitere benutzerdefinierte Spalte hinzu und nennen Sie sie DateKey . Befolgen Sie dann diese Power Query-Formel:

Die Formel prüft, ob ein Datumsunterschied vorliegt. Wenn dies der Fall ist, wird eine Liste mit Daten zwischen dem Startdatum des aktuellen Datensatzes erstellt und um die Anzahl der Tage erweitert. Wenn es jedoch keine Unterschiede gibt, wird das Datum zurückgegeben.

Das ist das Ergebnis:

Für die Tabellensubjekte, bei denen es keine Unterschiede gab, wurde das Datum zurückgegeben. Die Tabellen, die dies taten, erhielten eine Liste mit Daten.

Wenn Sie den DateKey auf neue Zeilen erweitern, können Sie die Datensätze aller Tage sehen.

Holen Sie sich die Gewichtsprognose von Datteln

Nachdem Sie eine Datumsliste erstellt haben, müssen Sie eine Gewichtsprognose für die Daten erhalten.

Fügen Sie zunächst eine benutzerdefinierte Spalte hinzu und nennen Sie sie WeightProjection . Geben Sie dann diese Power Query-Formel ein:

Die Syntax ruft den Gewichtungswert ab, addiert ihn zum DateKey-Wert, subtrahiert ihn vom Datumswert und multipliziert ihn mit dem WeightStep.

Fügen Sie am Ende der Formel erneut die Typnummer hinzu.

Sie haben jetzt die Gewichtsprognose für jedes Datum.

Entfernen Sie die Hilfsspalten „Benutzerdefiniert“, „Tage“ und „Gewichtsschritt“ mithilfe der folgenden Formel und ändern Sie das Format der Spalte „Datumsschlüssel“ in „Datum“.

Abschluss

In diesem Tutorial haben Sie gelernt, wie Sie die Lücken in Ihrem Datensatz schließen, indem Sie in jeder Datumsliste Werte erstellen.

Mithilfe dieser Datumswerte können Sie wertvolle Erkenntnisse aus Ihrem Modell gewinnen. Wenn Ihren Tabellen Informationen fehlen, können Sie sie mit dieser Technik umfassender gestalten.

Möglicherweise enthält Ihre Tabelle wichtige Daten, die Sie weglassen. Maximieren Sie daher immer jede Zeile und Spalte.

Alles Gute,

Melissa