Układ danych w power query

Zmiana układu danych w Power Query

Posted on Posted in Raport

W dzisiejszym wpisie dokonamy transformacji danych tabelarycznych na układ bazodanowy. W tym celu wykorzystamy Excelowy dodatek Power Query, który idealnie nadaje się do tego typu operacji. Przedstawione rozwiązania pozwolą na stworzenie połączenia pomiędzy pierwotnym układem danych a jego przetworzoną bazodanową wersją.

 

Zmiana układu z jedno na wielokolumnowy

Pierwsza transformacja jakiej dokonamy, polegać będzie na "wyciągnięciu" danych z jednej kolumny i przypisaniu ich do osobnych kolumn. Aby dokonać transformacji musimy na początek zaznaczyć zakres interesujących nas danych i po wciśnięciu kombinacji Ctrl+T zmienić go w Excelową tabelę.

 

Tworzenie tabeli

 

Następnie klikamy w dowolnym miejscu tabeli i z zakładki Power Query wybieramy opcję "Dane z programu Excel, Z tabeli/zakresu". Kiedy dane pojawią się już w Power Query, możemy przystąpić do modyfikacji ich układu. Na początek z zakładki Dodaj kolumnę wstawiamy Kolumnę indeksu z wartościami zaczynającymi się od 0.

 

Kolumna indeksu w Power Query

 

Po zaznaczeniu powstałej kolumny klikamy na kartę Przekształć a następnie za pomocą ikonki Obliczeń standardowych wybieramy Podziel bez reszty, wprowadzając wartość 3.

 

Podziel bez reszty

 

Dane z powstałej kolumny posłużą nam za numerację wierszy, w której mają znaleźć się nasze dane (0-8). Kolejna kolumna pomocnicza, którą utworzymy posłuży do nadania numerów kolumn w jakich znajdą się dane. Będą to 3 kolumny zawierające informacje o typie produktu, jego nazwie i cenie. Aby utworzyć kolumnę postępujemy tak samo jak w przypadku kolumny z numerami wierszy za wyjątkiem wybrania do obliczeń metody Modulo, czyli obliczenia reszty z dzielenia liczby z kolumny przez określoną wartość. W tym wypadku również jako liczbę, przez którą będziemy dzielić wpisujemy 3.

 

Obliczenie za pomocą metody modulo

 

Tabela jest już gotowa do transformacji. Teraz wystarczy tylko zaznaczyć kolumnę Indeks.1 (na bazie której powstaną nowe kolumny) i z karty Przekształć wybrać ikonkę Kolumny przestawnej. W pojawiającym się okienku deklarujemy dane, na bazie których mają powstać nowe kolumny oraz z opcji zaawansowanych wybieramy sposób łączenia nowych komórek (w tym wypadku będzie to brak agregacji).

 

Kolumna przestawna w power query

 

Po kliknięciu przycisku OK, nasze dane zmienią swój układ. Teraz możemy usunąć niepotrzebną kolumnę Indeksu oraz na nowo zdefiniować nagłówki powstałych kolumn.

 

Nowa tabela

 

Nowy format danych możemy załadować do Excela np. w postaci tabeli.

 

Załaduj jako tabela

 

Od tej pory każde dodanie danych do pierwotnej tabeli, połączone z odświeżeniem zapytania z Power Query, będzie skutkować transformacją danych i dopisaniem ich do tabeli w formie bazodanowej.

 

Zmiana układu z jednego wiersza na wiele wierszy

Kolejny rodzaj przekształcenia jaki wykonamy polegać będzie na zmianie układu danych z jedno na wielowierszowy. Czyli w tym wypadku chcemy aby dane dotyczące sprzedaży kwartalnej znajdowały się w pojedynczej kolumnie.

 

Drugie przekształcenie

 

Aby wykonać transformację należy ponownie załadować dane w formie tabeli do Power Query. W kolejnym kroku zaznaczamy kolumnę o nazwie Typ a następnie dokonujemy Anulowania przestawienia wszystkich pozostałych kolumn tabeli.

 

Anuluj przestawienie kolumn

 

W rezultacie otrzymujemy nowy układ tabeli, który podobnie jak w poprzednim przykładzie możemy zapisać w formie tabeli.

 

Tabelka po zmianie

 

Zmiana układu z jednego wiersza na wiele wierszy (ver.2)

Kolejny przykład jest nieco trudniejszy od swojego poprzednika. Przedstawione dane również znajdują się w jednym wierszu i dodatkowo dotyczą dwóch cech sprzedaży - walutowej i wyrażonej w sztukach.

 

Trzecia transformacja

 

Po załadowaniu danych do Power Query, tak jak w powyższym przykładzie wykonujemy Anulowanie przestawienia wszystkich kolumn, za wyjątkiem kolumny Grupa. Tabela po transformacji powinna wyglądać jak poniżej.

 

Anulowane przestawienie kolumn

 

Następnie po zaznaczeniu kolumny Atrybut, z karty Przekształć, wybieramy opcję Podziel kolumny, gdzie jako ogranicznik ustawiamy znak spacji.

 

Podziel kolumny

 

Podzielona kolumna posłuży nam teraz za źródło danych do stworzenia kolumny warunkowej, za pomocą której podzielimy dane na dwie osobne kolumny wyrażające sprzedaż w PLN i szt. W tym celu z karty Dodaj kolumnę, wstawiamy Kolumnę warunkową, którą deklarujemy według poniższych instrukcji.

 

Kolumna warunkowa

 

W kolejnym kroku usuwamy kolumnę Atrybut.2,  a następnie po zaznaczeniu kolumny Niestandardowe, tworzymy Kolumnę przestawną według poniższych wytycznych.

 

Kolumna przestawna

 

Po tej operacji pozostaje tylko na nowo zdefiniować nagłówki kolumn i tabela jest gotowa.

 

Nowa tabela

 

 

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *