Niestandardowe funkcje zapytań usługi LuckyTemplates

W dzisiejszym samouczku utworzymy niestandardową funkcję zapytania usługi LuckyTemplates, która będzie obsługiwać typowy, trudny w obsłudze format danych skumulowanych. Ta technika jest łatwa do ponownego wykorzystania w wielu raportach i wielu tabelach. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

Jest to kontynuacja samouczka, który niedawno przeprowadziłem, w którym zajmowaliśmy się popularnym, trudnym w obsłudze formatem danych skumulowanych i używaliśmy Modulo, Pivot oraz niektórych funkcji czyszczących w dodatku Power Query, aby przekształcić te dane w uporządkowany i funkcjonalny format.

Jest bardzo prawdopodobne, że będziesz musiał powtórzyć ten proces. Jeśli ten typ formatu powstał w wyniku kopiowania i wklejania, prawdopodobnie sytuacja się powtórzy. W takim przypadku prawdopodobnie będziesz chciał zbudować niestandardową funkcję, której będzie można używać wielokrotnie w wielu raportach i wielu tabelach, niezależnie od tego, jak się je nazywa i co jeszcze znajduje się w tych tabelach.

Spis treści

• Jak tworzyć i debugować niestandardowe funkcje zapytań usługi LuckyTemplates
• Wniosek

Jak tworzyć i debugować niestandardowe funkcje zapytań usługi LuckyTemplates

Przejdźmy teraz do dodatku Power Query. Jeśli przejdziemy do naszej tabeli danych, w Edytorze zaawansowanym mamy kod M określający, w jaki sposób przeszliśmy od danych stosu do pożądanego formatu.

Jeśli chcemy użyć tego ponownie, po prostu klikamy prawym przyciskiem myszy Dane i mówimy Utwórz funkcję .

Mówi, że nie będzie odwoływać się do żadnego parametru, ale można utworzyć funkcję bez parametrów. Odpowiemy Tak, ponieważ zamierzamy dodawać parametry na bieżąco w Edytorze zaawansowanym. Nazwiemy tę funkcję Unstack – 3 Elements , ponieważ mamy trzy wiersze w formacie raw. Gdybyśmy mieli format dwurzędowy, prawdopodobnie zbudowalibyśmy osobny, składający się z dwóch lub czterech elementów.

Więc teraz mamy to Invoke, nie mamy żadnych parametrów, przejdźmy do Edytora zaawansowanego i zacznijmy pracować nad tym.

Po pierwsze, nie potrzebujemy tego Źródła. Zamierzamy użyć parametrów funkcji do zdefiniowania naszego źródła.

Chcemy zdefiniować parametr, który będzie wchodził. Robimy to za pomocą otwartego nawiasu, a następnie nazwy parametru. Nazwijmy to Stack , która będzie tą jedną kolumną danych, która jest niesformatowanymi danymi stosu pochodzącymi z wklejania adresów e-mail.

Chcemy, aby to pojawiło się jako lista i chcemy, aby wynikiem tego była tabela, ponieważ gdy przestawiamy ją z tej pojedynczej kolumny do wielu kolumn, przechodzi ona z listy do tabeli. A teraz musimy przekonwertować tę przychodzącą listę na tabelę, abyśmy mogli dodać nasz indeks, zainicjować Modulo unpivot i zrobić wszystko, co musimy zrobić, aby ponownie ją sformatować.

Nazwiemy go ConvertStack i użyjemy funkcji o nazwie Table.FromList . Podobnie jak wiele funkcji M, robi dokładnie to, co mówi. Wystarczy lista. W tym przypadku lista będzie naszą listą stosów. A potem musimy zastąpić Źródło tutaj naszym ConvertStack. Wygląda całkiem nieźle! Nie mamy żadnych błędów składniowych, więc kliknijmy Gotowe.

Teraz daje nam to, czego chcieliśmy, czyli możliwość wyboru kolumny.

Następnie wybierzemy TestData, czyli źle sformatowane dane. Wybieramy kolumnę Wartość, a następnie klikamy OK. Następnie klikamy Invoke.

Otrzymujemy jednak ten błąd. Naprawdę trudno jest określić, co to za błąd. Jednym z problemów z funkcjami niestandardowymi jest to, że nie rozdzielają one zastosowanych kroków, po prostu otrzymujesz jeden krok dla całej funkcji niestandardowej. Ale jest sposób na obejście tego podczas debugowania, który ci pokażę. Znacznie łatwiej będzie zorientować się, co się tutaj dzieje.

Zamiast uruchamiać to na razie jako funkcję, uruchomimy to jako zapytanie. Po prostu ręcznie zainicjujemy to wywołanie stosu. Musimy zdefiniować, czym jest Stack, ponieważ nie rysujemy go poprzez interakcję polegającą na wybraniu go w Invoke. Mamy więc Stack równy TestData i kolumnę Value TestData.

To, co robi, to rozkłada to teraz na zastosowane kroki. Pomoże nam to ustalić, dlaczego to nie działa. Zwróć uwagę na jedną interesującą rzecz, gdy pobieramy Stack. Początkowo pobiera on TestData[Value], ale kiedy konwertujemy go na tabelę, zamiast Value jest nagłówek kolumny, nagłówek kolumny to teraz Column1.

Zapamiętaj komunikat o błędzie, że nie mógł znaleźć kolumny Wartość. Powodem, dla którego nie mógł znaleźć kolumny Wartość, jest to, że funkcja Table.FromList zmienia jej nazwę na Kolumna1.

Jeśli przejdziemy w dół Zastosowanych kroków, zobaczysz, że w kroku Dodano niestandardowy pojawia się błąd. W tym miejscu nie znaleziono wartości rekordu.

I tak, jeśli przejdziemy do Edytora zaawansowanego, znajdziemy to pole niestandardowe i zobaczymy, że znajduje się ono w tekście. Usuń miejsce, w którym usuwamy niepotrzebne znaki, ale nadal odnosi się do pole Wartość. Zmienimy to na Kolumnę 1. Podobnie w polu Usunięte kolumny odnosi się do wartości, więc zmienimy to również na Kolumna1.

A kiedy dojdziemy do końca Zastosowanych Kroków, daje to doskonały efekt końcowy.

Teraz, gdy wiemy, że wszystko działa prawidłowo, pozostaje nam jeszcze tylko jedna rzecz do zrobienia. Pamiętaj, że w celu debugowania przekształciliśmy funkcję niestandardową w zapytanie. Teraz musimy wziąć to zapytanie i przekształcić je z powrotem w funkcję.

Tworzenie niestandardowej funkcji czyszczenia tekstu w kodzie
M dodatku Power Query w niestandardowych kolumnach dodatku Power Query | Zapytanie i operatory funkcji usługi LuckyTemplates
w edytorze zapytań

Wniosek

W tym samouczku zbudowaliśmy tę niestandardową funkcję, debugowaliśmy ją, przetestowaliśmy i działa świetnie. I tak teraz, gdy mamy trzyelementowy stos, możemy po prostu wybrać tę tabelę, wybrać tę kolumnę w tabeli i uruchomić tę niestandardową funkcję. Następnie zastosuj Modulo, unpivot i posprzątaj.

To powinno dać ci kilka fajnych narzędzi do tworzenia niestandardowych funkcji. Pamiętaj też o tej sztuczce dotyczącej debugowania, zamieniając ją z powrotem w zapytanie z funkcji, a następnie przełączając je z powrotem po debugowaniu. Jest to naprawdę cenne narzędzie, które znacznie ułatwia debugowanie niestandardowych funkcji.

Wszystkiego najlepszego!