Power Query M: Naprzemienny wzorzec 0-1 w tabeli dat

Ten samouczek koncentruje się na używaniu funkcji number.mod dodatku Power Query do tworzenia naprzemiennego wzorca 0-1 w tabeli dat. Rozwiązanie, które zamierzam wam pokazać, pochodzi od jednego z naszych zaawansowanych użytkowników społeczności, Rajesha, z LuckyTemplates. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Celem jest wygenerowanie sekwencji czterech zer na przemian z czterema jedynkami, a następnie powtarzanie jej przez cały dzień.

Rajesh był na miejscu w swojej analizie i rozbiciu problemu za pomocą funkcji Power Query M, aby go rozwiązać. Powiedziawszy to, przejdźmy do dodatku Power Query.

Spis treści

• Funkcja Power Query M Number.Mod
• Tworzenie niestandardowej kolumny dla wzorca Power Query M
• Tworzenie konstrukcji IF-THEN-ELSE
• Wniosek

Funkcja Power Query M Number.Mod

Sercem naszego rozwiązania będzie Power Query M, a konkretnie funkcja number.mod . Co to robi?

Możesz sprawdzić wewnętrzną dokumentację dowolnej funkcji M, wprowadzając jej nazwę bez nawiasów, jak pokazano tutaj na pasku formuły. Ta funkcja zwraca resztę z dzielenia całkowitego. Zajmuje do trzech parametrów, pierwsze dwa, liczbę i dzielnik, który jest również liczbą.

Teraz zacznijmy. Wybiorę moje przykładowe zapytanie zawierające przygotowany przeze mnie zakres dat.

W wielu przypadkach numer. mod jest używany w połączeniu z kolumną indeksu, która jest następnie używana jako jej pierwszy parametr. Ale nie zamierzam tego robić, ponieważ jeśli się nad tym zastanowić, wartość daty jest liczbą całkowitą. Tak więc tworzenie indeksu, który ma taką samą szczegółowość jak kolumna Data, w której każdy wiersz ma unikalną wartość, jest czymś, czego możemy uniknąć.

Wszystko, co musimy zrobić, aby naśladować indeks, to od każdej daty w kolumnie Data odjąć pierwszą datę w naszej tabeli dat. Aby to zrobić, możemy zapisać wartość daty początkowej w zmiennej, abyśmy mogli jej używać w kółko. Uważam, że Edytor zaawansowany jest najlepszym miejscem do tego.

Otworzę więc okno Edytora zaawansowanego i utworzę nową zmienną. Nazwijmy to StartDate . Zapisałem tutaj swoją Pierwszą randkę, więc po prostu ją skopiuję i wkleję. Nie zapomnij o przecinku na końcu, a teraz możemy zastąpić tę Pierwszą randkę naszą zmienną Data rozpoczęcia.

Jak widać nic się nie zmieniło.

Tworzenie niestandardowej kolumny dla wzorca Power Query M

Za pomocą tej minitabeli dodamy niestandardową kolumnę.

Nazwijmy tę kolumnę Pattern i wprowadź funkcję Power Query M, number.mod . Widzisz tutaj dwa parametry, liczbę jako liczbę dopuszczającą wartość null i dzielnik jako liczbę dopuszczającą wartość null. Wymaga więc dwóch wartości typu liczbowego jako parametrów.

Teraz nie mamy numerów, prawda? Mamy daty i nie ma w nich czegoś takiego jak automatyczna konwersja typu. Musimy więc uzyskać liczbę z naszej wartości daty. Więc wpisujemy tutaj, liczba. z naszej kolumny Data , a następnie odejmujemy naszą zmienną StartDate.

Teraz powiedzmy, że pierwsza data w naszej tabeli Date zwraca jedynkę, wtedy nasza StartDate również zwróci jedynkę. I tak, jeden minus jeden równa się zero. Chcę, aby nasza numeracja indeksów zaczynała się od jedynki zamiast od zera. Więc będziemy musieli dodać jeden z powrotem plus jeden. Teraz jeden minus jeden równa się zero plus jeden zwraca jeden dla pierwszego rekordu.

W przypadku drugiego rekordu otrzymujemy wartość dwa (2) dla daty w kolumnie Data minus jeden (1) z naszej wartości StartDate, która jest równa jeden, plus ta, którą dodajemy z powrotem, zwraca dwa i tak dalej . Nasz powtarzający się wzór był oparty na serii czterech naprzemiennych wartości. Więc jako dzielnik przekażę wartość cztery (4).

Zobaczmy, co to daje. W rzędzie czwartym otrzymujemy zero, podobnie jak w rzędzie ósmym. Tak więc każde czwarte wystąpienie zwraca wartość zerową dla tego rekordu.

Tworzenie konstrukcji IF-THEN-ELSE

Wróćmy do naszego okna dialogowego klienta i rozszerzmy logikę, którą napisaliśmy do tej pory. Z wyjątkiem dzielnika, musimy powtórzyć tę samą logikę, aby móc zidentyfikować ósme wystąpienie.

Jednak w tym ósmym rzędzie chcemy innej wartości. Tak więc, aby dokonać rozróżnienia między czwartym a ósmym wystąpieniem, możemy użyć konstrukcji JEŚLI-TO-JEŻELI. W ten sposób możemy zwrócić wartość dla pierwszego testu logicznego, który zwraca prawdę.

Więc jeśli , a ja wkleję tutaj nasz kod (z wyjątkiem dzielnika), zamień ten dzielnik na ósemkę, aby w ten sposób ósmy wiersz zwrócił zero. Więc jeśli to równa się zeru, oznacza to, że nie powiedzie się na drodze do prawdy lub fałszu. Następnie chcemy jeden (1). W przeciwnym razie , jeśli nasza logika identyfikuje tutaj czwarty wiersz równy zeru, to chcemy zero (0). W przeciwnym razie nie chcemy odzyskać wartości. Dodajmy więc null .

Teraz sprawdźmy ponownie naszą logikę. Tak więc przy każdym czwartym wystąpieniu otrzymujemy to zero. A potem, ósmego, dostajemy jedynkę. Następnie otrzymujemy zero i jedynkę i tak dalej.

Więc to wygląda dobrze. Wszystko, co musimy teraz zrobić, to uzupełnić te wartości.

Tak więc wewnątrz paska formuły dodamy na początku Table.FillUp , a następnie kilka nawiasów otwierających. Na końcu naszego kodu dodajemy numer typu – musimy też dodać kolumnę jako listę. Więc idziemy przecinek , a następnie używamy nawiasów klamrowych jako inicjatora listy i jako wartość tekstową przekazujemy w nazwie naszej kolumny Pattern . Nie zapomnij o nawiasach zamykających i naciśnij OK.

Przewodnik dla początkujących po kodzie M w LuckyTemplates
Funkcja M dla tabeli dat – jak dodać parametr w edytorze zapytań
Sortowanie kolumn tabeli dat w LuckyTemplates

Wniosek

To bardzo wyjątkowy scenariusz i szczerze mówiąc, nie przychodzi mi do głowy żadna aplikacja dla tego konkretnego wzorca, ale mam nadzieję, że dostrzegasz potencjał tworzenia sekwencji przy użyciu funkcji Power Query M, number.mod.

Aby uzyskać więcej informacji, obejrzyj cały film poniżej tego samouczka.

Wszystkiego najlepszego!

Melisa