Beheben fehlender Währungskurse mit Power Query in LuckyTemplates

In diesem Tutorial erfahren Sie, wie Sie Power Query für Währungsumrechnungen in LuckyTemplates verwenden. Das vollständige Video dieses Tutorials können Sie unten in diesem Blog ansehen.

Die in diesem Blog beschriebene Methode hilft Ihnen, Probleme in Ihrer Währungskurstabelle mithilfe des letzten verfügbaren Kurses zu lösen.

Sie werden verstehen, wie Sie die Dinge regeln können, wenn in der Wechselkurstabelle nicht für jeden einzelnen Tag ein Wechselkurs angegeben ist.

Inhaltsverzeichnis

• Identifizieren fehlender Daten
• Trennung verschiedener Währungen
• Fehlende Tarife ergänzen
• Alle Währungen zusammenführen
• Beziehungen im Datenmodell erstellen
• Vergleich von DAX- und Power Query-Methoden
• Abschluss

Identifizieren fehlender Daten

Dieses Problem kann auftreten, wenn die Datumstabelle keine Daten für Feiertage oder Wochenenden enthält.

Wenn Sie an einem Tag keinen Tarif haben und diesen nicht berücksichtigen, werden Kennzahlen wie „Gesamtumsatz“ nicht umgerechnet.

Wenn Sie einen Blick auf diese Währungskurstabelle werfen, sind dort die Kurse in Euro für Januar 2016 aufgeführt.

Beachten Sie jedoch, dass der 27. und 28. Tag fehlen und keine Rate haben. Wenn Sie sich also die Matrix ansehen, können Sie sehen, dass es für 27 und 28 keine Kurse gibt.

Die Kennzahl „Währungskurs mit fehlendem Wert“ ist einfach eine Summe der Tabelle „Fakt-Währungskurse“.

Der Gesamtwert von 26,6983 in der Tabelle ist bedeutungslos. Es handelt sich lediglich um die Summe aller darüber liegenden Währungskurse, die Sie nicht addieren müssen, da es sich um die aktuellen Tageskurse handelt.

Dies kann nun in Power Query behoben werden, da es sich eher um ein Datenmodellierungsproblem handelt, das Abfragen und Aggregation umfasst. Dies ist auch mit DAX möglich, einfacher und schneller geht es jedoch mit Power Query in LuckyTemplates.

Klicken Sie also auf „Daten transformieren“ und öffnen Sie dann das Power Query-Fenster.

Dies ist die Tabelle mit fehlenden Daten. Sie können in dieser Wechselkurstabelle auch sehen, dass sie Euro, Pfund und Dollar enthält.

Und dann ist dies der Abschlusstisch, dessen Werte am 27. und 28. Januar liegen. Dies ist die Tabelle oder Ausgabe, die Sie haben sollten.

Importieren Sie Ihre Daten aus der Quelle, aus der Sie sie beziehen, und ändern Sie einfach den Datentyp.

Trennung verschiedener Währungen

Wenn Sie mehr als eine Währung haben, ist es als nächstes wichtig, diese nach ihrem Ticker zu trennen und zu gruppieren.

Wenn Sie sie gruppieren, achten Sie darauf, sie in „Alle Zeilen“ zu gruppieren, damit beim Klicken auf „Euro“ beispielsweise nur Wechselkurse in Euro angezeigt werden.

Sie können auch sehen, dass hier sowohl Tage als auch die Pfund-Währung fehlen.

In LuckyTemplates gibt es dieses Ding namens „ Invoke Custom Function“ . Dies ist eine Funktion mit FillMissingRates.

Wenn Sie auf „Euro“ klicken, wird eine vollständige Tabelle angezeigt, die die fehlenden Daten enthält, die Sie in der erweiterten Tabelle gesehen haben. Es ist von den ältesten zu den neuesten Tarifen sortiert.

Finden Sie heraus, was die benutzerdefinierten Funktionen bewirken. Wenn Sie auf das Zahnradsymbol neben „Benutzerdefinierte Funktion aufrufen“ klicken, können Sie den Spaltennamen und die Quelle sehen, die in die Spalte „Alle“ zieht.

Beginnen Sie dazu mit einer einfachen Tabelle. Führen Sie die Transformationen durch, erhalten Sie das gewünschte Ergebnis und verwenden Sie es dann zum Erstellen der Funktion. Es ist sinnvoller, als es in der Tabelle zu versuchen, in der sich alle anderen Untertabellen befinden.

Wenn Sie sich beispielsweise auf den Euro konzentrieren möchten, filtern Sie ihn nach dem Ticker.

Um herauszufinden, welche Tage fehlen, führen Sie sie mit Ihrer Datumstabelle zusammen.

Hier ist eine einfache, nach unten gefilterte Währungstabelle und DimDate-Tabelle:

Für die Join-Art können Sie „Full Outer“ verwenden, um alle Zeilen aus beiden Tabellen einzubinden. Dies zeigt Ihnen, was fehlt und welche Tarife dazu passen.

Wenn Sie es zusammenführen, erhalten Sie eine vollständige Tabelle als Untertabelle.

Sortieren Sie anschließend die Zeilen und erweitern Sie dann „DimDate“, sodass nur noch die Spalte „Datum“ enthalten ist.

Jetzt können Sie sehen, dass alles zusammenpasst. Und wenn Sie die Zeilen vom ältesten zum neuesten sortieren, können Sie auch die beiden fehlenden Tage mit Nullwerten sehen.

Fehlende Tarife ergänzen

Es ist wichtig, dass sie in dieser Reihenfolge vorliegen, da Sie alle Nullen in allen Spalten außer „Datum.1“ ausfüllen.

Nachdem Sie es ausgefüllt haben, können Sie sehen, dass sich die Nullwerte in Euro und den letzten verfügbaren Kurs geändert haben.

Entfernen Sie als Nächstes die unnötigen Spalten wie die Datumsspalte aus der Tabelle.

Die Spalte „Datum.1“ enthält bereits die Daten, sodass die Spalte „Datum“ nicht benötigt wird. Stellen Sie dann die Crossrate auf „Dezimalzahl“ ein.

Ordnen Sie abschließend die Spalten neu an, benennen Sie sie um und stellen Sie sicher, dass Ihre Datentypen festgelegt sind.

Sie verfügen nun über eine vollständige Währungskurstabelle.

Dies funktioniert jedoch nur für diese Tabelle. Sie müssen einen Weg finden, dieselbe Logik auf eine Tabelle anzuwenden, in der Währungen gruppiert sind.

Alle Währungen zusammenführen

Wenn Sie unterschiedliche Währungen haben, ist es schwierig, unterschiedliche Kurstabellen zu verwalten. Sie müssen sie also zusammenführen, um eine Währungstabelle zu erstellen.

Nehmen Sie zunächst Ihre vorhandene Tabelle und erstellen Sie eine Funktion, indem Sie auf Tabelle klicken und Funktion erstellen auswählen . Geben Sie als Nächstes einen Funktionsnamen ein.

Gehen Sie danach zum erweiterten Editor . Entfernen Sie die Berechnungen, die Sie nicht benötigen. Entfernen Sie in diesem Fall die Quelle, den Filter und ändern Sie die Syntax.

Benennen Sie als Nächstes Ihren Parameter „Quelle“ und geben Sie dann „Let“ ein. Da die gefilterten Zeilen entfernt wurden, ändern Sie den Wert in der Syntax für zusammengeführte Abfragen in „Quelle“. Löschen Sie anschließend die Quelle am Ende der Syntax und klicken Sie dann auf „Fertig“.

Gehen Sie nun zu „Spalte hinzufügen“ und klicken Sie auf „Benutzerdefinierte Funktion aufrufen“. Geben Sie einen Spaltennamen wie „Alle Daten“ ein. Wählen Sie für die Funktionsabfrage die zuvor erstellte Funktion aus. Wählen Sie „Alle“ als Quelle und klicken Sie dann auf „OK“.

Als Nächstes müssen Sie alle Spalten außer „Alle Daten“ entfernen. Erweitern Sie dann diese Spalte und deaktivieren Sie die Einstellung „Originalspaltennamen als Präfix verwenden“. Klicken Sie anschließend auf OK und ändern Sie die Spaltentypen.

Legen Sie den Typ der Spalten „Datum“, „Ticker“ und „Crossrate“ auf „Datum“, „Text“ bzw. „Dezimal“ fest. Wenn Sie fertig sind, klicken Sie auf Schließen und Übernehmen.

Sie erhalten nun eine kombinierte Währungskurstabelle ohne fehlende Daten und Kurse.

Beziehungen im Datenmodell erstellen

In Ihrem Datenmodell können Sie die Tabelle FactCurrencyRates sehen . Erstellen Sie nun eine Beziehung, Ticker zu Ticker und Datum zu Datum, mit dieser Tabelle und den Tabellen DimCurrencyRates und DimDate.

Ziehen Sie in Ihrer Matrix den Wert „Währungskurs nicht fehlen“ ein, um zu sehen, dass nun die Kurse für den 27. und 28. enthalten sind, die alle in Power Query erstellt wurden.

In der Spalte „Letzter gemeldeter Währungskurs“ werden auch die Kurse der fehlenden Tage angezeigt. Es werden die gleichen Tarife und Werte angezeigt. Der Unterschied besteht jedoch darin, dass diese Spalte in DAX erstellt wird.

Dies ist das Maß und die Syntax dieser Spalte. Es ist nicht so einfach, nur die Funktion in Power Query zu verwenden.

Wenn Sie sich die Tabelle ansehen, sehen Sie die Spalte „Converted Sales No Missing“. Es zeigt die Verkäufe an, die mithilfe der Spalte „Währungskurs nicht fehlen“ umgerechnet wurden. Die andere Spalte mit konvertierten Verkäufen verwendet die Spalte, die in DAX erstellt wurde.

Die zum Abrufen der konvertierten Verkäufe verwendete Funktion ist . Wenn Sie sich die Kennzahl für diese Spalte ansehen, können Sie sehen, dass SUMX die DimDate-Tabelle durchläuft.

Vergleich von DAX- und Power Query-Methoden

Sie können tatsächlich die beiden konvertierten Verkaufsspalten vergleichen, die DAX und Power Query in LuckyTemplates verwendet haben.

Entfernen Sie dazu alle Spalten mit Ausnahme der konvertierten Verkaufsspalten. Starten Sie dann den Leistungsanalysator und klicken Sie auf Aufzeichnung starten.

Öffnen Sie als Nächstes DAX Studio. Es handelt sich um eine Anwendung, die Ihnen zeigt, wie und warum Dinge in Ihrem Modell funktionieren. Kopieren Sie anschließend die Abfrage Ihrer Matrix und fügen Sie sie in das Studio ein.

Wandeln Sie für den Vergleich zunächst eine davon in einen Kommentar um, damit die andere Spalte, die getestet wird, nicht beeinträchtigt wird.

Laden Sie dann die Server-Timings und den Abfrageplan. Da Sie die Leistung vergleichen, leeren Sie zunächst den Cache, bevor Sie den Vergleich ausführen.

Nachdem Sie den Test ausgeführt haben, können Sie sehen, dass die Spalte, die DAX verwendet hat, viele Scans erhalten hat und eine Gesamtzeit von 71 Millisekunden hat.

Führen Sie nun die andere Spalte aus, die die Power-Abfrage verwendet hat. Aber wandeln Sie zunächst die andere Spalte in der Syntax in einen Kommentar um.

Sie können sehen, dass die Spalte nur 25 Millisekunden lang lief und nur 7 Scans hatte.

Sie können deutlich erkennen, welche zwischen den DAX- und Power Query-Methoden schneller und besser ist.

Abschluss

Ein Vorteil der Verwendung der Power Query-Methode in LuckyTemplates besteht darin, dass die Daten bereits gespeichert sind. Das Maß kann also die Rate ermitteln und diese dann mithilfe einfacher Funktionen ermitteln.

Ein weiterer Vorteil dieser Methode besteht darin, dass sie nicht langsamer wird, wenn die Berechnung komplex wird.

Dies funktioniert jedoch nur, wenn die von Ihnen angeforderten Daten nicht im laufenden Betrieb konvertiert werden müssen. Wenn dies der Fall ist, müssen Sie DAX verwenden.

Alles Gute,