Kontynuujemy temat przekształcania danych, na łatwiejsze do analizy, z poprzednich wpisów (PQ 5 i PQ 6)

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania  010202

Rozwiązanie w tym wpisie uwzględnia uwagi użytkownika The Power User (film https://www.youtube.com/watch?v=SOXvcBeXeyA) w komentarzu pod filmem (https://www.youtube.com/watch?v=jr2F-mrE1Uc) na podstawie, którego powstała seria 3 wpisów (PQ 5, PQ 6 i PQ 7). To rozwiązanie jest najbardziej skomplikowane, ale pozwala Ci się na wyższy poziom znajomości PowerQuery i języka M 😉

Standardowo nasze dane przechowywane są jako tabela, więc zaczytujemy je za pomocą polecenia Z tabeli (z karty dane od Excel 2016 lub z karty PowerQuery od Excela 2010).

W naszym zapytaniu nie potrzebujemy domyślnego kroku Zmieniania typów, więc możemy go usunąć klikając na czerwony x obok niego.

W pierwszym kroku będziemy potrzebowali dodać kolumnę (polecenie Kolumna niestandardowa z karty Dodaj kolumnę) łączącą wartości z dwóch pierwszych kolumn (Województwo i Miasto), czyli kolumn wspólnych dla poszczególnych zestawów danych (rekordów).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 03

Formuła jest analogiczna jak w Excelu (nie jest istotna dla nas nazwa kolumny, która powstanie) i wykorzystuje symbol ampersand ‚&’.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 04

Jak mamy połączone wartości z 2 pierwszych kolumn to ich nie potrzebujemy, więc je kasujemy, a następnie przeciągamy stworzoną przez nas kolumnę z formułę na początek danych.

Po tych krokach wykonujemy transponowania danych (polecenie z karty Przekształć), czyli zamieniamy miejscami wiersze z kolumnami.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 05

W wyniku nasza pierwsza kolumna stanie się pierwszym wierszem i przechowuje całkiem inny zestaw danych. Dlatego potrzebujemy ją ochronić – najprostszym na to sposobem jest przeniesieniem ją do nagłówków (polecenie Użyj pierwszego wiersza jako nagłówków z karty Przekształć).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 06

Nasze zestawy danych mają po dwa wiersze (wcześniej kolumny) i potrzebujemy je zidentyfikować, a następnie połączyć. Zaczniemy od tego, że dodamy kolumnę z indeksem od zera (polecenie z karty Dodaj kolumnę).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 07

Tylko my nie potrzebujemy numeracji wierszy od początku danych, ale numeracji wierszy danych w grupach, czyli pierwszy wiersz będzie miał numer 0, drugi 1, trzeci 0 itd. Wykorzystamy do tego funkcję Modulo (po 2), którą znajdziemy na karcie Dodaj kolumnę, gdy będzie zaznaczona kolumna z indeksem.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 08

Wartość dla Modulo wpisujemy jako 2 i uzyskujmy numerowanie wierszy jakiego potrzebujemy, ale potrzebujemy jeszcze kolumny, która określi pogrupuje nam grupy danych – chodzi o to, żeby wiersze z tej samej grupy danych miały taki sam numer (nie koniecznie w kolejności).

My zrobimy to za pomocą Kolumny warunkowej (karta Dodaj kolumnę).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 09

Nazwijmy ją sobie L.p. i chcemy ją uzależnić od kolumny Wstawiono modulo. Jeśli wartość w tej kolumnie będzie równa zero to chcemy zwracać wartość z kolumny Indeks, jeśli wartość modulo będzie inna (1), to wtedy chcemy Indeks pomniejszony o jeden. Nie możemy dla kolumny warunkowej przypisać od razu wartości indeks minus jeden, ale możemy wybrać wartość z kolumny Indeks…

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 10

, a później odjąć jedynkę w stworzonej formule. Musi być zaznaczone pole wyboru (checbox) Pasek formuły na karcie Widok. Wtedy wystarczy w nim dopisać ‚-1’ na koniec formuły przez zamykającym nawiasem.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 11

Nie będzie już nam potrzebna kolumna Indeks więc możemy ją usunąć.

Zaznaczamy teraz kolumny Modulo i L.p. i anulujemy przestawienie innych kolumn (np. dzięki poleceniu z podręcznego menu po kliknięciu prawym przyciskiem na zaznaczone kolumny).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 12

Teraz czeka nas trudniejsze zadania, bo w dwóch kolejnych krokach potrzebujemy mieć dwa różne filtry na kolumnę Wstawiono modulo. W pierwszym chcemy widzieć zera, a w drugim jedynki.

Stworzenie pierwszego z tych kroków jest banalne, bo wystarczy przefiltrować odpowiednio dane po kolumnie Wstawiono modulo. To stworzenie kolejnego kroku sprawia trudność.

Mamy już widoczny pasek formuły, więc kopiujemy formułę dla pierwszego filtrowania.

Widzimy dokładnie ile ma się równać wartość w kolumnie modulo. Potrzebujemy niemal identycznej formuły tylko musimy zamienić 0 na 1.

Zaczniemy od tego, że ustawiamy się na ostatnim kroku zapytania (filtrze po zerze) i klikamy w symbol fx znajdujący się obok paska z formułą.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 13

Gdy doda się nowy krok do paska formuły wklejamy skopiowaną formułę na przefiltrowanie wierszy tylko zamieniamy 0 na 1.
Mamy teraz 2 kroki z różnymi wynikami filtrowania. Potrzebujemy im zmienić nazwę, żeby łatwiej je później zidentyfikować. Klikamy na nazwę kroku, a następnie naciskamy klawisz F2, żebyśmy mogli zmienić nazwy na np. DataSet1 i DataSet2.

Uff. Ten krok zdecydowanie wykracza ponad rozwiązania, które stosujemy na co dzień.

Teraz potrzebujemy scalić zapytania (polecenie na karcie Narzędzia główne).

Będziemy chcieli połączyć nasze z zapytanie z naszym zapytanie (bieżącym zapytaniem). Dokładnie tak dobrze przeczytałeś, chcemy scalić ze sobą to samo zapytanie. Trzymając klawisz Ctrl zaznaczamy u góry i u dołu kolumny L.p. i Atrybut (przechowuje wartości z nagłówka kolumn, te które wcześniej łączyliśmy). Jako rodzaj sprzężenia wybieramy wewnętrzne i klikamy przycisk OK.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 14

Do zapytania doda się Nowa kolumna z wartościami typu Table. Żeby zobaczyć co się pod nimi kryje wystarczy kliknąć na dowolną komórkę i na dole edytora zobaczymy wartości (tabelę) kryjącą się w zaznaczonej komórce.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 15

W tych komórkach kryją się identyczne wiersze tabeli zapytania. To dlatego, że jeszcze nie zmodyfikowaliśmy formuły kroku scalania. Teraz PowerQuery łączy ostatni krok zapytania (DataSet2) z ostatnim krokiem zapytania.

Musimy zmodyfikować formułę, żeby jedno z zapytań odwoływało się do wcześniejszego kroku (DataSet1), czyli wystarcz, że zmienimy jedną cyfrę 😉

Załóżmy, że zmiany dokonamy w pierwszy odwołaniu, bo najpierw chcemy mieć wiersz z nazwą sklepu, a później ulicą na której jest.

Po zmianie kodu formuły możemy rozwinąć (nie agregować) kolumnę Nowa kolumna klikając na strzałki wskazujące w przeciwnym kierunku (patrz nagłówek kolumny). Potrzebujemy tylko kolumny Wartość i nie chcemy, żeby nadana jej nazwa używała prefiksu kolumny, z której ją rozwijamy.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 16

Jesteśmy już prawie na końcu drogi 😀 Wystarczy, że usuniemy niepotrzebne już kolumny Wstawiono modulo i L.p.
Następnie musimy podzielić kolumnę Atrybut (tą z nagłówków danych, która przechowuje informacje o Województwie i Mieście) po ograniczniku (którym jest średnik) na kolumny (polecenie z karty Narzędzia główne).

Na koniec zostanie tylko zmiana nagłówków kolumn i możemy załadować nasze zapytanie do Excela. Jak z poprzednimi zapytaniami z serii nie ma problemu gdy dodajemy nowe wiersze danych do oryginalnej tabeli. Jest problemy, gdy dodamy nowe kolumny.
Żeby poprawić zapytanie musimy zmodyfikować 4 krok zapytania, gdzie zmieniamy kolejność kolumn (kolumnę z połączonym Województwem i Miastem przesuwamy na początek danych). Kolumny układają się w nieprawidłowej kolejności i musimy poprawić ten krok.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP