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 '&'.

=[Województwo]&";"&[Miasto]

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.

= Table.AddColumn(#"Wstawiono modulo", "L.p.", each if [Wstawiono modulo] = 0 then [Indeks] else [Indeks]-1)

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.

= Table.SelectRows(#"Anulowano przestawienie innych kolumn", each ([Wstawiono modulo] = 0))

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.

= Table.NestedJoin(DataSet2,{"L.p.", "Atrybut"},DataSet2,{"L.p.", "Atrybut"},"Nowa kolumna",JoinKind.Inner)

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.

= Table.NestedJoin(DataSet1,{"L.p.", "Atrybut"},DataSet2,{"L.p.", "Atrybut"},"Nowa kolumna",JoinKind.Inner)

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