W dzisiejszym poście zajmiemy się tematem powtarzania kodów kreskowych bądź innych wartości w kolejnych wierszach. Omówimy rozwiązanie tego zagadnienia w Power Query na podstawie przykładowych danych źródłowych z rysunku nr 1.
Podczas jednej z moich konsultacji online pojawił się problem – potrzeba, aby powtarzać kodu kreskowe kilka razy pod rząd w kolejnych wierszach. Można to zrobić za pomocą VBA, ale tutaj omówimy rozwiązanie w Power Query. W naszych danych źródłowych kody kreskowe zapisane są w postaci ciągów cyfr, w drugiej kolumnie mamy podaną ilość powtórzeń danego kodu.
Aby wczytać dane do Power Query wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).
Otworzy nam się Edytor zapytań z wczytaną tabelą tKody (rys. nr 3). W Ustawieniach zapytania usuwamy krok Zmieniono typ z Zastosowanych kroków, ponieważ jest zbędny. Musimy zmienić typ danych ręcznie.
Nasz kod kreskowy jest zmieniony na liczby, a my chcemy by był traktowany jako tekst, ponieważ funkcja z jakiej będziemy korzystać w Power Query radzi sobie z tekstem, natomiast z liczbami nie (trzeba by dokonywać konwersji). Klikamy na ikonkę ABC123 z boku nazwy tytułu kolumny Kod kreskowy i z podręcznego menu wybieramy polecenie Tekst (rys. nr 4).
W kolejnym kroku musimy dodać kolumnę za pomocą polecenie Kolumna niestandardowa (punkt nr 2 na rysunku nr 5) z karty Dodaj kolumnę.
Otworzy nam się okno Kolumny niestandardowej, gdzie będziemy korzystać z funkcji. Zmieniamy nazwę kolumny na Powt, a następnie w polu Formuła kolumny niestandardowej wpisujemy formułę funkcji dla tej kolumny. Do zapisania formuły użyjemy kolumn z pola Dostępne kolumny (oznaczone strzałkami na rysunku nr 6). Zapis formuły będzie wyglądał następująco:
=Text.Repeat([#"Kod#(1f)kreskowy"],#"Powtórz [x] razy"])
Tak wpisane parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.
Otrzymamy dane przedstawione na rysunku nr 7.
Dane te mają widoczne od razu niedociągnięcie, mianowicie poszczególne kody nie są od siebie niczym oddzielone. W naszym przykładzie kody kreskowe mają po 5 znaków, ale nie każde kody są jednakowe. Musimy wprowadzić modyfikacje w naszą kolumnę niestandardową. Klikamy na ikonkę koła zębatego obok nazwy kroku Dodano kolumnę niestandardową w Zastosowanych krokach. Otworzy nam się po raz kolejny okno Kolumny niestandardowej (rys. nr 8), gdzie musimy zmienić naszą formułę funkcji i wstawić rozdzielenie kodów za pomocą jakiegoś symbolu (w naszym przykładzie @, bo lubię małpki ?). Zapis formuły będzie wyglądał następująco:
=Text.Repeat([#"Kod#(1f)kreskowy"] & "@",#"Powtórz [x] razy"])
Wprowadzoną zmianę zatwierdzamy przyciskiem OK.
Otrzymamy dane przedstawione na rysunku nr 9, gdzie kody zostały rozdzielone symbolem @.
Symbole te posłużą nam do rozdzielenia kolumn. Rozwijamy polecenia Podziel kolumny (punkt nr 2 na rysunku nr 10) z karty Narzędzia główne i wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 10).
Otworzy nam się okno Dzielenia kolumny według ogranicznika (rys. nr 11), gdzie Power Query rozpozna nasz ogranicznik. Musimy rozwinąć sobie Opcje zaawansowane, ponieważ chcemy zrobić podział na Wiersze (zaznaczone zielonym prostokątem na rysunku nr 11) , a nie kolumny, czyli podział ma być w dół danych (zaznaczone zieloną strzałką na rysunku nr 11) a nie w bok.
Wprowadzone parametry podziały kolumny zatwierdzamy przyciskiem OK. i otrzymamy dane przedstawione na rysunku nr 12.
Usuwamy krok Zmieniono typ1 z Zastosowanych kroków i otrzymamy dane przedstawione na rysunku nr 13.
W danych tych odfiltrujemy puste komórki, czyli klikamy ikonkę trójkąta przy nazwie kolumny Powt i w podręcznym menu odznaczamy checkbox przy nazwie Puste (rys. nr 14).
Nasz filtr zatwierdzamy przyciskiem OK i otrzymamy dane przedstawione na rysunku nr 15, które możemy wczytać do Excela.
Tak naprawdę nie potrzebujemy aż trzech kolumn, ponieważ mamy te same dane w kolumnie pierwszej i trzeciej (Kod kreskowy i Powt). Wystarczy nam jedna kolumna z powtórzonymi kodami. Zaznaczamy kolumnę Powt i klikamy na jej tytuł prawym przyciskiem myszy, z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 16).
Tak przygotowane dane możemy załadować do Excela. Wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 17).
Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania danych jako Tabela, a następnie określamy miejsce wstawienia danych jako Istniejący arkusz i wskazujemy konkretną komórkę w tym arkuszu (rys. nr 18). Tak ustawione parametry wstawienia danych zatwierdzamy przyciskiem Załaduj.
Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 19.
Mamy obliczoną dodatkowo ilość wszystkich powtórzeń – 88 sztuk. Taką samą ilość pokazuje nam zapytanie tKody w Zapytaniach dotyczących arkusza (oznaczone zielonymi strzałkami na rysunku nr 20).
Możemy sprawdzić działanie zapytania zmieniając dane w tabeli z danymi źródłowymi. Wystarczy odświeżyć dane z zapytania aby otrzymać aktualne wyniki.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
