Kontynuujemy temat przekształcania danych, na łatwiejsze do analizy, z poprzedniego wpisu (PQ 5)
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ę.
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]
Niestety po zatwierdzeniu formuły okazuje się, że nie daje poprawnych wyników w wierszach, gdzie pojawia się wartość null.
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).
W oknie Zamieniania wartości w pierwsze pole wpisujemy wartość null, a drugie pozostawiamy puste.
Doda się nowy krok przed stworzoną przez nas formułą i tym razem zadziała ona tak jak chcemy.
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.
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.
Rozdzieliliśmy połączone przez nas dane na wiersze…
… 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.
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