Power Query LuckyTemplates | Twórz nowe rekordy na podstawie pól daty

W tym samouczku chcę zaprezentować kilka metod tworzenia nowych rekordów na podstawie pól daty przy użyciu Power Query LuckyTemplates. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Pokażę dwie metody, jak to osiągnąć. Ten przykład pochodzi od członka , który zamieścił pytanie z datą i liczbą noclegów. Członek chciał wiedzieć, jak tworzyć nowe rekordy dla każdej nocy spędzonej w motelu za pomocą dodatku Power Query.

Spis treści

• Metoda 1: użycie funkcji Number.From w usłudze Power Query LuckyTemplates
• Metoda 2: Korzystanie z listy funkcji M.Daty w usłudze Power Query LuckyTemplates
• Wniosek

Metoda 1: użycie funkcji Number.From w usłudze Power Query LuckyTemplates

Jeśli jesteś zaznajomiony z obliczaniem dat w programie Excel, prawdopodobnie wiesz, że każdy dzień można przetłumaczyć na liczbę całkowitą i możesz to zrobić również tutaj, aby uzyskać odpowiednik liczbowy dla każdej daty. Możemy użyć funkcji Liczba.Od .

Odwołuję się do zapytania źródłowego, klikając je prawym przyciskiem myszy i wybierając opcję Odwołanie . Następnie przez ten mini stół przejdź do Add Custom Column . Teraz wprowadźmy funkcję Number.From i wskażmy pierwszą rzeczywistą kolumnę zawierającą nasze daty. Nie zapomnij o nawiasach zamykających i naciśnij OK .

Więc tutaj widzimy reprezentację całkowitą dla wartości daty. A ponieważ jest to liczba, możemy dodawać i odejmować dni. Celem jest utworzenie daty dla każdej kolejnej nocy pobytu .

Jeśli przyjrzymy się pierwszemu rekordowi, ta osoba została tylko na jedną noc, więc musimy zwrócić tylko pierwszą rzeczywistą wartość daty. Następnie, po raz drugi, ta osoba została na dwie kolejne noce. Musimy zwrócić obie wartości dla 13 i 14 grudnia. Możemy to osiągnąć, jeśli dodamy wartość z kolumny Noce do wartości z naszej kolumny Niestandardowe , a następnie odejmiemy jedną, ponieważ ta wartość początkowa została już przekazana.

Przejdźmy więc do paska formuły i rozwiń logikę, którą stworzyliśmy do tej pory. Musimy dwukrotnie użyć tej wartości numerycznej dla daty, więc zapiszę ją w zmiennej. Dodam wyrażenie let , a następnie dodam nazwę mojej zmiennej. Nazwę to Pierwszą Nocą . Następnie możemy dodać klauzulę in .

Listy można rozszerzać do nowych wierszy za pomocą zestawu inicjatorów list, którymi są nawiasy klamrowe. Możemy stworzyć listę z datami. Zaczniemy od naszej zmiennej FirstNight do zmiennej FirstNight plus cokolwiek jest w naszej liczbie nocy. Za pomocą zestawu nawiasów kwadratowych wskaż kolumnę Noce, a następnie odejmij jeden . Naciskam OK i mamy teraz listę.

Kiedy klikam z boku w białym polu, możemy zobaczyć zawartość tej zagnieżdżonej listy poniżej w drugim okienku podglądu.

Teraz za pomocą tych strzałek skierowanych w bok możemy rozwinąć do nowych wierszy . Następnie mogę ustawić typ danych na datę. Więc widzicie, że mamy teraz daty. W przypadku pierwszego rekordu zwracamy tę samą wartość, która jest w pierwszej wartości rzeczywistej. W przypadku tego drugiego rekordu mamy jeden rekord z 13 grudnia i jeden z 14 grudnia.

Metoda 2: Korzystanie z listy funkcji M.Daty w usłudze Power Query LuckyTemplates

W przypadku drugiej metody możemy użyć funkcji M o nazwie List.Dates . Odwołam się do mojego zapytania źródłowego i znowu, za pomocą tej mini ikony tabeli, dodam niestandardową kolumnę.

Dodam tę funkcję List.Date, a następnie umieszczę ją w nawiasie otwierającym. Po pierwsze, chce wartości początkowej jako daty i jest to w naszej pierwszej rzeczywistej kolumnie, która zawiera nasze wartości dat. Wybieramy więc, a następnie stawiamy przecinek. Dalej jest liczba jako liczba, co oznacza, ile dat chcesz wrócić do tej listy. Widzimy to w kolumnie Noce .

Dalej jest krok jako czas trwania. Pomyśl o tym jako o przerwie. Teraz szukamy kolejnych nocy i chcemy mieć ciągłą listę dat. Pojedynczy dzień ma numeryczny odpowiednik jeden, więc wprowadzę czas trwania od jeden , następnie nawias zamykający i naciśnij OK.

Ponownie mamy zagnieżdżoną listę. Dla pierwszego rekordu mamy tylko jedną wartość, a dla drugiego rekordu mamy 13 i 14 grudnia.

Następnie możemy ponownie rozwinąć to do nowych wierszy i ustawić odpowiedni typ danych do daty.

Teraz chcę zwrócić uwagę, że pierwszy obwód w funkcji List.Dates wymaga typu danych daty. Jeśli przejdę do kroku źródłowego i zmienię ten typ danych na datę/godzinę, zwróci błąd. To dlatego, że to nie mija randka. Możemy to łatwo naprawić, przechodząc do paska formuły i dodając Date.From .

Inną rzeczą, na którą chcę zwrócić uwagę, jest to, że możesz także utworzyć naprzemienną listę dat . Wszystko, co musisz zrobić, to na przykład zmienić ostatni parametr kroku na 2.

Wartości rekordów — ustrukturyzowana wartość w Power Query
Wyodrębnianie wartości z rekordów i list w Power Query
Tworzenie wartości dla każdej daty przy użyciu formuły Power Query

Wniosek

W tym blogu pokazałem dwa sposoby tworzenia nowych rekordów na podstawie pól daty. Pierwszy polega na wygenerowaniu listy dat przy użyciu inicjatorów list, a drugi na użyciu funkcji List.Dates.

Mam nadzieję, że ten samouczek okaże się pomocny. Możesz obejrzeć pełny samouczek wideo poniżej i sprawdzić poniższe linki, aby uzyskać więcej powiązanych treści.

Wszystkiego najlepszego!

Melisa