Konwertowanie kodu YYWWD na wartość daty w usłudze LuckyTemplates

W tym blogu będziemy omawiać, jak przekonwertować kod YYWWD na wartość daty . Ten samouczek został oparty na odpowiedzi na zapytanie członka  . Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Członek poprosił o sposób przekonwertowania swojego pola Potwierdzona data dostawy , które jest w formacie rok, tydzień i dzień tygodnia, i przekształcenie go w pole daty.

W swoim przykładzie członek wyjaśnia, że ​​kod 20145 powinien zostać przekonwertowany na 2020-04-03. Z tego możemy wywnioskować, że pierwsze dwie cyfry odpowiadają rokowi, kolejne dwie cyfry odpowiadają numerowi tygodnia, a ostatnia cyfra odpowiada dniu tygodnia.

Teraz, gdy mamy już pomysł, jak poradzić sobie z zapytaniem, zacznijmy.

Spis treści

• Łamanie kodu w celu uzyskania właściwej wartości daty
• Kolumna rok
• Kolumna tygodnia
• Kolumna dnia
• Wyszukiwanie trójstronne w celu uzyskania wartości daty
• Rozpoznawanie par
• Uzyskanie właściwej wartości daty
• Odejmowanie od kolumny dnia
• Wniosek

Łamanie kodu w celu uzyskania właściwej wartości daty

Aby ułatwić nam pracę, załadujmy nasze dane w edytorze Power Query .

Pierwszą rzeczą, jaką zauważyłem po załadowaniu danych do dodatku Power Query, było natychmiastowe wykonanie kroku typu zmiany, jak pokazano poniżej.

Ponieważ nie potrzebujemy tego, możemy usunąć ten krok, klikając znajdujący się obok niego przycisk X.

Teraz zacznijmy dzielić kod na osobne kolumny dla roku, tygodnia i dnia tygodnia.

Kolumna rok

Jak wspomniałem wcześniej, pierwsze dwie cyfry kodu odpowiadają rokowi wartości daty.

Aby dodać osobną kolumnę dla roku, musimy upewnić się, że wybrana jest kolumna Potwierdzona data dostawy . W tym celu przejdź do zakładki Dodaj kolumnę , kliknij Wyodrębnij , a następnie wybierz Pierwsze znaki .

Ponieważ potrzebujemy tylko pierwszych dwóch cyfr w kolumnie roku, wprowadź „2” w zakładce Liczba i kliknij OK.

Teraz zmodyfikujmy naszą nową kolumnę, edytując pasek formuły. Aby zmienić nazwę kolumny, zmień pierwsze znaki na pasku formuły na rok. Aby przekształcić go we właściwą wartość roku, dodajmy 2000 do każdej z naszych wartości.

Zauważ, że po zmodyfikowaniu naszej formuły otrzymamy błąd z powodu niezgodności typów.

Komunikat o błędzie mówi, że nie możemy zastosować operatora + do wartości liczbowych i typu tekstowego.

Aby to naprawić, musimy edytować naszą formułę, aby wartość tekstowa została przekonwertowana na liczbę. W tym celu użyjemy funkcji Number.from i umieścimy kolejne części w nawiasach. Następnie zmień zwracany typ z tekstowego na liczbowy.

Kolumna tygodnia

Wyodrębnijmy teraz numer tygodnia z kodu.

Ponownie upewnij się, że wybrana jest kolumna Potwierdzona data dostawy , przejdź do zakładki Dodaj kolumnę, kliknij Wyodrębnij, a następnie wybierz Zakres.

Aby uzyskać trzecią i czwartą cyfrę naszego kodu, musimy zacząć liczyć od 0, ponieważ Power Query jest oparte na zerach. Oznacza to, że aby uzyskać trzecią cyfrę naszego kodu, nasz zakres powinien zaczynać się od 2.

A ponieważ potrzebujemy tylko 2 cyfr z kodu na numer tygodnia, wpiszemy 2 w polu Liczba znaków.

Ponownie, ta sama logika ma tutaj zastosowanie. Aby przekształcić etykietę kolumny w jej właściwą nazwę, zmień Zakres tekstu na Tydzień na pasku formuły.

Dodaj także funkcję Number.from do naszej formuły, umieść kolejne części w nawiasach i zmień zwracany typ na liczbę.

Kolumna dnia

W przypadku kolumny dzień potrzebujemy tylko ostatniej cyfry kodu. Aby go wyodrębnić, wybierz kolumnę Potwierdzona data dostawy , przejdź do zakładki Dodaj kolumnę, wybierz Wyodrębnij, a następnie kliknij Ostatnie znaki .

Wpisz 1, ponieważ z kodu, który musimy wyodrębnić, została tylko jedna cyfra.

Podobnie jak to zrobiliśmy wcześniej, na pasku formuły zmień ostatnie znaki na dzień, aby nasza nowa kolumna miała swoją właściwą nazwę.

Ponownie dodaj funkcję Number.from , umieść następujące części formuły w nawiasach i zmień zwracany typ na liczbę.

Wyszukiwanie trójstronne w celu uzyskania wartości daty

Teraz, gdy mamy już wszystkie komponenty potrzebne do pobrania pojedynczej daty z naszej tabeli kalendarza, pozostaje nam już tylko przeprowadzić wyszukiwanie trójstronne .

Aby to zrobić, przejdź do zakładki Strona główna i wybierz Scal zapytania.

Ponieważ zamierzamy połączyć się z naszą tabelą Daty, kliknij rozwijaną kartę i wybierz Daty.

Rozpoznawanie par

Teraz zidentyfikujemy pary, które do siebie pasują.

Z górnej tabeli wybierz kolumnę roku, a następnie sparuj ją z kolumną roku poniższej tabeli.

Aby wybrać inną parę, naciśnij przycisk Ctrl na klawiaturze, wybierz kolumnę tygodnia z górnej tabeli i sparuj ją z kolumną numeru tygodnia w poniższej tabeli.

Na koniec, wciąż naciskając przycisk Ctrl, wybierz kolumnę dnia z górnej tabeli i sparuj ją z kolumną Dzień w tygodniu poniższej tabeli.

Aby upewnić się, że kolumny są sparowane poprawnie, sprawdź, czy numery indeksów pasują do siebie.

Jak pokazano poniżej, kolumny Rok powinny mieć indeks 1, kolumna Tygodnie powinna mieć indeks 2, a kolumna Dni powinna mieć indeks 3.

Uzyskanie właściwej wartości daty

Ponieważ potrzebujemy tylko kolumny dat, odznacz wszystkie pozostałe kolumny i pozostaw zaznaczoną kolumnę dat, jak pokazano poniżej.

Jak widać, pierwsza wartość w naszej nowej kolumnie dat to 4-4-2020. Pamiętaj jednak, że na forum kod 20145 należy zamienić na 3-4-2020.

Dzieje się tak, ponieważ jeśli przyjrzymy się naszej tabeli dat, zobaczymy, że w kolumnie DayInWeek liczenie zaczyna się od 0 do 6, a nie od 1 do 7.

Odejmowanie od kolumny dnia

Aby to naprawić, odejmijmy 1 od wszystkich wartości w naszej kolumnie Dzień.

W sekcji Zastosowane kroki kliknij krok, do którego dodaliśmy kolumnę dnia. To był krok wstawiania ostatnich znaków .

Na pasku formuły odejmij 1 zaraz po nawiasach.

Po wykonaniu tej czynności możemy zobaczyć, że mamy już poprawne wartości w naszej kolumnie daty.

Na koniec ostatnią rzeczą, którą musimy zrobić, to posprzątać i usunąć kolumny, których już nie potrzebujemy.

Aby to zrobić, wybierz opcję Wybierz kolumny, odznacz wszystko inne i pozostaw zaznaczoną kolumnę Potwierdzona data dostawy oraz kolumnę Data.

I skończyliśmy! Efekt naszej pracy powinien wyglądać tak.

Wyświetlanie wyników do daty bieżącej lub określonej daty w usłudze LuckyTemplates
Zapobieganie prognozowaniu wyników od początku roku do przodu
Filtrowanie danych według niestandardowych lat i kwartałów obrachunkowych przy użyciu kolumn obliczeniowych w usłudze LuckyTemplates

Wniosek

Na tym blogu przekonwertowaliśmy kod w formacie roku, tygodnia i dnia tygodnia na odpowiednią wartość daty. Rozbiliśmy kod na części i utworzyliśmy z niego 3 kolumny. Korzystając z wyszukiwania trójstronnego, połączyliśmy te kolumny, aby uzyskać pojedynczą wartość daty.

Technika, której użyliśmy do rozwiązania problemu na forum pomocy technicznej, może być wykorzystana w innych scenariuszach. Upewnij się więc, że dobrze je rozumiesz.

Wszystkiego najlepszego,

Melisa