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.

rys. nr 1 — Przykładowe dane

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

rys. nr 2 — Z tabeli

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.

rys. nr 3 — Edytor zapytań

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

rys. nr 4 — Polecenie Tekst

W kolejnym kroku musimy dodać kolumnę za pomocą polecenie Kolumna niestandardowa (punkt nr 2 na rysunku nr 5) z karty Dodaj kolumnę.

rys. nr 5 — Kolumna niestandardowa

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.

rys. nr 6 — Tworzenie kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 7.

rys. nr 7 — dane z nową kolumną

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.

rys. nr 8 Kolumna niestandardowa

Otrzymamy dane przedstawione na rysunku nr 9, gdzie kody zostały rozdzielone symbolem @.

rys. nr 9 — Dane z kodami rozdzielonymi znakiem @

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

rys. nr 10 — Podziel kolumny

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.

rys. nr 11 _ dzielenie kolumny według ogranicznika

Wprowadzone parametry podziały kolumny zatwierdzamy przyciskiem OK. i otrzymamy dane przedstawione na rysunku nr 12.

rys. nr 12 — Wydzielone kolumny

Usuwamy krok Zmieniono typ1 z Zastosowanych kroków i otrzymamy dane przedstawione na rysunku nr 13.

rys. nr 13 — Dane

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

rys. nr 14 — Odfiltrowanie pustych

Nasz filtr zatwierdzamy przyciskiem OK i otrzymamy dane przedstawione na rysunku nr 15, które możemy wczytać do Excela.

rys. nr 15 — Odfiltrowane dane

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

rys. nr 16 — Usuń inne kolumny

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

rys. nr 17 — zamknij i załaduj do

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.

rys. nr 18 — Okno ładowania do

Otrzymamy dane załadowane do Excela przedstawione na rysunku nr 19.

rys. nr 19 — Dane w Excelu

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

rys. 20 — Ilość powtórzeń

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

Książka Mistrz Excela reklama