W idealnym świecie dla analityka wszystkie dane są w układach bazodanowych. Nie ma nic lepszego niż zestaw kolumn z wartościami w jednej lub kilku tabelach, które łatwo ze sobą połączymy. Niestety w życiu bywa różnie i czasami nawet dane systemowe mają inny układ. Jednym z często pojawiających się problemów jest zamieszczenie wielu wartości w jednej komórce. Może to wynikać z oszczędności w generowaniu ilości rekordów. Rozwiązaniem tego problemu byłoby podzielenie danych na wiersze, po prostu rozbudować naszą tabelę danych.

Sposoby na podzielenie danych na wiersze

Gdy znamy wszystkie narzędzia Excela to jak zwykle mamy kilka metod na rozwiązanie tego samego problemu. Wszystko zależy od tego ile mamy danych co i dalej chcemy z nimi zrobić. Jeżeli mamy tylko kilka rekordów to możemy podzielić dane na kolumny przyciskiem w karcie Dane. W następnym kroku wkleić wartości z transpozycją i uzupełnić dane w pozostałych kolumnach. Gdy mamy większą liczbę rekordów, takie rozwiązanie jest zbyt czasochłonne.

Inną metodą było użycie makra. W tym przypadku napisanie odpowiedniego makra będzie dość skomplikowane. Samo pisanie makra też może zająć sporo czasu. Zaletą tego jest natomiast automatyzacja przekształcania danych.

Trzeci sposób na podzielenie danych na wiersze to użycie Power Query. To narzędzie łączy zarówno łatwość użycia jak i automatyzację, czyli wszystko co nam potrzebne. Dostępne też jest dla każdego użytkownika niezależnie od wersji Excela czy umiejętności.

Podzielenie danych na wiersze w Power Query

Spójrzmy na nasze przykładowe dane. Mamy tabelę dotyczącą sprzedaży, w której w kolumnie ID produktów zawarta jest lista ID produktów w zamówieniu. W każdym zamówieniu może być inna liczba produktów, dlatego inne sposoby niż użycie Power Query będą o wiele trudniejsze.

Zaciągnięcie danych do Power Query

W pierwszym kroku zaciągamy dane do Power Query. Robimy to po przez wejście na kartę Dane i użycie przycisku Z tabeli/zakresu. W zależności od wersji Excela układ przycisków może być inny ale na pewno w tym miejscu znajdziemy odpowiedni przycisk.

Po zaciągnięciu danych do Power Query po prawej stronie mamy listę wykonanych kroków. Poza określeniem źródła danych mamy też krok, który automatycznie ustawił typy danych w kolumnach. Opcję tę można wyłączyć w ustawieniach. Zawsze warto zwrócić uwagę jak wyglądają dane po tym kroku, czasami PowerQuery rozpoznaje dane w inny sposób niż byśmy chcieli.

Zmieniamy w tym przypadku typ danych dla Daty na Data, bo nie mamy informacji o godzinie. Pozostałe możemy zostawić bez zmian.

Podzielenie danych na wiersze

Przechodzimy do sedna tematu. Klikamy prawym przyciskiem myszy  na kolumnie ID produktów i wybieramy opcję Podziel kolumny, a następnie według ogranicznika. Możem też zaznaczyć kolumnę i użyć przycisku na karcie Narzędzia główne

Po otwarciu okna do dzielenia kolumny pokazuje nam się kilka opcji. Po pierwsze określamy według jakiego ogranicznika chcemy dzielić dane. Następnie możemy podzielić kolumnę na każde wystąpienie lub tylko pierwsze z lewej lub prawej strony. To się przydaje, gdy np. mamy w komórce pełen adres i chcemy z niego wyodrębnić tylko pierwszy element – miasto. Znak cudzysłowu pozostaje bez zmian.

Podzielenie danych na wiersze wymaga wykonania dwóch kroków. Po pierwsze zmieniamy rodzaj ogranicznika, ponieważ naszym ogranicznikiem nie jest średnik a spacja-średnik. Z listy rozwijanej wybieramy niestandardowe i wpisujemy „; ”.

Gdybyśmy teraz zatwierdzili to okno to podzielimy dane na kolumny. Nas interesuje przecież podzielenie danych na wiersze. Zatem rozwijamy opcje zaawansowane. Tu możemy ograniczyć liczbę kolumn jakie powstaną lub wybrać opcję podzielenia danych na wiersze. Wybieramy podział na wiersze i zatwierdzamy ustawienia.

W ten sposób otrzymaliśmy każde ID produktu w osobnych wierszach, a wszystkie ID produktów znajdują się nadal w jednej kolumnie. Pozostałe kolumny zostały odpowiednio uzupełnione danymi. Nie mamy też pustych wartości, ponieważ Power Query podzieliło dane na wiersze w takiej ilości ile było ID w danej komórce. Automatycznie też został dodany krok, który zmienił typ danych na liczbowy.

Załadowanie danych do Excela

Gdy już odpowiednie przekształciliśmy dane pozostaje załadowanie ich do Excela. Rozwijamy przycisk „Zamknij i załaduj do… „ po czym pokazuje nam się okno wyboru gdzie chcemy załadować dane.

W zależności co potrzebujemy zazwyczaj wybieramy tabelę, jeżeli chcemy bezpośrednio pracować na danych albo tworzymy tylko połączenie i ładujemy dane do modelu danych.

Podzielenie danych na wiersze – co jeszcze warto wiedzieć

Gdy mamy jedną kolumnę z połączonymi danymi, które chcemy rozdzielić na wiersze to łatwo zrobimy to według podanego przykładu. W przypadku kilku kolumn również możemy tak postępować dzieląc dane na wiersze z każdej kolumny osobno. Problem z tym, że wtedy bardzo szybko rośnie nam liczba wierszy. Jeśli jednym z przekształceń danych jest ich przefiltrowanie to warto umieścić przed takimi przekształceniami

Jak widać Power Query to świetne narzędzie do takich przekształceń. Nie dość że dodaje nam dodatkowe możliwości niż sam Excel to często same przekształcenia są prostsze w utworzeniu niż podobne działania w Excelu.

Przedstawione rozwiązanie możesz przetestować na załączonym pliku

Zobacz też inne wpisy usprawniające codzienną pracę