Usuwanie ostatnich kolumn w Power Query — sprytna metoda

Dynamiczne usuwanie ostatnich kolumn w Power Query pozwala na elastyczne przetwarzanie danych bez względu na zmieniające się nazwy kolumn. W tym artykule pokazuję, jak skutecznie przekształcić raporty z Excela poprzez usunięcie dwóch ostatnich kolumn niezależnie od ich nazw, co zapewnia stabilność rozwiązania nawet przy zmiennej strukturze danych.

Metoda ta wykorzystuje funkcje Table.ColumnNames i operacje na listach, co znacząco zwiększa użyteczność zapytań Power Query.

Problem z usuwaniem kolumn o zmiennych nazwach

Często podczas pracy z danymi w Power Query napotykamy na problem, gdy musimy usunąć określone kolumny, ale ich nazwy nie są stałe. W moim przypadku chciałem zawsze usunąć dwie ostatnie kolumny z raportu Excel, niezależnie od ich nazw. Standardowe podejście polegające na usuwaniu kolumn po nazwach nie zadziała w takiej sytuacji, ponieważ nazwy tych kolumn mogą się zmieniać w różnych raportach.

Pracując z wieloma raportami, zauważyłem, że mimo zmienności danych, struktura pozostaje podobna — zawsze chcemy usunąć ostatnie dwie kolumny. To wymaga stworzenia dynamicznego rozwiązania, które będzie działało niezależnie od tego, jak nazywają się te kolumny w konkretnym raporcie.

Standardowe podejścia do usuwania kolumn w Power Query

Power Query oferuje kilka standardowych metod usuwania kolumn, jednak nie wszystkie są odpowiednie dla naszego przypadku. Przyjrzyjmy się im bliżej:

• Usunięcie kolumn po nazwie — metoda prosta, ale działa tylko wtedy, gdy znamy dokładne nazwy kolumn, które chcemy usunąć. W naszym przypadku nie możemy jej zastosować, ponieważ nazwy kolumn będą się zmieniać.
• Wybieranie kolumn do zachowania — możemy zaznaczyć kolumny, które chcemy zachować i użyć opcji "Usuń inne kolumny". To podejście również wymaga znajomości konkretnych nazw.

W tej sytuacji potrzebujemy bardziej zaawansowanego rozwiązania, które będzie działać dynamicznie i zawsze usunie dwie ostatnie kolumny, niezależnie od ich nazw.

Dynamiczne rozwiązanie krok po kroku

Aby stworzyć dynamiczne rozwiązanie, które zawsze usuwa dwie ostatnie kolumny, potrzebujemy zastosować kilka przekształceń z wykorzystaniem funkcji Power Query. Oto jak to zrobić:

Krok 1: Pobranie danych z pliku Excel

Zaczynamy od pobrania danych z pliku Excel. W zakładce "Dane" wybieramy opcję "Pobierz dane z pliku arkusza" i wskazujemy lokalizację naszego pliku z raportem. W tym przykładzie pracujemy z jednym arkuszem na raz, więc wybieramy arkusz "Raport 1" i klikamy "Przekształć dane".

Krok 2: Usunięcie automatycznego wykrywania typów

Po załadowaniu danych zauważamy, że Power Query automatycznie dodaje krok "Zmieniono typ", który wykrywa typy danych dla poszczególnych kolumn. Ten krok odwołuje się do kolumn po ich nazwach, co w naszym przypadku stanowi problem — gdy zmienimy raport, nazwy kolumn mogą być inne, więc ten krok będzie generował błąd. Musimy go usunąć.

Krok 3: Tworzenie dynamicznej listy nazw kolumn

Teraz tworzymy dynamiczną listę nazw kolumn, z których będziemy usuwać ostatnie dwie:

1. Klikamy obok paska formuły i wybieramy "fx Dodaj krok"
2. Odwołujemy się do kroku, który zawiera wszystkie nagłówki (np. "Nagłówki o podwyższonym poziomie")
3. Używamy funkcji Table.ColumnNames aby wyciągnąć listę wszystkich nazw kolumn
4. W karcie "Narzędzia do obsługi listy" wybieramy "Usuń elementy" → "Usuń końcowe elementy" i ustawiamy liczbę elementów na 2

Po wykonaniu tych kroków otrzymujemy listę nazw kolumn bez dwóch ostatnich elementów. To będzie nasza dynamiczna lista kolumn, które chcemy zachować.

Krok 4: Uporządkowanie kroków i odwołań

Teraz musimy upewnić się, że nasze kroki są w odpowiedniej kolejności i prawidłowo się do siebie odwołują:

1. Przeciągamy utworzony krok z listą na koniec
2. Sprawdzamy odwołania — krok z listą powinien odwoływać się do kroku zawierającego wszystkie nagłówki
3. Zmieniamy nazwę kroku na bardziej opisową (np. "Usunięto dwie kolumny") używając klawisza F2 lub opcji "Zmień nazwę" z menu kontekstowego

W tym momencie mamy poprawnie skonfigurowaną dynamiczną listę nazw kolumn, które chcemy zachować.

Krok 5: Zastosowanie dynamicznej listy do wyboru kolumn

Ostatnim krokiem jest zastosowanie naszej dynamicznej listy do funkcji Table.SelectColumns:

1. Modyfikujemy krok, który zawiera funkcję Table.SelectColumns
2. Zamiast zahardkodowanej listy kolumn, używamy odwołania do naszego wcześniejszego kroku z listą
3. Używamy składni #"nazwa kroku" (np. #"Usunięto dwie kolumny")

Po zatwierdzeniu tych zmian, nasze zapytanie będzie zawsze dynamicznie wybierać wszystkie kolumny oprócz dwóch ostatnich, niezależnie od ich nazw.

Dodatkowe przekształcenia danych

W moim przykładzie chciałem również wykonać dodatkowe przekształcenie — umieścić nazwy miesięcy w jednej kolumnie, a wartości liczbowe w drugiej. Aby to zrobić:

1. Zaznaczamy dwie pierwsze kolumny
2. Klikamy prawym przyciskiem myszy i wybieramy opcję "Anuluj i przedstawienie innych kolumn"
3. W pasku formuły zmieniamy domyślne nazwy kolumn "Atrybut" na "Miesiąc" i "Wartość" na "Sprzedaż"

To przekształcenie pozwala nam uzyskać bardziej przejrzystą strukturę danych, gdzie miesiące i wartości sprzedaży są wyraźnie oddzielone.

Testowanie rozwiązania na różnych raportach

Aby upewnić się, że nasze rozwiązanie działa poprawnie, przetestowałem je na różnych raportach, zmieniając źródło danych w kroku nawigacji:

1. Raport 1: zawierał różne nazwy miesięcy
2. Raport 2: zawierał dodatkowy miesiąc (kwiecień)
3. Raport 3: miał mniej kolumn, ale rozwiązanie nadal działało prawidłowo

We wszystkich przypadkach nasze dynamiczne rozwiązanie prawidłowo usuwało dwie ostatnie kolumny, niezależnie od ich nazw i struktury raportu. To potwierdza, że stworzyliśmy uniwersalne rozwiązanie, które będzie działać z różnymi zestawami danych.