Tworzenie wartości dla każdej daty przy użyciu formuły Power Query

W tym samouczku dowiesz się, jak tworzyć wartości dla każdego dnia przy użyciu formuły dodatku Power Query w usłudze LuckyTemplates.

Tworzenie wartości dla dat to ważna technika, której należy się nauczyć w usłudze LuckyTemplates. Może dostarczyć Ci danych, które chcesz zobaczyć w ciągu dni w swoim modelu. Ta technika obejmuje również luki między kolumnami.

Jest to świetne narzędzie do użycia, gdy w tabelach dat brakuje informacji.

Oto przewodnik krok po kroku, jak to zrobić: Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Spis treści

• Ustaw dodatkowe wartości
• Utwórz warunek filtru dla identyfikatorów
• Dodaj warunek filtra dla dat
• Sprawdź i posortuj kolumnę daty
• Użyj funkcji Table.FirstN
• Uzyskaj różnicę dni między 2 wartościami dat
• Określ krok wagi
• Uzupełnij lukę
• Uzyskaj projekcję wagi dat
• Wniosek

Ustaw dodatkowe wartości

W przypadku zestawów danych zawierających identyfikatory, daty i wagi należy wypełnić lukę między tymi pomiarami. Można to zrobić, tworząc zakres dat, aby rozłożyć różnicę na dni.

Aby to zrobić, przejdź do Power Query, aby zobaczyć swoją tabelę.

Musisz załadować kopię swoich danych do pamięci. Aby tak się stało, należy użyć miary Table.Buffer .

Jeśli klikniesz TableBuffer, zobaczysz, co robi miara.

Następnie wróć do swojego zestawu danych i kliknij fx na pasku formuły. Musisz dodać Table.Buffer w mierze.

Następnie zmień nazwę tego kroku na BufferedTable .

Następnie dodaj niestandardową kolumnę.

Utwórz warunek filtru dla identyfikatorów

W tej formule dodatku Power Query zmień przywoływany krok z BufferedTable na RenameColumns , ponieważ najpierw należy sprawdzić BufferedTable.

Aby sprawdzić BufferedTable, użyj Table.SelectRows , która zwraca tabelę pasującą do wybranego warunku.

Dlatego dla każdego wyrażenia w formule użyj Table.SelectRows, a następnie użyj BufferedTable.

Jako warunek filtrowania użyj niestandardowej funkcji końca i wpisz BT dla buforowanej tabeli . Sprawdź kolumnę identyfikatora, jeśli jest równa identyfikatorowi z bieżącego rekordu. Następnie uzyskaj dostęp do bieżącego rekordu, używając podkreślenia jako operatora dostępu, a następnie odwołaj się do tej samej kolumny identyfikatora.

Otrzymasz przefiltrowaną tabelę zawierającą tylko identyfikatory pasujące do bieżącego rekordu. 

Dodaj warunek filtra dla dat

Musisz przefiltrować kolumnę daty, ponieważ potrzebujesz tylko dat, które są równe bieżącemu rekordowi.

Użyj tych funkcji, aby sprawdzić kolumnę daty BT:

Ta kolumna musi być większa lub równa dacie z bieżącego rekordu.

Dzięki temu tabela będzie zawierać dwa wiersze zamiast trzech:

Sprawdź i posortuj kolumnę daty

Musisz sprawdzić, czy sortowanie kolumny daty jest poprawne za pomocą Table.Sort . Funkcja Table.Sort sortuje tabelę według jednej lub kilku kolumn w zależności od żądanych kryteriów.

Składnia w tym przykładzie jest dość skomplikowana, więc musisz wykonać drążenie w dół tematu tabeli.

Musisz kliknąć tabelę prawym przyciskiem myszy i dodać ją jako nowe zapytanie.

Kliknięcie powoduje utworzenie niestandardowego zapytania zawierającego tę tabelę.

Następnie kliknij kolumnę daty i wybierz Sortuj rosnąco.

Skopiuj całą formułę:

Wróć do głównego zapytania i wklej je na pasku formuły:

Musisz usunąć odwołanie do Table.Sort, ponieważ tabela, która musi przekazać parametr, powinna być wynikiem Table.SelectRows.

Gdy skończysz, ponownie dodaj warunki sortowania. Po zastosowaniu zmian tabela jest teraz poprawnie posortowana według daty.

Użyj funkcji Table.FirstN

W tym kroku potrzebujesz tylko 2 rekordów zamiast 3.

Aby to naprawić, wróć do zapytania niestandardowego. Kliknij Zachowaj wiersze, a następnie Zachowaj górne wiersze.

Wprowadź 2 w pustym polu dla liczby wierszy.

Zauważysz, że na pasku formuły pojawia się funkcja Table.FirstN. Table.FirstN zwraca pierwsze wiersze tabeli w zależności od wartości countOrCondition .

Wstaw tę funkcję wraz z liczbą potrzebnych wierszy w głównym zapytaniu. Dołącz tabelę typów funkcji na końcu.

Każdy obiekt tabeli w zestawie będzie miał teraz 1 lub 2 wiersze.

Uzyskaj różnicę dni między 2 wartościami dat

Następnym krokiem jest uzyskanie liczby dni między wartościami dat i wyodrębnienie daty, która znajdowała się w drugim rekordzie.

Zacznij od dodania niestandardowej kolumny. Nazwij to Dni . Następnie postępuj zgodnie z tą formułą dodatku Power Query:

Składnia zapewnia, że ​​w tabeli znajdują się 2 rekordy. Jeśli jest tylko 1, różnica dnia wynosi 0, ponieważ nie ma następnego rekordu. Jeśli jest 2, możesz uzyskać różnicę z wartości dat.

Następnie dodaj numer typu w formule w ten sposób.

Jak widać, różnica wynosi 385 dni od 14 września 2017 r. do 4 października 2018 r.

Określ krok wagi

Następnie musisz obliczyć, jaka powinna być waga na każdy dzień.

Musisz ponownie dodać niestandardową kolumnę, nazwać ją WeightStep i skopiować tę formułę dodatku Power Query:

Składnia sprawdza, czy tabela ma więcej niż 1 wiersz, odejmuje wagę od bieżącego rekordu i dzieli tę różnicę przez liczbę dni.

Ponownie dodaj numer typu do formuły, aby zobaczyć wagę każdego dnia.

Uzupełnij lukę

Teraz, gdy masz już liczbę dni, możesz utworzyć listę dat, aby wypełnić lukę w zbiorze danych.

Najpierw dodaj kolejną niestandardową kolumnę i nazwij ją DateKey . Następnie postępuj zgodnie z tą formułą dodatku Power Query:

Formuła sprawdza, czy istnieje różnica dat. Jeśli tak, tworzy listę dat pomiędzy datą początkową pochodzącą z bieżącego rekordu i rozszerza ją o liczbę dni. Ale jeśli nie ma żadnych różnic, zwraca datę.

to jest wynik:

Tematy tabeli, które nie miały żadnych różnic, miały zwróconą datę; stoły, które to zrobiły, otrzymały listę dat.

Jeśli rozwiniesz DateKey do nowych wierszy, możesz zobaczyć zapisy ze wszystkich dni.

Uzyskaj projekcję wagi dat

Po utworzeniu listy dat musisz uzyskać prognozę wagi dla dat.

Najpierw dodaj niestandardową kolumnę i nadaj jej nazwę WeightProjection . Następnie wprowadź tę formułę dodatku Power Query:

Składnia pobiera wartość wagi, dodaje ją do wartości DateKey, odejmuje ją od wartości daty i mnoży ją przez WeightStep.

Ponownie dodaj numer typu na końcu formuły.

Masz teraz przewidywaną wagę dla każdej daty.

Usuń kolumny pomocnicze: Custom, Days i WeightStep, korzystając z poniższej formuły, i zmień format kolumny DateKey na Date.

Wniosek

Z tego samouczka dowiedziałeś się, jak wypełnić luki w zbiorze danych, tworząc wartości na każdej liście dat.

Te wartości dat pomagają uzyskać cenne informacje z modelu. Jeśli w tabelach brakuje informacji, możesz użyć tej techniki, aby uczynić je bardziej wszechstronnymi.

W tabeli mogą znajdować się istotne dane, które pomijasz, dlatego zawsze maksymalizuj każdy wiersz i kolumnę.

Wszystkiego najlepszego,

Melisa