Nasze przykładowe dane utrudniają nam ich analizę.

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 01

Dokładnie chodzi o to, że w naszym przykładzie 2 pierwsze kolumny są wspólne dla kolejnych zestawów danych w wierszu (rekordów), czyli Województwo i Miasto powtarza się dla Sklep1, Sklep2 itd.

Potrzebujemy tak przekształcić dane, żeby dla każdego sklepu z konkretnego Województwa i Miasta powstał 1 wiersz (rekord).

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 02

Takie dane jest dużo prościej analizować. Dodatkowo ograniczają też puste komórki, które występują w pierwszej tabeli.
Ten przykład postaw na podstawie filmu z kanału YT ExcelIsFun – https://www.youtube.com/watch?v=jr2F-mrE1Uc

I jest częścią 3 filmów (jeszcze PQ 6 i PQ 7) pokazujących w jak różny sposób możesz rozwiązać ten problem w zależności od Twojej wiedzy na temat PowerQuery. To rozwiązanie jest prawdopodobnie najbardziej pracochłonne, ale też najprostsze, bo wystarczy kilka powtarzających się kliknięć, żeby uzyskać efekt, który nas interesuje (posłużymy się łączeniem zapytań).

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).

Po wczytaniu danych do edytora zapytań PowerQuery będziemy musieli stworzyć osobne zapytania dla każdego kolejnego sklepu (rekordu), czyli w pierwszym zapytaniu będziemy potrzebowali kolumn Województwo, Miasto, Sklep1 i Ulica1, w kolejnym będziemy podmieniać kolumny Sklep i Ulica na kolejne kolumny.

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 03

Zaznaczać kolumny możemy analogicznie jak w Excelu za pomocą klawisza Shift (od do) albo za pomocą klawisza Ctrl (dodajemy kolejne, w które klikniemy). Ważna jest kolejność, w której zaznaczamy kolumny – zaznaczamy te, które chcemy, żeby zostały i zaznaczamy je od lewej do prawej. Będziemy chcieli usunąć niezaznaczone kolumny.

Wystarczy, że klikniemy prawym przyciskiem myszy w dowolną zaznaczoną kolumnę i z podręcznego menu wybierzemy polecenie Usuń inne kolumny. Tylko zanim to zrobimy…

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 04

…zastanówmy się ile zestawów danych (rekordów) jest maksymalnie w pojedynczym wierszu? Mamy numerowane kolumny, więc widzimy, że mamy maksymalnie 3 sklepy i dokładnie tyle zapytań będziemy potrzebowali stworzyć. Każde z nich będzie musiało mieć dwie pierwsze kolumny i jeszcze 2 odpowiedzialny za kolejny sklep (rekord).

Żeby szybko je stworzyć możemy rozwinąć zakładkę zapytań z lewej strony edytora, następnie kliknąć prawym przyciskiem myszy na zapytanie, które wczytaliśmy (jego nazwa to nazwa tabeli, z której pobraliśmy dane), a następnie z podręcznego menu wybrać polecenie Duplikuj 2 razy, ponieważ potrzebujemy jeszcze dwóch zapytań.

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 05

Warto odrobinę zmienić nazwy zapytań, żebyśmy wiedzieli, które z kolei dane znajdują się w konkretnym zapytaniu (w zapytaniach na razie nie przejmujemy się wartościami null). Co jest dużo ważniejsze musimy zmienić nazwy kolumn Ulica i Sklep, żeby we wszystkich zapytaniach były identyczne (wystarczy dwukrotnie kliknąć na nagłówek kolumny).

Gdy dane w wierszu mamy już podzielone na poszczególne rekordy możemy połączyć zapytania. Wystarczy, że na karcie Narzędzia główne rozwiniemy poleceni Dołącz zapytania, żeby połączyć je w nowym zapytaniu.

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 06

Chcemy połączyć 3 zapytania, więc wybieramy odpowiednią opcję w oknie dołączania i przyciskiem Dodaj… dodajemy interesujące nas zapytania (uwzględniając kolejność w jakiej chcemy je połączyć).

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 07

Uzyskujemy prawie taki wynik jaki chcemy, musimy tylko odfiltrować wartości null po kolumnie Sklep lub ulica i ewentualnie posortować dane i zmienić domyślną nazwę zapytania łączącego tabele.

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 08

Następnie możemy już załadować wynik zapytania do Excela.

PQ 5 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - łączenie zapytań 09

Takie zapytanie (jak większość domyślnie tworzonych zapytań), nie ma problemu gdy dodamy kolejne wiersze do oryginalnej tabeli (wystarczy je odświeżyć). Jednak gdybyśmy chcieli dołożyć kolejne kolumny z danymi kolejnego rekordu, to musielibyśmy tworzyć kolejne zapytanie uwzględniające te kolumny, a następnie uwzględnić je w zapytaniu łączącym poszczególne zapytania.

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