Rozwiązywanie brakujących kursów walut za pomocą dodatku Power Query w usłudze LuckyTemplates

W tym samouczku dowiesz się, jak używać dodatku Power Query do przeliczania walut w usłudze LuckyTemplates. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Metoda omówiona na tym blogu pomoże Ci rozwiązać problemy w tabeli kursów walut przy użyciu ostatniego dostępnego kursu.

Zrozumiesz, jak rozwiązywać problemy, gdy tabela kursów walut nie zawiera kursów walut na każdy dzień.

Spis treści

• Identyfikacja brakujących danych
• Oddzielanie różnych walut
• Uzupełnianie brakujących stawek
• Połączenie wszystkich walut
• Tworzenie relacji w modelu danych
• Porównanie metod DAX i Power Query
• Wniosek

Identyfikacja brakujących danych

Ten problem może wystąpić, jeśli tabela dat nie zawiera danych dotyczących dni wolnych od pracy lub weekendów.

Jeśli nie masz stawki z żadnego dnia i nie uwzględnisz jej, metryki, takie jak Łączna sprzedaż, nie zostaną przekonwertowane.

Jeśli spojrzysz na tę tabelę kursów walut, znajdziesz w niej kursy w euro na styczeń 2016 r.

Ale zauważ, że brakuje 27 i 28 dnia i nie ma stawki. Tak więc, jeśli przejdziesz do macierzy, zobaczysz, że nie ma stawek dla 27 i 28.

Kurs waluty z miarą Brakująca jest po prostu sumą tabeli Faktyczne kursy walut.

Suma 26,6983 w tabeli jest bez znaczenia. Jest to po prostu suma wszystkich kursów walut powyżej niej, których nie trzeba sumować, ponieważ są takie, jakie są w bieżącym dniu.

Teraz można to naprawić w Power Query , ponieważ jest to bardziej problem z modelowaniem danych, który obejmuje zapytania i agregację. Można to również zrobić za pomocą języka DAX, ale jest to łatwiejsze i szybsze dzięki Power Query w usłudze LuckyTemplates.

Kliknij więc opcję Przekształć dane , a następnie otwórz okno dodatku Power Query.

To jest tabela z brakującymi danymi. Możesz również zobaczyć w tej tabeli kursów walut, że zawiera ona euro, funty i dolary.

A oto stół finałowy z wartościami 27 i 28 stycznia. To jest tabela lub dane wyjściowe, które powinieneś mieć.

Importuj dane z dowolnego źródła, z którego je otrzymujesz, i po prostu zmień typ danych.

Oddzielanie różnych walut

Następnie, jeśli masz więcej niż jedną walutę, ważne jest, aby je oddzielić i pogrupować według ich znaczników.

Grupując je, pamiętaj, aby zgrupować je we wszystkich wierszach, aby po kliknięciu na przykład euro wyświetlały się tylko kursy walut w euro.

Możesz również zobaczyć, że brakuje tutaj dni, a także waluty funta.

W usłudze LuckyTemplates istnieje coś takiego, jak funkcja Invoke Custom Function . Jest to funkcja z FillMissingRates.

Jeśli klikniesz Euro, zobaczysz pełną tabelę zawierającą brakujące dane, które widziałeś w rozwiniętej tabeli. Jest posortowana od najstarszych do najnowszych stawek.

Dowiedz się, co robią funkcje niestandardowe. Jeśli klikniesz ikonę koła zębatego w pobliżu funkcji Wywołaj funkcję niestandardową, zobaczysz nazwę kolumny i źródło, które ciągnie w kolumnie Wszystkie.

Aby to zrobić, zacznij od podstawowej tabeli. Wykonaj przekształcenia, uzyskaj pożądany wynik, a następnie użyj go do zbudowania funkcji. Ma to większy sens niż próba zrobienia tego w tabeli, w której znajdują się wszystkie inne podtabele.

Na przykład, jeśli chcesz skupić się na euro, przefiltruj je do paska.

Aby dowiedzieć się, jakich dni brakuje, połącz ją z tabelą dat.

Oto podstawowa przefiltrowana tabela walut i tabela DimDate:

W przypadku Join Kind możesz użyć Full Outer, aby wprowadzić wszystkie wiersze z obu stołów. To pokazuje, czego brakuje i pasujące stawki.

Kiedy go scalisz, otrzymasz pełną tabelę jako tabelę podrzędną.

Następnie posortuj wiersze, a następnie rozwiń DimDate, aby zawierała tylko kolumnę Data.

Teraz widać, że wszystko się zgadza. A kiedy posortujesz wiersze od najstarszych do najnowszych, możesz również zobaczyć dwa brakujące dni z wartościami pustymi.

Uzupełnianie brakujących stawek

Ważne jest, aby były w tej kolejności, ponieważ zamierzasz wypełnić wszystkie wartości zerowe we wszystkich kolumnach z wyjątkiem Data.1.

Po jego wypełnieniu zobaczysz, że wartości zerowe zostały zmienione na euro i ostatni dostępny kurs.

Następnie usuń z tabeli niepotrzebne kolumny, takie jak kolumna Data.

Kolumna Date.1 zawiera już daty, więc kolumna Date nie jest potrzebna. Następnie ustaw Współczynnik krzyżowy na Liczba dziesiętna.

Na koniec zmień kolejność i nazwy kolumn oraz upewnij się, że ustawione są typy danych.

Masz teraz kompletną tabelę kursów walut.

Działa to jednak tylko w przypadku tej tabeli. Musisz znaleźć sposób na zastosowanie tej samej logiki do tabeli, w której pogrupowane są waluty.

Połączenie wszystkich walut

Jeśli masz różne waluty, trudno jest utrzymywać różne tabele kursów. Musisz je więc połączyć, aby utworzyć jedną tabelę walut.

Aby rozpocząć, weź istniejącą tabelę i utwórz funkcję, klikając Tabela i wybierając opcję Utwórz funkcję . Następnie wprowadź nazwę funkcji.

Następnie przejdź do Edytora zaawansowanego . Usuń obliczenia, których nie będziesz potrzebować. W takim przypadku pozbądź się źródła, filtruj i zmieniaj składnię.

Następnie nazwij parametr Source, a następnie wprowadź Let. Ponieważ filtrowane wiersze zostały usunięte, zmień wartość w składni scalonych zapytań na źródło. Następnie usuń Źródło na końcu składni, a następnie kliknij Gotowe.

Teraz przejdź do Dodaj kolumnę, kliknij Wywołaj funkcję niestandardową. Wprowadź nazwę kolumny, na przykład Wszystkie dane. W przypadku zapytania funkcji wybierz funkcję, która została utworzona wcześniej. Wybierz Wszystkie jako źródło, a następnie kliknij OK.

Następną rzeczą do zrobienia jest usunięcie wszystkich kolumn z wyjątkiem wszystkich danych. Następnie rozwiń tę kolumnę i odznacz ustawienie „Użyj oryginalnej nazwy kolumny jako prefiksu”. Następnie kliknij OK i zmień typy kolumn.

Ustaw typ kolumn Date, Ticker i Crossrate odpowiednio na Date, Text i Decimal. Po zakończeniu kliknij Zamknij i zastosuj.

Otrzymasz teraz łączną tabelę kursów walut bez brakujących dat i kursów.

Tworzenie relacji w modelu danych

W swoim modelu danych możesz zobaczyć tabelę FactCurrencyRates . Teraz utwórz relację Ticker to Ticker i Date to Date z tą tabelą oraz tabelami DimCurrencyRates i DimDate.

W swojej macierzy wprowadź kurs waluty, którego nie brakuje , aby zobaczyć, że teraz zawiera kursy dla 27. i 28. dnia, wszystkie wykonane w dodatku Power Query.

Kolumna Ostatnio zgłoszony kurs waluty pokazuje również kursy z brakujących dni. Pokazuje te same stawki i wartości. Ale różnica między nimi polega na tym, że ta kolumna jest tworzona w języku DAX.

To jest miara i składnia tej kolumny. Nie jest to tak proste, jak użycie samej funkcji w dodatku Power Query.

Patrząc na tabelę, zobaczysz kolumnę Przeliczona sprzedaż bez braków. Pokazuje sprzedaż przeliczoną przy użyciu kolumny Brak brakujących kursów walut. Druga kolumna z przekonwertowaną sprzedażą wykorzystuje kolumnę utworzoną w języku DAX.

Funkcją używaną do uzyskiwania przekonwertowanej sprzedaży jest . Jeśli spojrzysz na miarę dla tej kolumny, zobaczysz, że SUMX przechodzi przez tabelę DimDate.

Porównanie metod DAX i Power Query

W rzeczywistości możesz porównać obie przekonwertowane kolumny sprzedaży, które korzystały z języka DAX i dodatku Power Query w usłudze LuckyTemplates.

Aby to zrobić, pozbądź się wszystkich kolumn oprócz przekonwertowanych kolumn sprzedaży. Następnie uruchom analizator wydajności i kliknij Rozpocznij nagrywanie.

Następnie otwórz Studio DAX. Jest to aplikacja, która pokazuje, jak i dlaczego coś działa w Twoim modelu. Następnie skopiuj zapytanie swojej macierzy i wklej je w studio.

Dla porównania najpierw zamień jeden z nich w komentarz, aby nie kolidował z drugą testowaną kolumną.

Następnie załaduj chronometraż serwera i plan zapytań. Ponieważ porównujesz wydajność, najpierw wyczyść pamięć podręczną przed uruchomieniem porównania.

Po uruchomieniu testu widać, że kolumna, która korzystała z DAX-a dostała mnóstwo skanów i ma łączny czas 71 milisekund.

Teraz uruchom drugą kolumnę, która korzystała z zapytania Power. Ale najpierw zamień drugą kolumnę w składni na komentarz.

Możesz zobaczyć, że kolumna działała tylko przez 25 milisekund i ma tylko 7 skanów.

Możesz wyraźnie zobaczyć, która działa szybciej i lepiej między metodami DAX i Power Query.

Wniosek

Zaletą korzystania z metody Power Query w LuckyTemplates jest to, że dane są już przechowywane. Tak więc miara może znaleźć stawkę, a następnie wydobyć ją za pomocą prostych funkcji.

Inną cechą tej metody jest to, że nie zwalnia, jeśli obliczenia stają się skomplikowane.

Działa to jednak tylko wtedy, gdy dane, o które prosisz, nie muszą być konwertowane w locie. Jeśli tak, musisz użyć języka DAX.

Wszystkiego najlepszego,