Power BI

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żdej daty w tabelach przy użyciu formuły dodatku Power Query w usłudze LuckyTemplates.

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

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ę.

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

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.

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

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

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

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

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

Następnie dodaj niestandardową kolumnę.

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

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

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.

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

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.

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

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.

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

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

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

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:

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

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:

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

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.

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

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

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

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

Skopiuj całą formułę:

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

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

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

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.

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

Wprowadź 2 w pustym polu dla liczby wierszy.

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

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.

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

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:

Tworzenie wartości dla każdej daty przy użyciu formuły 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.

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

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

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

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:

Tworzenie wartości dla każdej daty przy użyciu formuły 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.

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

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:

Tworzenie wartości dla każdej daty przy użyciu formuły 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:

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

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.

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

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

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

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

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:

Tworzenie wartości dla każdej daty przy użyciu formuły 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.

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

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

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

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

More in Power BI