Wieczny kalendarz świąt — POTW #12 (rozwiązanie Power Query)

W tym poście na blogu przyjrzymy się mojemu rozwiązaniu . W tym wyzwaniu ponownie przyjrzeliśmy się pierwszemu wyzwaniu Problem tygodnia LuckyTemplates, w którym stworzyliśmy wieczny kalendarz świąt z połączenia konkretnych i względnych dat. Ale tym razem będziemy używać tylko dodatku Power Query . Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Jeśli utkniesz na samym podziale problemu, możesz wrócić do , aby sprawdzić, czy w którymś z podejść istnieją elementy, które mogą pomóc w rozwiązaniu tego wyzwania.

Czytając zgłoszenia, widziałem mieszankę ludzi wykorzystujących interfejs użytkownika i piszących niestandardowe funkcje , aby stworzyć wieczny kalendarz. Analiza każdego wpisu była przyjemnością, a jeśli chcesz się zainspirować, zachęcam cię do zrobienia tego samego.

Kiedy sam pracowałem nad tym scenariuszem, stworzyłem wiele rozwiązań. Ten, z którym poszedłem, najlepiej ilustruje, jak można nie tylko rozbić problem na mniejsze części , ale także uczynić je widocznymi . Jest to bardzo pomocne w przypadku napotkania błędu lub nieoczekiwanych wyników.

Spis treści

• Punkt wyjścia do stworzenia wiecznego kalendarza
• Pobieranie lat z tabeli dat
• Uzyskiwanie liczby z kolumny dnia
• Wywoływanie funkcji niestandardowej dla wiecznego kalendarza świąt
• Tworzenie listy dat
• Stosowanie logiki warunkowej
• Przeglądanie kodu M dla wiecznego kalendarza świąt
• Wniosek

Punkt wyjścia do stworzenia wiecznego kalendarza

Ten surowy świąteczny stół to nasz punkt wyjścia. Aby ułożyć datę, potrzebujemy roku, miesiąca i dnia dla określonych dat. Brakuje tylko roku.

Ale w przypadku dat względnych musimy wykonać trochę więcej pracy. Na początek musimy podzielić tę kolumnę dnia na liczbę i dzień tygodnia. Przeanalizujmy te kroki.

Pobieranie lat z tabeli dat

Aby uczynić to rozwiązanie dynamicznym, zamierzam pobrać lata z tabeli dat . Na pasku formuły widać, że użyłem projekcji, aby uzyskać tabelę jednokolumnową z mojej tabeli dat. To oczywiście będzie zawierało duplikaty. Aby je usunąć, dodałem Table.Distinct .

Kiedy kliknę z boku w białe miejsce obok którejkolwiek z tych tabel, zobaczysz także listę z różnymi latami obecnymi w moim zapytaniu o tabelę dat.

Otrzymaliśmy nazwę miesiąca , ale tak naprawdę będziemy potrzebować numeru miesiąca . Teraz, kiedy połączymy rok z nazwą miesiąca, możemy użyć funkcji Date.From , aby zwrócić pierwszy dzień tego miesiąca jako wartość daty.

Dodając Date.Month , pozostaje nam tylko numer miesiąca dla tej konkretnej daty. Dla stycznia zwraca 1. Dla lutego zwraca 2 i tak dalej.

Uzyskiwanie liczby z kolumny dnia

Aby uzyskać liczbę z kolumny dnia, zdecydowaliśmy się nie dzielić kolumny i zamiast tego wyodrębnić wartość. Jak widać na pasku formuły, użyliśmy Text.BeforeDelimiter i wykorzystaliśmy tę spację do zidentyfikowania prawej i lewej części tego ciągu tekstowego.

Jeśli nie ma miejsca, chcemy zwrócić wszystko, co pozostaje w kolumnie dzień. Tak więc dla pierwszego rekordu, który zwraca 1. Dla drugiego rekordu, który zawiera tę spację, zwraca pierwszą część ciągu tekstowego, słowo Third .

Zastosujemy podobne podejście, aby uzyskać nazwę daty, ale użyjemy funkcji List.PositionOf . Zwraca przesunięcie od zera wartości na liście przekazanej jako pierwszy argument i zwraca -1, jeśli wartość nie pojawia się na tej liście.

Dla pierwszego rekordu przekazuje wartość 1, której nie ma na naszej liście. Dlatego zwraca wartość -1. W przypadku drugiego rekordu zwraca wartość Monday .

Przekazuje to do naszej funkcji i zwraca 1, ponieważ jest to druga pozycja na naszej liście. Następnie zamienimy wszystkie wartości -1 na null .

W tym momencie utworzyliśmy wszystkie bloki konstrukcyjne potrzebne do wywołania funkcji niestandardowej.

Wywoływanie funkcji niestandardowej dla wiecznego kalendarza świąt

Na pasku formuły widzimy, że funkcja przyjmuje cztery argumenty: rok , miesiąc , cyfrę i dzień tygodnia .

Ale w jaki sposób określa tę wartość daty? Ponownie użyjemy List.PositionOf , aby uzyskać wartość liczbową dla cyfry. Mamy listę zawierającą pierwszą, drugą, trzecią i czwartą. Kiedy przekazujemy liczbę do List.PositionOf , zwraca ona przesunięcie od zera.

Ponownie, jeśli wartość nie jest obecna, zwraca -1. Tak więc dla pierwszego rekordu przekazujemy wartość 1. 1 nie występuje na tej liście, dlatego zwraca wartość -1. W przypadku drugiego rekordu przekazujemy wartość Third, która jest trzecią pozycją na naszej liście. To zwraca wartość 2.

Tworzenie listy dat

Utwórzmy teraz listę dat. Określimy datę początkową dla tego miesiąca, określimy liczbę dni w tym miesiącu i użyjemy List.Dates do utworzenia listy zawierającej wszystkie dni w tym miesiącu.

Następnie użyjemy List.Select , aby zachować tylko te daty, które przypadają na określony dzień tygodnia.

Kiedy klikniemy obok dowolnej z tych list, zobaczymy, że zawiera ona tylko te cztery elementy.

Stosowanie logiki warunkowej

Na koniec zastosujemy logikę warunkową, aby skonstruować lub pobrać datę. Jeśli dzień tygodnia ma wartość null , co wskazuje, że mamy określoną datę, możemy użyć wewnętrznej #daty do skonstruowania wartości daty.

Jeśli cyfra jest równa ostatniej, możemy użyć funkcji List.Reverse , aby odwrócić kolejność na liście i wyodrębnić pierwszą wartość.

Jeśli chcemy zmienić ten porządek rosnący na malejący, zawsze możemy wyodrębnić ostatnią datę z tego zakresu dat. Nie ma znaczenia, czy na liście mamy cztery, czy pięć elementów. Odwracając kolejność i wyodrębniając pierwszą pozycję, zawsze mamy ostatnią datę.

We wszystkich innych przypadkach używamy List.Skip .

Możemy wyodrębnić wartość na podstawie tej pozycji na liście. W Święto Dziękczynienia cyfrą jest czwarta, a czwarta zwraca 3. Spowoduje to pominięcie pierwszych trzech wartości na liście i zawsze zwróci czwartą pozycję.

Złóżmy to wszystko razem i przejdźmy do zapytania o rozwiązanie. W kroku źródłowym nazywamy tabelę dat świąt RAW. Następnie dodamy kolumnę, która zwraca listę z datami.

Przeglądanie kodu M dla wiecznego kalendarza świąt

Sprawdźmy kod M dla kolumny. Dla każdego wiersza w tabeli zagnieżdżono tabelę zawierającą odrębne lata z tabeli dat. Następnie przekształciliśmy wartości w tej kolumnie roku, wywołując funkcję niestandardową.

Oczywiście, aby móc wywołać funkcję niestandardową, musimy najpierw przeprowadzić konfigurację. Musimy uzyskać numer miesiąca , wartość liczbową i oczywiście dzień tygodnia . Następnie z tej zagnieżdżonej tabeli zachowaliśmy listę z datami.

Kiedy klikniemy z boku w białym polu, możemy zobaczyć podgląd tej listy poniżej.

Następnym krokiem jest rozwinięcie tej listy i dodanie CelebratedOnDate .

Oto logika, której użyliśmy w tym obliczeniu: jeśli data wypada w sobotę, odejmiemy dzień, aby wylądować w piątek. Jeśli jest to niedziela, dodamy dzień do lądowania w poniedziałek. Jeśli nie jest to sobota lub niedziela, chcemy mieć wartość null.

Ostatnim krokiem jest zmiana typów.

Wniosek

Tylko krótkie przypomnienie, że to ciągła seria, która odbywa się w każdą pierwszą i trzecią środę na forum LuckyTemplates. Problem opublikowany w pierwszym tygodniu wymaga rozwiązania opartego na języku DAX, podczas gdy problem w trzecim tygodniu wymaga rozwiązania opartego na dodatku Power Query.

Jeśli podobały Ci się treści omówione w tym POTW, zasubskrybuj kanał telewizyjny LuckyTemplates, aby uzyskać więcej. Cały czas publikujemy ogromną ilość treści ode mnie i wielu twórców treści, których celem jest ulepszenie sposobu, w jaki korzystasz z usługi LuckyTemplates i Power Platform.

Melisa