Wiersze z wieloma rekordami do prawidłowego zestawu danych – scalanie 1 zapytania – PowerQuery #7

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

Wiersze z wieloma rekordami do prawidłowego zestawu danych – podział po ograniczniku na wiersze – PowerQuery #6

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

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

Wiersze z wieloma rekordami do prawidłowego zestawu danych – dołączanie zapytań – PowerQuery #5

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

Sortowanie nagłówków kolumn alfabetycznie – PowerQuery #4

Czasami potrzebujemy posortować dane po nagłówkach kolumn (w poziomie).

Możemy to zrobić w Excelu sortując w poziomie (ważne nie można w ten sposób sortować w tabeli Excela). Wystarczy zaznaczyć dane i wybrać polecenie Sortuj z karty Dane. Czasami dodaje się jeszcze numerację oryginalną kolumn, żeby można do niej łatwo wrócić.

PQ 4 - Sortowanie nagłówków kolumn alfabetycznie 01

ale dziś interesuje nas bardziej jak to zrobić w PowerQuery, gdzie wynik będzie w formie tabeli Excela.

Dla ułatwienia przykładowe dane przechowujemy w tabeli Excela, żeby móc skorzystać polecenia PowerQuery – Z tabeli (w Excelu 2016 znajduje się ono na karcie dane, wcześniej na osobnej karcie dodatku PowerQuery).

PQ 4 - Sortowanie nagłówków kolumn alfabetycznie 02

Trafimy do edytora zapytań (PowerQuery). (Domyślnie doda się krok Zmienione typ po kroku Źródło, który usuwamy, bo nam nie jest potrzebny).

Nagłówki są w nagłówkach – tak jak powinny być ;), ale my potrzebujemy, żeby znalazły się w pierwszym wierszu danych, dlatego rozwijamy polecenie Użyj pierwszego wiersz jako nagłówka (np.: z karty Przekształć) by uzyskać odwrotny efekt.

PQ 4 - Sortowanie nagłówków kolumn alfabetycznie 03

W PowerQuery brak jest możliwości sortowania w poziomie, dlatego musimy wykonać jeszcze kilka kroków. Pierwszym z nich będzie Transponowanie (karta Przekształć).

PQ 4 - Sortowanie nagłówków kolumn alfabetycznie 04

Wtedy pierwszy wiersz staje się kolumną, a kolumnę już w PowerQuery możemy sortować (np.: z karty Narzędzia główne).
PQ 4 - Sortowanie nagłówków kolumn alfabetycznie 05

Teraz pozostaje nam tylko wykonać drogę powrotną, czyli transponujemy dane, by pierwsza kolumna, stała się pierwszym wierszem, a następnie Używamy pierwszego wiersza jako nagłówków i mamy posortowaną tabelę po nagłówkach (w poziomie). Wystarczy tylko Załadować wynik zapytania tam gdzie chcemy. (Musimy tylko pamiętać, żeby nadać kolumną odpowiednie typy danych w tym przykładzie musimy zrobić to z kolumną z datami, żeby zostały prawidłowo wczytane do Excela).

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

Podział kolumny po ilości znaków – PowerQuery #3

Pobrałeś dane ze źródła i jest z nimi poważny problem – są złączone razem. Nie ma w nich żadnego ogranicznika.

PQ 3 - Podział kolumny po ilości znaków - 01

Ten ciąg potrzebujesz podzielić po ilości znaków. W Excelu najszybszym rozwiązaniem byłoby skorzystanie z polecenia Tekst jako kolumny z karty Dane (innym rozwiązaniem byłoby korzystanie z funkcji FRAGMENT.TEKSTU).
Czyli musimy zaznaczyć kolumnę danych, a następnie w pierwszym kroku polecenia wybrać opcję podziału po stałem szerokości (po ilości znaków).

PQ 3 - Podział kolumny po ilości znaków - 02

W drugim kroku musimy zaznaczyć miejsca, w których chcemy dokonać podziału (kliknąć myszką po odpowiedniej ilości znaków, żeby wstawił się linie podziału na kolumny. Podziału na kolumny chcemy dokonać, najpierw po 10, później po 2, 4, 6, 3 i 3 znakach).

PQ 3 - Podział kolumny po ilości znaków - 03

W trzecim ostatnim kroku możemy wybrać jak mają być sformatowane poszczególne kolumny. W tym przykładzie dla wszystkich kolumn wystarczający jest format ogólny – poprawnie zinterpretuje wszystkie wartości – nawet datę z pierwszej kolumny. Będziemy musieli tylko zmienić miejsce docelowe. Zamiast do kolumny A2 chcemy, żeby dane zaczęły się wpisywać od komórki B2.

PQ 3 - Podział kolumny po ilości znaków - 04

Jeśli mamy taki podział w Excelu i wystarczy, że dokonamy go raz to sprawa jest jasna. Ale jeśli dane pochodzą z innego źródła (np.: pliku tekstowego, .csv, czy bazy danych) to chcielibyśmy rozwiązanie bardziej dynamiczne. Rozwiązaniem jest dodatek PowerQuery (dostępny od Excela 2010).

Dla ułatwienia przykładowe dane przechowujemy w tabeli Excela, żeby móc skorzystać polecenia PowerQuery – Z tabeli (w Excelu 2016 znajduje się ono na karcie dane, wcześniej na osobnej karcie dodatku PowerQuery).

PQ 3 - Podział kolumny po ilości znaków - 05

Trafimy do edytora zapytań (PowerQuery), gdzie potrzebujemy rozwinąć polecenie Podziel kolumny, by odnaleźć możliwość dzielenia po ilości znaków. (Możemy usunąć domyślnie dodany krok Zmieniono typ, gdyż nic nam w tym momencie nie daje).

PQ 3 - Podział kolumny po ilości znaków - 06

Tutaj niestety nie jest tak prosto, bo podziału możemy dokonać po ilości znaków z lewej bądź prawej strony, albo po powtarzającej się ilości znaków, ale nie ma takiej możliwości jak w polecenie Tekst jako kolumna, gdzie sami klikaliśmy w miejsca podziału.

Na razie ustawmy liczbę znaków na 10 i podział z lewej strony.

PQ 3 - Podział kolumny po ilości znaków - 07

Prawdopodobnie znowu dodał się krok Zmieniono typ, ale tym razem go zostawiamy, żeby daty były poprawnie interpretowane. Dla nas jednak jest ważniejszy wcześniejszy krok Podzielono kolumnę według położenia. Klikamy na niego i patrzymy na formułę, która pokazuje się w pasku formuły:

PQ 3 - Podział kolumny po ilości znaków - 08

Jeśli nie widzisz paska formuły przejdź na kartę Widok i zaznacz pole wyboru (checkbox) Pasek formuły.

Jest to formuła w języku M (języku PowerQUery). Prawdopodobnie w większości jest dla Ciebie mało zrozumiała, ale wystarczy, że skupimy się na jej fragmentach {0, 10} oraz {„Połączona kolumna.1”, „Połączona kolumna.2”} . Są to odpowiednio ilości znaków, po których następuje podział po kolumnach (pierwsze zero jest istotne, gdyż odpowiada za pierwszą kolumnę, że zaczyna się ona od początku).

Czego nie widać na pierwszy rzut oka jest to, że liczba znaków jest zawsze od początku tekstu. Czyli jeśli chcemy dokonać podziału najpierw po 10, a potem po 2 znakach, to argument wpisany w formule musimy mieć odpowiednio postać {0, 10, 12, 16, 22, 25, 28}, a nie {0, 10, 2}. Oznacza to podział na trzy kolumny, który w drugim omawianym przez na argumencie musimy nadać nazwy. Jeśli napiszesz mniej nazw kolumn niż wynika to z podziału po ilości znaków, to dalsze kolumny się nie wyświetlą. Dla uproszczenia przykładu kolumny nazywamy „k1”, „k2”, itd.

Mamy mało kolumn, więc jesteśmy w stanie sami policzyć sobie kolejne ilości znaków, ale poniżej znajdziesz sposób na ułatwienie tego procesu w Excelu. Czyli funkcja PowerQuery dzieląca tekst tak jak wcześniej polecenie Tekst jako kolumny powinna mieć postać:

Niestety taka zmiana formuły spowoduje błąd w kolejnym kroku (domyślnej zmianie typów danych), gdyż zmieniliśmy nazwy kolumny. Najlepiej go usunąć i samemu odpowiednio pozmieniać typy kolumn korzystając np.: z polecenia z karty Narzędzia główne.

Mamy odpowiedni podział kolumn, więc możemy je załadować do Excela. Pamiętaj przy tym, żeby rozwinąć polecenie Zamknij i załaduj by zobaczyć możliwość załadowania do, zamiast domyślnego ładowania danych zapytania do nowego arkusza.
Wynik zapytania PowerQuery jest identyczny jak wynik polecenia Tekst jako kolumny:

PQ 3 - Podział kolumny po ilości znaków - 09

Jest jednak duża różnica, ponieważ tabelę wynikową z PowerQuery można odświeżyć klikając na nią np: prawym przyciskiem myszy i wybierają polecenie Odśwież z podręcznego menu. Można nawet korzystając z polecenia Połączenia na karcie Dane ustawić, żeby zapytanie odświeżało się automatycznie przy otwieraniu pliku.

Jak już wspominałem w tym przykładzie jest mało kolumn, ale w pracy miałem sytuację wielokrotnie, że liczba kolumn wynosiła kilkadziesiąt i łatwo byłoby się pomylić przy liczeniu. Dlatego korzystałem z pomocy Excela przy tworzeniu ciągów liczbowy do argumentów funkcji PowerQuery.

Załóżmy sytuację, że mamy podane liczby co ile musi nastąpić podział kolumny, czyli w tym przykładzie 10, 2, 4, 6, 3, 3. Musimy pamiętać, że PowerQuery potrzebuje jeszcze początkowego 0 oraz, że ciąg ma być ilością znaków od początku tekstu, a długości poszczególnych kolumn. Dlatego musimy odpowiednio zsumować wartości:

=SUMA(I$1:I1)

PQ 3 - Podział kolumny po ilości znaków - 10

Pierwsze odwołanie jest zablokowane, żeby się nie ruszało, a drugie jest odblokowane, żeby przeciągając kolumnę w dół odpowiednio poszerzał się zakres, po którym sumujemy.

Jeśli w Twojej wersji Excela jest dostępna funkcja POŁĄCZ.TEKSTY, to wystarczy, że z niej skorzystasz by połączyć wszystkie liczby:

=POŁĄCZ.TEKSTY(„, „;;J1:J7)

Jeśli jej nie masz, to albo wstawiasz dużo argumentów do funkcji ZŁĄCZ.TEKSTY, albo w każdym kolejnym wierszy dołączasz przecinek i kolejną liczbę do komórki powyżej:

=K2&”, „&J3

PQ 3 - Podział kolumny po ilości znaków - 11

Pierwsze 0 wpisujemy ręcznie.
Wtedy końcowy tekst możemy skopiować i wkleić do zapytania PowerQuery.

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