Kontynuujemy temat przekształcania danych, na łatwiejsze do analizy, z poprzedniego wpisu (PQ 5)

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 0102

Rozwiązanie w tym wpisie uwzględnia funkcjonalność PowerQuery, która nie była jeszcze dostępna, gdy Mike Girvin tworzył film (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). Chodzi o podział po ograniczniku na wiersze, a nie kolumny.

Jeśli masz najnowszą wersję PowerQuery na pewno masz dostępne to polecenie.

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.

Teraz potrzebujemy dodać Kolumnę niestandardową z karty Dodaj kolumnę.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 03

Nazwa tej kolumny jest dla nas drugorzędna, więc może zostać domyślna. Najważniejsza jest sama formuła – będzie to po prostu odpowiednie łączenie danych z kolumn. Formuła będzie przypominać analogiczną w Excelu (moglibyśmy ją nawet zrobić w Excelu i dopiero zaczytać do PowerQuery, ale ma to być tylko formuła/kolumna pomocnicza, więc tworzymy ją w PowerQuery).

Chodzi o to, żeby pomiędzy kolumnami, które mają znaleźć się w tym samym wierszu wstawić jeden ogranicznik (np. średnik ; ), a pomiędzy kolumnami, które mają znaleźć się już w nowym wierszu inny ogranicznik (np. znak małpy @).

=[Sklep1]&";"&[Ulica1]&"@"&[Sklep2]&";"&[Ulica2]&"@"&[Sklep3]&";"&[Ulica3]

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 04

Niestety po zatwierdzeniu formuły okazuje się, że nie daje poprawnych wyników w wierszach, gdzie pojawia się wartość null.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 05

Najprościej dla nas będzie naprawić formułę zamieniając wartości null na pusty ciąg tekstowy, pustą wartość. Żeby to zrobić musimy zaznaczyć pierwszy krok naszego zapytania (Źródło), następnie zaznaczyć kolumny, gdzie chcemy zmienić wartości (powinna wystarczyć zmiana od kolumny Sklep2 do końca).

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 06

W oknie Zamieniania wartości w pierwsze pole wpisujemy wartość null, a drugie pozostawiamy puste.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 07

Doda się nowy krok przed stworzoną przez nas formułą i tym razem zadziała ona tak jak chcemy.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 08

Kolejnym krokiem będzie usunięcie zbędnych już kolumn z danymi sklepu (w końcu mamy je stworzonej przez nas kolumnie).

Wystarczy je zaznaczyć, kliknąć w dowolny z nagłówków prawym przyciskiem myszy i wybrać z podręcznego menu usuń kolumny.
Teraz wystarczy zaznaczyć kolumnę pomocniczą (Niestandardową) i wybrać polecenie Podziel kolumny z karty Narzędzia główne.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 09

W opcjach podziału wybieramy niestandardowy ogranicznik i wpisujemy ustaloną przez nas wcześniej małpę '@' (lub inny znak, którego użyliśmy). Rozwijamy również opcje zaawansowane i zaznaczamy, że podziału chcemy dokonać na wiersze, a nie na kolumny.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 10

Rozdzieliliśmy połączone przez nas dane na wiersze…

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 11

… a potrzebujemy jeszcze podzielić je na kolumny, więc wykorzystujemy jeszcze raz polecenie Podziału kolumny po ograniczniku, tylko tym razem po średniku i dzielimy na kolumny.

Dopiero przy tym kroku może nam się przydać zmiana typów w danych w kolumnach, więc jeśli dodał się automatycznie możemy go zostawić.

Pozostaje tylko odfiltrować puste komórki i zmienić nazwy kolumn danych i możemy wczytać dane do Excela.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 12

Analogicznie jak we poprzednim wpisie ), nie ma problemu gdy dodamy kolejne wiersze do oryginalnej tabeli (wystarczy odświeżyć zapytanie). Problem pojawia się gdy dodamy nowe kolumny. Tutaj poprawka jest łatwiejsza niż we wcześniejszym wpisie, gdyż wystarczy te nowe kolumny uwzględnić w napisanej przez nas formule (np. klikając na koło zębate obok kroku dodawania kolumn formuły).

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