POWER QUERY: Zamień kolumny na wiersze
Po co zamieniać kolumny na wiersze? Najczęściej po to, żeby przygotować dane do tabeli przestawnej. Z taką sytuacją masz do czynienia, kiedy masz tabelę z danymi w kolejnych kolumnach. Natomiast w tabeli przestawnej najlepiej mieć dane w postaci bazodanowej.
Zanim pojawił się POWER QUERY, trzeba było kombinować z różnymi, najczęściej czasochłonnymi metodami (np. z wykorzystaniem funkcji TRANSPONUJ) Teraz jest na to znacznie prostszy sposób i co ważne – pozwalający na automatyczną aktualizację. Zainteresowany?
Przeczytaj również:
Kolumny na wiersze: w czym problem?
Wyobraź sobie, że masz stworzyć budżet wynagrodzeń dla kilku działów. Najwygodniej będzie działać, dodając wartości w kolejnych kolumnach reprezentujących miesiące. Szczególnie, jeżeli będziesz używał formuł obliczających potrzebne wartości.
Zatem w czym problem?
Po stworzeniu budżetu i niezbędnych akceptacjach, trzeba będzie przenieść dane do bazy i raportów. Dzięki temu będziesz w stanie porównywać plan z wykonaniem. Najczęściej i najłatwiej robi się to za pomocą tabeli przestawnej. A powyższy format nie jest najlepszy dla tabel. Dużo lepiej sprawdza się układ bazodanowy, tak jak poniżej.
Kolumny na wiersze, czyli zrób UNPIVOT
Doprowadzenie do takiego układu danych, szczególnie gdy masz setki czy tysiące wierszy wymagało sporo zachodu. Wymagało, bo na szczęście mamy POWER QUERY, aka GET & TRANSFORM (od wersji 2016)
Poniżej znajdziesz kolejne kroki jakie powinieneś zrealizować. W nawiasie zaznaczyłem, jeżeli krok jest opcjonalny i wynika z tego jak wyglądają moje dane. Oczywiście możesz pobrać plik startowy i przećwiczyć kolejne etapy.
Potrzebny czas: 3 minuty
Jak zamienić kolumny na wiersze w POWER PIVOT
- Zamień dane do postaci tabeli.
Kliknij w dowolnym miejscu z danymi i przekształć je do postaci tabeli. NARZĘDZIA GŁÓWNE > FORMATUJ JAKO TABELĘ lub skrót CTRL + L
- Dodaj tabelę do POWER QUERY.
Przejdź na kartę POWER QUERY i wybierz polecenie Z TABELI / ZAKRESU. W nowszych wersjach odpowiednie polecenie znajdziesz na karcie DANE, w sekcji POBIERANIE I PRZEKSZTAŁCANIE DANYCH.
- Usuń niepotrzebne kolumny (opcjonalnie).
Usuń ostatnią kolumnę RAZEM. Podsumowanie nie będzie potrzebne. Kliknij prawym przyciskiem myszy i wybierz USUŃ.
- Zamień „null” na zera (opcjonalnie).
Zamieniając wartości null na 0, spowodujesz że dane dla tych kategorii pojawią się w docelowym układzie danych. W przeciwnym razie zostaną pominięte.
Zaznacz kolumny z miesiącami. Przejdź na wstążkę PRZEKSZTAŁĆ, wybierz ZAMIENIANIE WARTOŚCI i w pierwszym okienku wpisz null, a w drugim 0. - Anuluj przestawianie innych kolumn.
Teraz zasadnicza część całej operacji. Zaznacz kolumny „Dział” i „Grupa Kosztów”. Te kolumny pozostaną niezmienione. Kliknij prawym przyciskiem myszy i z menu kontekstowego wybierz polecenie ANULUJ PRZESTAWIANIE INNYCH KOLUMN.
- Zmień nazwę kolumn (opcjonalnie).
Kliknij dwukrotnie w nagłówek kolumny „Atrybut” i zmień nazwę na „Miesiąc”
- Zapisz i załaduj do arkusza.
Przejdź na wstążkę NARZĘDZIA GŁÓWNE i wybierz ZAMKNIJ I ZAŁADUJ. Wybierając miejsce w nowym arkuszu, Twoje dane zostaną załadowane w przekształconej formie do wskazanego miejsca.
Na filmie poniżej zobaczysz wszystkie opisane kroki. Dodatkowo przekształcam dane z kolumny „Grupa kosztów”, aby pozbyć się niepotrzebnych liczb z opisów kosztów.
Podsumowanie
Zamiana kolumn na wiersze, dzięki POWER QUERY jest naprawdę prosta. Przekształcanie danych do takiej postaci jest wręcz koniecznością, jeżeli chcesz efektywnie pracować na tabelach przestawnych.
Jest jeszcze jedna zaleta opisanego mechanizmu. Jeśli dodasz kolejne dane w tabeli źródłowej, wystarczy że później klikniesz ODŚWIEŻ, a przekształcona tabela docelowa zostanie automatycznie zaktualizowana.
2 Comments
Juszka · 29 lipca 2022 at 09:46
Cześć, a co w przypadku gdy tabela generuje się na podstawie danych z bazy zewnętrznej i nie mogę przewidzieć ilości wierszy?
Łukasz Pietrzak · 1 sierpnia 2022 at 11:58
Cześć, o ile dobrze rozumiem pytanie to powinno zadziałać. Dlatego, że wskazujemy kolumny, które nie podlegają UNPIVOTOWI, a więc cała reszta podlega 🙂
Teraz zasadnicza część całej operacji. Zaznacz kolumny „Dział” i „Grupa Kosztów”. Te kolumny pozostaną niezmienione. Kliknij prawym przyciskiem myszy i z menu kontekstowego wybierz polecenie ANULUJ PRZESTAWIANIE INNYCH KOLUMN