Dynamicznie przestawiaj wiele kolumn w usłudze LuckyTemplates

W tym samouczku wrócimy do tematu, który omówiłem wcześniej, dotyczącego usuwania wielu kolumn w dodatku Power Query przy użyciu List.Zip. Członek LuckyTemplates zapytał na , jak uczynić tę metodę dynamiczną. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Oznaczyłem to jako zaawansowany, ponieważ będziemy pisać wiele funkcji M, a niektóre z nich mogą być nawet dla ciebie nowe.

Powiedziawszy to, przejdźmy do dodatku Power Query.

Spis treści

• Dane w formacie tabelarycznym
• Zmienianie kodu w celu dynamicznego odstawiania wielu kolumn
• Dynamicznie przestawiaj wiele kolumn: dodawanie niestandardowej logiki kolumn
• Wniosek

Dane w formacie tabelarycznym

Jak widać, dane te zostały już przekształcone w odpowiedni format tabletu nadający się do analizy. Ten proces, choć bardzo ważny, nie zostanie tutaj omówiony, ale pokrótce omówimy każdy krok.

W kroku źródłowym widzimy ciągłe powtarzanie się par wartości, którymi są godziny i kontakty dla każdego z wymienionych powyżej atrybutów.

W zastosowanych krokach możesz zobaczyć pozostałe kroki. Usunęliśmy niektóre górne wiersze, usunęliśmy puste kolumny, wyczyściliśmy nasze wartości tekstowe i wypełniliśmy je. Następnie transponowaliśmy nasze dane, usunęliśmy pusty rekord, połączyliśmy kolumny pierwszą i drugą, ponownie wyczyściliśmy nasze wartości tekstowe i ponownie transponowaliśmy nasze dane.

Stworzyliśmy niestandardowy krok, który później przeanalizujemy bardziej szczegółowo, wyodrębniliśmy wartości z naszej listy, a następnie ostatecznie zmieniliśmy typ.

Spójrzmy na cały kod. Interesuje nas tutaj funkcja List.Zip . Krótkie podsumowanie, pobiera pojedynczą listę jako parametr , a następnie zwraca listę. W rezultacie widzimy, że ta pojedyncza lista w tym przypadku zawiera trzy oddzielne listy.

Jeśli przyjrzymy się pierwszej liście, zawiera ona zakodowane na stałe nazwy atrybutów, tylko wartości tekstowe, które zostały wpisane. Druga lista zawiera zakodowane na stałe odniesienia do kolumn dla wartości godzinowych. Ostateczna lista zawiera zakodowane na stałe odniesienia do kolumn dla wartości kontaktowych.

List.Zip następnie bierze element z każdej z tych trzech list i łączy je na podstawie ich pozycji na nowej liście. Na przykład każdy pierwszy element z tych list jest łączony w nową listę. To samo dzieje się ze wszystkimi wartościami wymienionymi na drugiej liście i tak dalej.

Wartości zakodowane na stałe mogą mieć znaczenie, jeśli jest mało prawdopodobne, aby atrybuty zmieniały się w czasie. Więc jeśli następnym razem nie wszystkie przedmioty są obecne lub istnieją nowe przedmioty, możesz wpaść w kłopoty.

Zmienianie kodu w celu dynamicznego odstawiania wielu kolumn

Jak więc sprawić, by było to dynamiczne?

Pierwszą rzeczą, którą robię, jest przejrzenie kodu, aby sprawdzić, czy istnieje miejsce, w którym możemy pobrać te wartości atrybutów bez konieczności projektowania oddzielnego kroku, aby to osiągnąć. Więc cofniemy się przez kod, a ten krok RemoveNull jest doskonały.

Możesz zobaczyć, że zawiera listę wszystkich wartości atrybutów tutaj w pierwszej kolumnie. Możemy użyć interfejsu użytkownika, aby je wyodrębnić.

Kliknij ten nagłówek prawym przyciskiem myszy i poniżej wybierz Dodaj jako nowe zapytanie .

Otrzymujemy tę listę i to jest dobra wiadomość, ponieważ chcemy zastąpić tę zakodowaną na stałe listę, którą widzieliśmy wcześniej, dynamiczną. Wszystko, co musimy zrobić, to uporządkować wartości.

Zacznijmy od usunięcia tych wartości zerowych. Tak więc wewnątrz paska formuły dodamy List.RemoveNulls . Jak widać teraz, wszystkie elementy są powtarzane, a to z powodu par wartości. Jest wymieniony raz dla godzin.

I znowu, dla kontaktów, aby usunąć te duplikaty, możemy dodać List.Distinct . Teraz utworzyliśmy tę logikę i wszystko, co musimy zrobić, to skopiować ją i wkleić z powrotem do naszego przykładowego zapytania.

W przykładowym zapytaniu otwórz Edytor zaawansowany i utwórz nową nazwę zmiennej. Nazwijmy to, AttributeList . Następnie wklejamy kod i stawiamy przecinek.

Wróćmy teraz do naszego kroku niestandardowego. Wewnątrz List.Zip możemy zastąpić tę pierwszą listę nazwą naszej zmiennej (AttributeList).

Przyjrzyjmy się teraz bliżej drugiej liście. Wiemy, że odwołuje się do nazw kolumn, a każda z tych nazw kolumn zaczyna się od tego samego atrybutu, który już mamy na tej liście, po którym następuje spacja i tekst „godziny”.

Musimy tylko dodać sufiks do każdego elementu. Cofnijmy się więc o krok i dodajmy niestandardową kolumnę, aby stworzyć pewną logikę.

Dynamicznie przestawiaj wiele kolumn: dodawanie niestandardowej logiki kolumn

To tylko tymczasowa kolumna, więc nie musimy nadawać jej właściwej nazwy. Usuniemy go, gdy skończymy.

Aby zmienić wartości na liście, możemy użyć funkcji List.Transform . Musimy przekształcić wartości w naszym AttributeList , więc zamierzam to wkleić.

Tutaj w nagłówkach widzimy, że wartości tekstowe zaczynają się od dużej litery, a reszta jest pisana małymi literami. Nasz atrybut zawiera tylko słowo pisane wielką literą, więc musimy również przekształcić tę wartość tekstową. I możemy użyć podkreślenia, aby uzyskać dostęp do każdego elementu listy i dodamy ten sufiks.

W ten sposób otrzymujemy wartość listy w naszej kolumnie. Nie ma znaczenia, który wybierzesz, ale po prostu kliknij z boku w białej przestrzeni w dowolnym z nich tutaj.

Poniżej strony widzimy podgląd utworzonej przez nas listy. Widzisz więc, że nagłówki kolumn mają teraz odpowiednią wielkość liter, więc pasują do nagłówków kolumn, które widzimy tutaj z tą listą.

Mamy teraz środki do identyfikowania pól z naszego rekordu, do których chcemy uzyskać dostęp. Otwórzmy więc ponownie nasze niestandardowe okno dialogowe kolumny i dodajmy Record.SelectFields . Chce rekordu jako rekordu, więc możemy użyć tego podkreślenia, aby uzyskać dostęp do bieżącego rekordu, na którym się znajdujemy. A potem daliśmy mu listę z wszystkimi nazwami pól, więc dodaj nawias zamykający i naciśnij OK .

Więc nie mamy już listy, ale mamy teraz zapis. Kliknijmy ponownie w bok w białym polu, aby wyświetlić zawartość tego rekordu. Tutaj widzimy, że zawiera listę nazw pól rekordów i wartości pól rekordów.

Ale nas interesują tylko wartości pól rekordu, więc musimy je wyodrębnić. Wróćmy do naszego niestandardowego okna dialogowego kolumny i dodajmy Record.FieldsValues ​​.

Teraz ponownie otrzymujemy listę, a nie rekord. Ponownie klikamy na bok w białym polu i widzisz, że mamy teraz listę zawierającą tylko te wartości. Więc to wygląda dobrze.

Skopiujmy całą logikę, którą stworzyliśmy, z wyjątkiem ostatnich nawiasów zamykających. A teraz możemy usunąć naszą tymczasową kolumnę, ponieważ już jej nie potrzebujemy.

Na pasku formuły wybieramy naszą drugą listę i zastępujemy ją naszą logiką.

Aby utworzyć listę z wartościami pól rekordów dla kontaktów, wystarczy zmienić sufiks. Możemy więc wybrać również tę ostatnią listę i wkleić naszą logikę.

Następnie zamiast godzin zmieńmy to na kontakty .

Żadnych błędów i wygląda dobrze. Jest to teraz dynamiczne rozwiązanie pozwalające cofnąć przestawienie wielu kolumn.

LuckyTemplates Unpivot Columns – Power Query samouczek
Jak cofnąć przestawienie kolumn w LuckyTemplates
Podstawy unpivot i Pivot w LuckyTemplates – recenzja edytora zapytań

Wniosek

W ten sposób możesz dynamicznie cofnąć przestawienie wielu kolumn w usłudze przy użyciu List.Zip.

Jeśli pojawi się nowy atrybut, zostanie on automatycznie pobrany przez AttributeList. A kiedy element już nie istnieje, nie będzie obecny na tej liście.

Mam nadzieję, że ci się podobało. Obejrzyj cały film poniżej, aby uzyskać więcej informacji na temat tego samouczka, i sprawdź poniższe łącza, aby uzyskać więcej informacji na temat przenoszenia wielu kolumn w usłudze LuckyTemplates.

Wszystkiego najlepszego

Melisa