LuckyTemplates Power Query: usuwanie zduplikowanych wierszy

Na tym blogu pokażę, jak usunąć zduplikowane wiersze w usłudze LuckyTemplates Power Query na podstawie warunków. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Ten przykład pochodzi z pytania zadanego na . Członek ma dane transakcyjne i chce usunąć wiersze, które wzajemnie się znoszą.

Istnieją dwa różne typy transakcji, jeden dla przychodzących i jeden dla wychodzących. Dodała także liczbę ruchów. Na tej podstawie sformułowała trzy scenariusze.

Scenariusz pierwszy opiera się na dwóch rzędach. Jeśli wzajemnie się znoszą, oba rzędy należy usunąć. Jeśli tak nie jest, należy zachować oba wiersze.

Scenariusze drugi i trzeci są oparte na kombinacji rzędów, częściowo znoszących się nawzajem. Wiersze te należy usunąć na zasadzie pierwsze weszło, pierwsze wyszło, tak aby zachowywane były tylko najnowsze transakcje przychodzące.

Zasadniczo powtarzamy w kółko tę samą logikę. Powinniśmy być w stanie zaprojektować jedno podejście pasujące do wszystkich tych scenariuszy.

Niektóre z rzeczy, które zamierzamy zrobić, to dodać kolumnę z bezwzględnymi ilościami i pogrupować według „dodaj kolumnę bilansu ilościowego” oraz zagnieżdżoną tabelę przy użyciu opcji „wszystkie wiersze”. Przekształcimy również te zagnieżdżone tabele, posortujemy typ przesunięcia i datę księgowania, utworzymy sumę bieżącą i zachowamy tylko wiersze na podstawie warunku.

Oceniłem to na poziomie średniozaawansowanym, ponieważ większość przekształceń, które możemy tutaj wykonać, odbywa się za pomocą interfejsu użytkownika. Powiedziawszy to, przejdźmy do LuckyTemplates Power Query.

Spis treści

• Dodawanie kolumny z ilościami bezwzględnymi w dodatku Power Query
• Grupuj według indeksu ID i ilości bezwzględnej
• Przekształcanie zagnieżdżonych tabel w usłudze LuckyTemplates Power Query
  • Rodzaj sortowania ruchu i data księgowania
  • Tworzenie sumy bieżącej
  • Filtrowanie wierszy na podstawie warunku
• Wniosek

Dodawanie kolumny z ilościami bezwzględnymi w dodatku Power Query

Zaczniemy od dodania kolumny z ilościami bezwzględnymi. Wybierz kolumnę Ilość , następnie na wstążce lub karcie Dodaj kolumnę przejdź do opcji Naukowe , a następnie kliknij Wartość bezwzględna .

Zmienię nazwę tej kolumny na pasku formuły.

Grupuj według indeksu ID i ilości bezwzględnej

Możemy teraz grupować według indeksu ID i bezwzględnej ilości. Wybierz Indeks ID , a następnie przytrzymaj klawisz Shift lub Control, aby wybrać wiele opcji, a następnie kliknij Abs qty (ilość bezwzględna).

Funkcję Grupuj według znajdziesz na karcie Przekształć.

Ale możesz go również znaleźć na karcie Strona główna.

Kliknij na nią, a teraz dodamy nową kolumnę. To nie jest „liczba”, ale będzie to „suma” ilości. Zmieńmy więc jego nazwę na Qty (ilość) Balance . To będzie suma kolumny ilości. A potem dodamy kolejną agregację, ale będzie to specjalna agregacja, więc wybierzemy All Rows . Nadajmy również nazwę tej kolumnie ( AllRows ). Następnie naciśnij OK.

Jeśli klikniemy białe miejsce w tabeli, zobaczymy podgląd zagnieżdżonej tabeli poniżej. Widzimy, że ilości dla tej zagnieżdżonej tabeli znoszą się wzajemnie, a bilans ilościowy wynosi zero.

Jeśli spojrzymy na następną zagnieżdżoną tabelę (kliknij na bok w pustym miejscu następnego wiersza), zobaczymy, że kolumny ilości nie znoszą się nawzajem, a saldo ilościowe jest większe od zera.

Dodajmy niestandardową kolumnę, abyśmy mogli później użyć jej do przekształceń zagnieżdżonych tabel. Wybierz opcję Dodaj niestandardową kolumnę .

Zmieńmy nazwę tej kolumny na Transformacje i dodamy zero (0) jako symbol zastępczy. Następnie kliknij OK.

Przekształcanie zagnieżdżonych tabel w usłudze LuckyTemplates Power Query

Jesteśmy teraz gotowi do utworzenia logiki do przekształcania tych zagnieżdżonych tabel. Ale nie chcemy pisać wielu z nich i nie musimy.

W większości możemy korzystać z interfejsu użytkownika. Jeśli zbudujemy logikę dla tych przekształceń w oddzielnym zapytaniu, najbardziej rozbudowanym scenariuszem, jaki mieliśmy, był scenariusz z trzema lub czterema wierszami.

Teraz, jeśli kliknę po prawej stronie w ostatnim wierszu, zobaczę, że ta tabela ma cztery wiersze. Możemy więc użyć tego do zbudowania naszych transformacji.

Kliknij prawym przyciskiem myszy z boku w białym polu i wybierz opcję Dodaj jako nowe zapytanie .

Widzimy, że zagnieżdżona tabela została teraz rozszerzona i dodana jako nowe zapytanie i możemy to wykorzystać do zbudowania potrzebnej nam logiki.

Rodzaj sortowania ruchu i data księgowania

Teraz pierwszą rzeczą, którą musimy zrobić, to posortować według typu ruchu . Wybierzmy malejąco . W ten sposób transakcje wychodzące będą zawsze na wierzchu.

Następnie posortujemy Datę księgowania rosnąco , upewniając się, że jeśli pominiemy wiersze, to zawsze będzie to zgodne z zasadą FIFO (first in first out) .

Tworzenie sumy bieżącej

Aby zidentyfikować wiersze, które zamierzamy usunąć, dodam bieżącą sumę, aby wyłączyć, i możemy użyć List.FirstN do tego zadania. Ta funkcja tworzy listę na podstawie innej listy, w której najważniejsze elementy są przechowywane na podstawie określonej liczby lub kryteriów.

Wróćmy do naszego zapytania. Potrzebujemy dwóch rzeczy dla List.FirstN. Najpierw potrzebujemy tej listy, a ta lista to nasza kolumna Ilość . Jeśli kliknę prawym przyciskiem myszy nagłówek kolumny Quantity, otrzymamy opcję Dodaj jako NewQuery .

I tutaj widzimy kod, którego potrzebujemy do wygenerowania tej listy. W zastosowanym kroku widać, że wskazuje on ostatni krok, a następnie identyfikuje kolumnę w tych nawiasach.

Druga część, której potrzebujemy dla List.FirstN, to liczba określająca, ile liczb należy zachować z tej listy. Możemy użyć do tego indeksu, więc zamierzam dodać kolumnę indeksu z jednego (1) dla tego wiersza.

Tak więc dla numeru 1 zachowa numer w pierwszym wierszu w kolumnie Ilość i tak dalej. I teraz możemy to podsumować.

Dodajmy niestandardową kolumnę i nazwijmy ją Running Total . Używamy tutaj List.FirstN i widzimy, że pierwszym parametrem była ta lista, kolumna Ilość. Musimy wskazać ostatni krok, a tym ostatnim krokiem jest Added Index .

Zidentyfikowaliśmy żądaną kolumnę i jest to nasza kolumna Ilość. Nasza liczba znajduje się w kolumnie Indeks.

Teraz zwraca listę. Jeśli klikniemy z boku w białym miejscu, możemy zobaczyć zawartość tej listy. W przypadku pierwszego rekordu zachował tylko górny wiersz z tej listy. W przypadku drugiego rekordu utrzymał dwa górne rzędy.

Teraz wszystko, co musimy zrobić, to zsumować te ilości i możemy użyć Lists.Sum , aby to zrobić. Dodam to na pasku formuły. Zamierzam też umieścić Type .

Filtrowanie wierszy na podstawie warunku

Dzięki tej sumie bieżącej możemy zidentyfikować wiersze, które chcemy zachować. Chcemy zachować tylko te wiersze, które są większe od zera, więc dodajmy warunek filtra.

Możemy teraz usunąć nasze kolumny pomocnicze. Wybierz opcję Indeks i Suma bieżąca, a następnie kliknij opcję Usuń kolumny.

W Edytorze zaawansowanym możemy teraz skopiować utworzony przez nas kod. Otworzę Edytor zaawansowany i zobaczymy, że jest to nasz podzielony krok. Możemy więc wybrać i skopiować wszystko poniżej.

Wróćmy teraz do naszego pierwotnego zapytania. Ponownie otwórz Edytor zaawansowany. I zamiast symbolu zastępczego przejdźmy do nowej linii. Zamierzam użyć wyrażenia „ let ”, ponieważ może ono przechwytywać wartości z obliczeń pośrednich w zmiennych. Więc „pozwól”, a następnie przejdź do nowej linii i wklej moje przekształcenia. Również zadeklaruję Type .

Podświetlony powyżej kod wskazuje na naszą rozszerzoną zagnieżdżoną tabelę, która jest umieszczona w kolumnie Wszystkie wiersze. Wskażę więc na to, zastępując ten wyróżniony kod AllRows .

Tak więc nasza kolumna transformacji ma teraz zagnieżdżone tabele. Budujemy nasze zapytanie na podstawie ostatniego wiersza, prawda? Kiedy zaczynaliśmy, zawierało to cztery wiersze, a zawierało tylko dwa. Mamy też te zbilansowane ilościowo zero, prawda? A teraz ma pusty stół.

Możemy je usunąć, filtrując według kolumny Bilans ilościowy. Nie chcemy, aby bilans ilościowy był równy zeru.

A potem wybiorę moją kolumnę transformacji i usunę inne kolumny. Mogę teraz rozszerzyć moje zagnieżdżone tabele transformacji. Nie używaj oryginalnej nazwy kolumny jako prefiksu i naciśnij OK.

Na karcie Przekształć wybierz pozycję Typ danych i gotowe. I w ten sposób czyścisz dane transakcyjne.

Podstawy funkcji Unpivot i Pivot w usłudze LuckyTemplates — Edytor zapytań Przejrzyj
moje wskazówki praktyczne dotyczące korzystania z zaawansowanego edytora usługi LuckyTemplates
Zaawansowane przekształcenia w usłudze LuckyTemplates

Wniosek

W tym samouczku pokazałem, jak wyczyścić dane transakcyjne, a konkretnie usunąć zduplikowane wiersze. To świetna technika, której możesz użyć podczas korzystania z usługi LuckyTemplates Power Query.

Mam nadzieję, że podobał ci się ten. Obejrzyj pełny samouczek wideo poniżej, aby uzyskać więcej informacji. Zapoznaj się z poniższymi linkami, aby uzyskać więcej powiązanych treści dotyczących edytora LuckyTemplates Power Query.

Dzięki!

Melisa