Kod M w niestandardowych kolumnach dodatku Power Query | LuckyTemplates

W dzisiejszym blogu przedstawię rozwiązanie problemu LuckyTemplates , w tym prostą i uogólnioną platformę do włączania kodu M do niestandardowej kolumny dodatku Power Query. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Możliwość tworzenia kolumn w dodatku Power Query przy użyciu kodu M zwiększa możliwości rozwiązywania bardziej złożonych problemów.

Zanim przejdziemy do rozwiązania, przyjrzyjmy się naturze problemu. Dałem duży zestaw danych z Kaggle z 93 latami danych Oscara. Chcieliśmy wybrać najlepszą aktorkę, zwyciężczynię, a zwłaszcza te kobiety, które zdobyły więcej niż jedną nagrodę dla najlepszej aktorki, a następnie zwizualizować za pomocą osi czasu Queryon rok, w którym zdobyły drugą nagrodę.

Łącząc ten problem, miałem kilka celów. Na czym chcemy, aby problem się koncentrował i jakie chcemy mieć główne cele?

A w tym było ich trzech.

Pierwszym z nich było nauczenie się lub rozwinięcie pewnej praktyki z konstruktem Grupuj według i Wszystkie wiersze . Jest to coś, na czym naprawdę się skupiałem, ponieważ w prosty sposób rozwiązuje szeroki zakres problemów i często eliminuje potrzebę stosowania bardzo złożonego języka DAX.

Drugi cel jest szczególnie przeznaczony dla osób, które nie były przyzwyczajone do pracy w M. Chciałem stworzyć problem, który ułatwiłby pisanie podstawowego kodu M. Chciałem wymusić ten sposób myślenia o tym, jak to jest rozwijać podstawowy kod M i proces jego wykonywania.

Według rozdziału w książce Gila Raviva na temat Power Query, około 40% problemów w Power Query można rozwiązać za pomocą interfejsu użytkownika. Ale to, z dodatkiem podstawowego kodu M i możliwością tworzenia niestandardowych kolumn, naprawdę nie jest takie trudne. Możesz zwiększyć ten odsetek o ponad 80%. Możesz podwoić swoje możliwości rozwiązywania problemów dzięki kilku podstawowym, prostym umiejętnościom.

Trzecią rzeczą, którą chcę zrobić, jest przedstawienie niestandardowej wizualizacji Queryon Timeline osobom, które jej nie znają. To jest coś, co naprawdę pokochałem. Używałem go w wielu różnych przypadkach i myślę, że zapewnia on naprawdę wyjątkowy sposób wizualizacji danych.

Spis treści

• Rozwiązanie problemu tygodnia nr 9
• Używanie Grupuj według w Power Query
• Zawiera kod M Table.AddIndexColumn
• Wizualizacja przy użyciu niestandardowej wizualizacji osi czasu zapytania
• Wniosek

Rozwiązanie problemu tygodnia nr 9

Zastanawiając się nad tym problemem, chciałem przeprowadzić eksperyment myślowy. Co by było, gdyby zamiast pliku CSV wręczyć Ci 93 kartki papieru (po jednej z coroczną zdobywczynią nagrody dla najlepszej aktorki), jak wyglądałoby rozwiązanie?

Twoje rozwiązanie prawdopodobnie wyglądałoby podobnie do tego, co mam poniżej.

Myślę, że każdy miałby w miarę podobne rozwiązanie. Gdy masz już to koncepcyjne rozwiązanie, wystarczy wymyślić, jak zmusić dodatek Power Query do wykonania każdego z tych kroków.

Potraktuję to jako ogólny zarys, a następnie omówię, w jaki sposób operacjonalizują to koncepcyjne rozwiązanie w coś w Power Query. Podejście, które zamierzamy zastosować, jest określane przez Melissę, naszego stałego guru Power Query, jako nauka przez Google.

Teraz, jeśli wrócimy do naszego modelu koncepcyjnego, pierwsze dwa kroki obejmują posortowanie kuponów na grupy według aktorek i wyrzucenie każdej grupy, która ma tylko jeden kupon. Więc chcemy policzyć. Jeśli nie wiemy, jak to zrobić, możemy wejść do Google i spróbować wyszukać hasło. W tym przykładzie wypróbowałem „grupowanie w zapytaniu o moc”. Dzięki temu możemy znaleźć wszystko, co musimy wiedzieć o tym, jak wykonać to grupowanie.

Używanie Grupuj według w Power Query

Następnie możemy wskoczyć do Power Query i rozpocząć operacjonalizację naszego rozwiązania. To, co zrobiłem, to to, że w tej tabeli Oscarów właśnie przejrzałem i wyciągnąłem plik CSV i przefiltrowałem do zwycięzców dla najlepszej aktorki w każdym roku. Mamy więc tylko 93 rzędy nazwy i roku filmu.

Jeśli przejdziemy do Power Query i przejdziemy do tej tabeli, pierwszą rzeczą, którą chcemy zrobić, jest Group By .

Zawsze lubiłem przejść na wersję zaawansowaną tylko dlatego, że zapewnia nieco lepszy interfejs, nawet jeśli to, co robimy, jest całkiem proste.

Chcemy pogrupować według Name , a następnie zróbmy kolumnę AllData , a to będzie po prostu nasze All Rows. Zamiast zwracać tylko agregat, zwróci agregację, ale wszystkie wiersze, które chcemy, pozostaną nienaruszone.

Będziemy mieć kolejną kolumnę o nazwie Count i będziemy jej potrzebować, gdy przejdziemy do Count Rows . Chcemy znaleźć liczbę kartek w każdym stosie. Następnie klikamy OK.

Możemy wtedy kliknąć na to białe miejsce w tabeli i otrzymamy tylko jeden poślizg.

Ale wtedy dla tych, które mają wiele zliczeń, zobaczysz, że otrzymujemy wszystkie rekordy.

Następnie chcemy odfiltrować te (1). Więc w zasadzie wyrzucamy do kosza wszystkie te stosy, które mają tylko jedną nazwę. Wybierz 1 i kliknij OK.

A teraz mamy 14 wielokrotnych zdobywców nagród. A teraz mamy 14 wielokrotnych zdobywców nagród. Jeśli weźmiemy to i rozwiniemy, możemy usunąć Imię , ponieważ mamy już tutaj Imię i po prostu zachować year_film .

Zmniejszyliśmy liczbę rzędów do 30, ponieważ niektóre z nich będą miały wiele wygranych. I właściwie możemy nawet pozbyć się kolumny Count , ponieważ już wyrzuciliśmy jedynki za pomocą tylko jednej.

Teraz przejdziemy do następnej fazy naszego modelu koncepcyjnego, która polega na posortowaniu kuponów w każdej grupie według rosnącej daty, a następnie wybraniu drugiego dnia występującego w każdej grupie.

Sortowanie jest łatwe. Możemy wrócić do dodatku Power Query i wykonać Sortowanie rosnące według aktorki, a następnie według daty.

I pokaże ci jeden i dwa.

Więc jesteśmy dobrze posortowani, a teraz chcemy mieć indeks lub ranking, który ma dla każdego z nich, a następnie móc wziąć drugi z każdego stosu.

Zawiera kod M Table.AddIndexColumn

I tak teraz wracamy do naszego podejścia „Nauczone przez Google”, w którym możemy po prostu wyszukać rangę lub indeks, ale możemy też przejść do Przewodnika referencyjnego firmy Microsoft . Stworzyłem zewnętrzne narzędzie, którego cały czas używam, które po prostu przenosi Cię z usługi LuckyTemplates do Przewodnika referencyjnego firmy Microsoft.

W przewodniku Microsoft Reference Guide wszystko jest pogrupowane według funkcji i zawiera pole filtru, w którym można wyszukać wszystko, czego potrzebujesz. W takim przypadku wpisujemy Rank lub Indeks w polu filtru. Nie daje nam to nic dla Rank, ale dla Index mamy tę Table.AddIndexColumn , która jest odpowiednia i ma zastosowanie do tego, czego szukamy. Zawiera również kilka przykładów korzystania z tej funkcji.

Przejdźmy więc do Power Query i przegrupujmy nasz stół według Actress.

W ten sposób otrzymujemy grupę 14 zwycięzców według tabeli. A jeśli wejdziemy do którejkolwiek z tych tabel, powiedzmy, Frances McDormand, zobaczymy, że tabela jest posortowana według daty.

A oto duży krok, który polega na dodaniu niestandardowej kolumny dodatku Power Query i wprowadzeniu naszego kodu M. Będziemy mieli Table.AddIndexColumn , a następnie dodamy pole AllData . Nazwiemy naszą nową kolumnę (jako tekst) tutaj jako Index i zaczniemy nasz Index od jednego (1) i zwiększymy go o jeden (1).

I tutaj otrzymujemy ten doskonały indeks.

Teraz możemy usunąć tutaj naszą kolumnę AllData, ponieważ wszystko jest zbierane w pliku Custom . Następnie możemy po prostu rozwinąć kolumnę Niestandardowa, usunąć nazwę, ponieważ tutaj również mamy już nazwę. W ten sposób mamy numerowany indeks dla każdego z nich.

Na koniec możemy zrobić to, co zrobiliśmy w Count, czyli odfiltrować wszystkie te, które są w drugiej wygranej.

I oto idziemy. Oto nasze rozwiązanie.

Wizualizacja przy użyciu niestandardowej wizualizacji osi czasu zapytania

Jest kilka innych rzeczy, które chcemy tutaj zrobić, a wszystkie są oparte na implementacji Queryon Visual . Zrobimy scalenie i po prostu dodamy adres URL obrazu.

Więc połączymy zapytania, wyciągniemy tabelę Actress i połączymy na Name . Otrzymujemy od 14 do 14 pasujących rzędów, co jest dobre.

Następnie w kolumnie Aktorki po prostu rozwiniemy i rozwiniemy do adresu URL.

A teraz mamy tę kolumnę z adresami URL.

Jest jeszcze jedna rzecz, która wiąże się ze znajomością osi czasu zapytań. Queryon działa najlepiej, gdy ma do czynienia z pełnymi datami, a nie latami . A więc jest to coś, co przychodzi po prostu z eksperymentami, ale możemy wziąć kolumnę Year i utworzyć kolumnę From Examples . To, co chcemy tutaj zrobić, to po prostu zamienić lata na daty.

To poprawi formatowanie. To jest coś, czego nie spodziewałbym się, żebyś wiedział od razu, ale przychodzi to wraz z praktyką z Queryon. Klikamy OK, a to wchodzi jako wartość daty. Możemy usunąć tabelę indeksów, ponieważ już jej nie potrzebujemy, a następnie możemy zmienić nazwę kolumny year_film na Year .

Przejdźmy teraz do aspektu wizualizacji i chcę zwrócić uwagę na wpis jednego z członków LuckyTemplates, Jose, który wykonał świetną robotę w zakresie wizualizacji.

Wracając do Power Query na potrzeby wizualizacji, warto wspomnieć, że firma Microsoft niedawno wprowadziła pewne zmiany w sposobie certyfikowania niestandardowych wizualizacji . W ramach tej certyfikacji zaostrzyli zasady korzystania z zewnętrznych obrazów adresów URL.

Jeśli faktycznie korzystasz z wersji Queryon Timeline, która znajduje się w źródle aplikacji (wersja 1.05), nie będziesz w stanie pobrać tych obrazów adresów URL. I tak, masz teraz możliwość korzystania z tego 1.04, zachowując obrazy URL, gdzie mają na swojej stronie sposób na osadzenie binarnych 64 obrazów bezpośrednio w pliku PBIX.

Wspomniałem już o zmianie tego roku na pole daty, więc musimy połączyć to z naszym modelem danych.

Po prostu zmieńmy to z jednego na jeden na jeden na wiele i Pojedynczy kierunek na filtrze Krzyżowym, a następnie ustawmy tę relację jako aktywną .

Następnie w przypadku pola adresu URL obrazu musimy upewnić się, że w kategorii Dane jest ono wyświetlane nie jako Bez kategorii, ale jako Adres URL obrazu .

Teraz, gdy wszystko jest ładnie przygotowane, po prostu przeciągamy i upuszczamy pola Name , the Date i URL . Reszta staje się naprawdę prostym czyszczeniem w opcjach formatu.

Tabela dodatku Power Query: Jak dynamicznie scalać kolumny
Jak scalać zapytania w usłudze LuckyTemplates
Wyodrębnianie wartości z rekordów i list w dodatku Power Query

Wniosek

Mam nadzieję, że zachęciłem Cię do dodania kodu M do swojego repertuaru. Pokazałem, jak skonfigurować model koncepcyjny i wdrożyć podejście Ucz się przez Google. Przeprowadziłem Cię przez kolejne etapy włączania kodu M do niestandardowej kolumny i wizualizacji wyników za pomocą osi czasu zapytania.

Mam nadzieję, że uznałeś to za przydatne. Obejrzyj pełny film poniżej, aby uzyskać więcej informacji na temat samouczka, i sprawdź poniższe łącza, aby uzyskać więcej powiązanych treści.

Wszystkiego najlepszego!