0
0 Produkty w koszyku

No products in the cart.

Power Query 41 — Ponad milion wierszy danych w tabeli przestawnej

W dzisiejszym poście nauczymy się jak załadować ponad milion wierszy do tabeli przestawnej, czyli ponad 1048576 wierszy, które mieszczą się w arkuszu Excela. Będziemy używać do tego zadania dodatku do Excela – Power Query.

Dla ułatwienia dane pobierzemy sobie z pliku tekstowego, czyli wybieramy polecenie Z pliku tekstowego /CSV z karty Dane (rys. nr 1).

Pobieranie danych z pliku tekstowego/ CSV
Pobieranie danych z pliku tekstowego/ CSV

Otworzy nam się okno, w którym musimy znaleźć lokalizację pliku (danych bazowych), jaki chcemy zaimportować do Power Query. Wybieramy plik a następnie nasz wybór zatwierdzamy przyciskiem Importuj (rys. nr 2).

rys. nr 2 — Wybór źródła danych

Otworzy nam się okno z danymi Sprzedaż zagranicznak.csv z danymi, które będziemy chcieli edytować w Power Query, więc klikamy przycisk Edytuj (rys. nr 3).

rys. nr 3 — Edycja pliku CSV

Otworzy nam się Edytor zapytań Power Query, w którym chcemy sprawdzić czy wszystkie dane zostały załadowane poprawnie. Kolumna Kod pocztowy jest błędna, ponieważ Power Query źle rozpoznaje dane (np. dla kodu z wartością 0 z przodu Power Query pomija tą wartość 0 i otrzymujemy kod 4- cyfrowy). Wystarczy zmienić formatowanie tej kolumny. Klikamy na ikonkę 123 przy nazwie kolumny Kod pocztowy, a następnie z podręcznego menu wybrać formatowanie Tekst (rys. nr 4).

rys. nr 4 — Zmiana formatu danych

Pojawi się komunikat, w którym musimy zatwierdzić zmianę typu kolumny za pomocą przycisku Zamień bieżącą (rys. nr 5).

rys. nr 5 — Zmień typ kolumny

W kolejnym etapie wybieramy polecenie Zamknij i załaduj do z karty Narzędzia główne (rys. nr 6).

rys. nr 6 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych. Jeśli posiadasz subskrypcję Excela 365 w tym okienku znajduje się bezpośrednia opcja, aby załadować dane jako Raport w formie tabeli przestawnej (rys. nr 7).

rys. nr 7 — Parametry importowania danych

Jeśli posiadasz starszą wersję pakietu Office to wybieramy polecenie Utwórz tylko połączenie, następnie nasz wybór zatwierdzamy przyciskiem OK (rys. nr 8).

rys. nr 8 — Utwórz tylko połączenie

W Excelu w bocznym panelu stworzyło się zapytanie Power Query, tylko w formie połączenia (rys. nr 9).

rys. nr 9 — Zapytanie Power Query w panelu bocznym

Na tym etapie musimy ręcznie wstawić sobie tabelę przestawną. Klikamy polecenie Tabela przestawna z karty Wstawianie (rys. nr 10).

rys. nr 10 — Tabela przestawna

Otworzy nam się okno Tworzenia tabeli przestawnej. W polu wybierz dane, które chcesz analizować, zaznaczamy opcję Użyj zewnętrznego źródła danych, następnie klikamy przycisk Wybierz połączenie (rys. nr 11).

rys. nr 11 — Okno tworzenia tabeli przestawnej

Otworzy nam się okno z Istniejącymi połączeniami. Zaznaczamy połączenie w tym arkuszu Zapytanie – Sprzedaż Zagranicznak, a następnie klikamy przycisk Otwórz (rys. nr 12).

rys. nr 12 — Istniejące połączenia

Następnie w oknie Tworzenie tabeli przestawnej z rysunku nr 11, wybieramy miejsce wstawienia naszej tabeli (Istniejący arkusz i konkretna komórka $A$1) i tak ustawione parametry zatwierdzamy przyciskiem OK.

Mamy dużą ilość danych, więc musimy trochę poczekać, ponadto Excel robi sobie kopię zapytania, co dodatkowo wydłuża proces przetwarzania danych.

Otrzymamy w Excelu pustą tabelę, przestawną którą stworzymy przeciągając odpowiednie dane do pól etykiet wierszy i kolumn (rys. nr 13).

rys. nr 13 — Pusta tabela przestawna

Przeciągamy pola Miasto i Kraj do obszaru etykiet wierszy, a następnie przeciągamy pole Przychód do obszaru Sumy wartości (rys. nr 14).

rys. nr 14 — Przeciąganie danych do obszaru etykiet 

Otrzymamy tabelę przestawną przedstawioną na rysunku nr 15.

rys. nr 15 — Tabla przestawna

Możemy sobie podejrzeć ilość załadowanych wierszy w panelu Zapytania i połączenia. W tym arkuszu zostało stworzonych 2 501 580 wierszy (rys. nr 16).

rys. nr 16 — ilość wierszy

Klikamy skrót klawiszowy Ctrl+S, aby zapisać nasze dane. Skopiowane zapytanie Sprzedaż zagraniczna (2) zostało stworzone tylko pod tą tabelę przestawną. Połączenie źródłowe możemy dalej wykorzystywać do innych zadań.

Ciekawostką jest, że plik w Excelu dotyczący tego odcinka zajmuje niecałe 30 Mb, w porównaniu do 130 Mb danych w pliku źródłowym csv.


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

Power Query 40 Najdłuższy czas operacji i jej opis

W dzisiejszym poście zajmiemy się wyszukiwaniem najdłuższego czasu operacji oraz opisu tej konkretnej operacji. Zakładamy, że mamy linię produkcyjną, na której są wykonywane pewne czynności. Naszym zadaniem jest znalezienie operacji, która zajmuje najwięcej czasu, a następnie uzyskanie opisu nazwy tej operacji. Dodatkowym warunkiem tego zadania jest opcja, że najdłuższy czas operacji może się powtórzyć – uzyskamy wtedy np. dwie operacje w jednym wierszu. Zadanie to wykonamy w Power Query na podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 - Przykładowe dane
rys. nr 1 — Przykładowe dane

Pierwszym krokiem jest zaczytanie danych do Power Query, w tym celu wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 - Z tabeli
rys. nr 2 — Z tabeli

Otworzy nam się edytor zapytań z zaczytaną tabelą z danymi produkcyjnymi (rys. nr 3).

rys. nr 3 - Edytor zapytań
rys. nr 3 — Edytor zapytań

Zaznaczamy kolumnę Produkt ID a następnie wybieramy polecenie Grupowanie według z karty Narzędzia główne (rys. nr 4).

rys. nr 4 - Grupowanie według
rys. nr 4 — Grupowanie według

Otworzy nam się okno Grupowania według, gdzie możemy ustalić parametry grupowania. Dane grupujemy według kolumny Produkt ID. W polu nazwa nowej kolumny wpisujemy nazwę Najdłuższy czas, a następnie w polu Operacja wybieramy Maksimum. W polu Kolumna wybieramy kolumnę Czas bo na tej kolumnie mają się opierać nasze wyniki. Tak przygotowane parametry grupowania zatwierdzamy przyciskiem OK (rys. nr 5). 

rys. nr 5 - Parametry grupowania
rys. nr 5 — Parametry grupowania

Otrzymamy pogrupowane dane dla każdego ID produktu z maksymalnym czasem trwania operacji przedstawione na rysunku nr 6.

rys. nr 6 - Pogrupowane dane
rys. nr 6 — Pogrupowane dane

Otrzymaliśmy kolumnę z numerem ID produktów i najdłuższy czas trwania operacji. Potrzebujemy teraz wyciągnąć informację z nazwą tych operacji. Z założenia opis ten pokazany jest w danych krok wcześniej w zastosowanych krokach. Możemy scalić zapytanie z tym samym zapytaniem lecz z innym krokiem. Rozwijamy polecenie Połącz (punkt 2 na rysunku nr 7) z karty Narzędzia główne, a następnie wybieramy polecenie Scal zapytania (punkt 3).

rys. nr 7 - Scal zapytania
rys. nr 7 — Scal zapytania

Otworzy nam się okno Scalania danych. Dla tabeli tProdukcja (2) na górze okna zaznaczamy obie kolumny, czyli  Produkt ID oraz Najdłuższy czas. W drugim polu również wybieramy tabelę tProdukcja (2) (bieżący), a następnie w tej samej kolejności co powyżej zaznaczamy obie kolumny z danymi. W polu Rodzaj sprzężenia wybieramy rodzaj operacji, czyli Lewe zewnętrzne (wszystkie z pierwszej, pasujące z drugiej). Tak przygotowane parametry zatwierdzamy przyciskiem OK (rys. nr 8).

rys. nr 8 - Parametry scalania
rys. nr 8 — Parametry scalania

Otrzymujemy dane przedstawione na rysunku nr 9, gdzie pod słowem Table ukryta jest tabela z danymi (wynik porównania dwóch tabel).

rys. nr 9 - Scalone dane
rys. nr 9 — Scalone dane

Tabelki te zawierają tylko jeden wiersz ponieważ grupowanie wykonaliśmy po ostatnim kroku zapytania, czyli Pogrupowano wiersze. Naszym zadaniem jest scalenie danych z kroków Pogrupowano wiersze i Zmieniono typ. Klikamy na nazwę kroku Zmieniono typ, następnie przechodzimy w tryb edycji tekstu za pomocą klawisza F2 i kopiujemy nazwę tego kroku za pomocą skrótu klawiszowego Ctrl+C.

Sprawdzamy czy mamy widoczny pasek formuły. Jeśli nie to możemy go włączyć na karcie Widok klikając checkbox przy poleceniu Pasek formuły.

Będziemy teraz wprowadzać zmiany zapisie naszej formuły scalania w pasku formuły. Pierwszy krok się zgadza, czyli Pogrupowano wiersze. Drugi krok jest taki sam, więc musimy zaznaczyć nazwę kroku (zaznaczone na niebiesko na rysunku nr 10), a następnie wkleić nazwę skopiowanego wcześniej zapytania (Zmieniono typ) za pomocą skrótu klawiszowego Ctrl+V.

rys. nr 10 - Zmiany w formule
rys. nr 10 — Zmiany w formule

Jeśli teraz zatwierdzimy naszą zmianę klawiszem Enter Power Query zwróci nam błąd ponieważ w kroku Zmieniono typ nie było kolumny Najdłuższy czas (rys. nr 11). 

rys. nr 11 - Error
rys. nr 11 — Error

W kroku zmieniono typ mamy natomiast kolumnę Czas [s], której możemy użyć zamiennie. Wystarczy zmienić w zapisie formuły dla tego kroku nazwę Najdłuższy czas na Czas [s].

Zapis formuły powinien wyglądać następująco:

=Table.NestedJoin(#"Pogrupowano wiersze", {"ProduktID", "Najdłuższy czas"}, #"Zmieniono typ", {"ProduktID", "Czas [s]"}, "Pogrupowano wiersze", JoinKind.LeftOuter)

Otrzymamy dane przedstawione na rysunku nr 12. Mamy tutaj sytuację, że dwie operacje mają taki sam czas. 

rys. nr 12  - dwie operacje z takim samym czasem
rys. nr 12 — dwie operacje z takim samym czasem

Gdybyśmy nie mieli duplikatów w danych, wystarczyło by teraz rozwinąć kolumnę Pogrupowano wiersze po kolumnie Opis operacji (jak pokazano na rys. nr 13).

rys. nr 13 - Rozwiń
rys. nr 13 — Rozwiń

Otrzymalibyśmy wtedy dane przedstawione na rysunku nr 14.

rys. nr 14 - Dane po rozwinięciu
rys. nr 14 — Dane po rozwinięciu

Mamy tutaj dwa wiersze z tym samym czasem, a nam zależy aby były one w jednym wierszu. Usuwamy ostatni krok jaki wykonaliśmy, czyli wracamy do danych z rysunku nr 12.

W kolumnie Pogrupowano dane mamy dane w formie tabeli. Musimy dodać kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 15).

rys. nr 15 - Kolumna niestandardowa
rys. nr 15 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej. Zmieniamy nazwę nowej kolumny na Opis operacji, a następnie wpisujemy formułę tej kolumny. Będzie się ona opierać na funkcji Table.Column. Pierwszy argument funkcji to miejsce gdzie jest nasza tabela, czyli kolumna Pogrupowano wiersze. Drugi argument  to nazwa kolumny, którą chcemy wyciągnąć, czyli kolumna Opis operacji (rys. nr 16). Zapis będzie wyglądał następująco:

=Table.Column([Pogrupowano wiersze], "Opis operacji")

Powyższe parametry kolumny niestandardowej zatwierdzamy przyciskiem OK.

rys. nr 15 - Parametry kolumny niestandardowej
rys. nr 15 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 17. Dla pierwszego wiersza pod List ukryte są dwie wartości – dwie nazwy operacji.

rys. nr 17 - Dane z nową kolumną
rys. nr 17 — Dane z nową kolumną

W kolejnym kroku usuwamy kolumnę Pogrupowano wiersze. Klikamy na jej nagłówek prawym przyciskiem myszy, a następnie z podręcznego menu wybieramy polecenie Usuń (rys. nr 18).

 rys. nr 18 - polecenie Usuń
rys. nr 18 — polecenie Usuń

Następnie klikamy ikonkę strzałek przy nazwie kolumny Opis operacji i wybieramy polecenie Wyodrębnij wartości (rys. nr 19).

rys. nr 19 - Wyodrębnij wartości
rys. nr 19 — Wyodrębnij wartości

Otworzy nam się okno wyodrębniania wartości z listy, gdzie w polu Wybierz ogranicznik, który zostanie użyty do połączenia wartości listy wybieramy Niestandardowe, a następnie w polu poniżej wpisujemy nasz ogranicznik, czyli przecinek i spacja. Powyższe parametry zatwierdzamy przyciskiem OK (rys. nr 20).

rys. nr 20 - Parametry wyodrębniania wartości
rys. nr 20 — Parametry wyodrębniania wartości

Otrzymamy wyodrębnione dane przedstawione na rysunku nr 21.

rys. nr 21 - Wyodrębnione dane
rys. nr 21 — Wyodrębnione dane

Pozostaje nam wczytać tak przygotowane dane do Excela. W tym celu korzystamy z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 22).

rys. nr 22 - Zamknij i załaduj do
rys. nr 22 — Zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie wybieramy sposób wyświetlania danych jako Tabela, a następnie miejsce ich wstawienia – Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry zatwierdzamy przyciskiem Załaduj (rys. nr 23).

rys. nr 23 - Parametry ładowania danych
rys. nr 23 — Parametry ładowania danych

Otrzymamy dane zaczytane do Excela przedstawione na rysunku nr 24.

rys. nr 24 - Dane w Excelu
rys. nr 24 — Dane w Excelu

Możemy wprowadzać dowolne zmiany w tabeli z danymi wejściowymi, a następnie wystarczy kliknąć prawym przyciskiem myszy na tabelę z naszego zapytania i z podręcznego menu wybrać polecenie Odśwież, aby dane zostały zaktualizowane.


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

Power Query 39 — Numer tygodnia w roku i miesiącu data początku i końca tygodnia

W dzisiejszym poście zajmiemy się wyznaczaniem numeru tygodnia w roku i miesiącu oraz początku i końca tygodnia. W poradach od 341 do 344 omawialiśmy wyznaczanie tych danych w Excelu za pomocą funkcji .

Excel — Numer tygodnia w roku — porada 341 

Excel — Początek i koniec tygodnia na podstawie daty — porada 342 

Excel — Data początku i końca tygodnia na podstawie numeru tygodnia w roku — porada 343 

Excel — Numer tygodnia w miesiącu różne systemy — porada 344 

Poznamy możliwości Power Query w tym zakresie. Temat ten omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.

rys. nr 1 - Przykładowe dane
rys. nr 1 — Przykładowe dane

Mamy tutaj przedstawione dane zawierające zbiór dat i informację jaki to jest dzień tygodnia. Aby zaczytać dane do Power Query wybieramy polecenie Z tabeli z karty Dane (rys. nr 2).

rys. nr 2 - Z tabeli
rys. nr 2 — Z tabeli

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą. Klikamy ikonkę kalendarza przy nazwie kolumny Data aby zmienić formatowanie. Z podręcznego menu wybieramy typ danych – Data (rys. nr 3). Wyświetli nam się komunikat o Zmianie typu danych, w którym za pomocą przycisku Zamień bieżącą zatwierdzamy zmianę.

rys. nr 3 - Zmiana typu danych
rys. nr 3 — Zmiana typu danych

Otrzymamy dane przedstawione na rysunku nr 4.

rys. nr 4 - Dane w edytorze zapytań
rys. nr 4 — Dane w edytorze zapytań

Chcemy dodać nową kolumnę z danymi zawierającymi numer tygodnia więc korzystamy z poleceń z karty Dodaj kolumnę. Gdybyśmy natomiast chcieli przekształcić nasze dane musimy skorzystać z analogicznych poleceń na karcie Przekształć (rys. nr 5).

rys. nr 5 - Przekształcenie danych
rys. nr 5 — Przekształcenie danych

Naszym zadaniem jest dodanie nowych kolumn i interesującymi nas danymi. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 6)  z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Tydzień roku (punkt 4).

rys. nr 6 - Dodawanie nowej kolumny Tydzień roku
rys. nr 6 — Dodawanie nowej kolumny Tydzień roku

Otrzymamy dane z nową kolumną zawierającą numer tygodnia w roku odpowiadającego naszej dacie (rys. nr 7).

rys. nr 7 - Tydzień roku
rys. nr 7 — Tydzień roku

Istotną informacją tutaj jest, że Power Query działa na systemie pierwszym funkcji NUM.TYGODNIA Excela, czyli data 1 stycznia każdego roku to pierwszy dzień pierwszego tygodnia nowego roku. W Power Query możemy jedynie skorygować od jakiego dnia rozpoczyna się nowy tydzień. W Polsce standardowo nowy tydzień zaczyna się w poniedziałek i takie właśnie jest domyślne działanie funkcji Date.WeekOfYear w Power Query. Możemy to zmienić, wystarczy skopiować nazwę funkcji a następnie nacisnąć przycisk fx obok paska formuły, aby dodać nowy krok i wkleić w pasek formuły zapis =Date.WeekOfYear. Kiedy zatwierdzimy taką formułę klawiszem Enter otrzymamy informacje na temat tej funkcji, między innymi jej parametrów oraz jakie zwraca wartości. Możemy sobie tutaj w polu firstDayOfWeek wybrać dzień jaki chcemy przyjąć za początek tygodnia (rys. nr 8).

rys. nr 8 - funkcja  Date.WeekOfYear
rys. nr 8 — funkcja Date.WeekOfYear

Usuwamy ten krok z Zastosowanych kroków, ponieważ wprowadziliśmy go tylko w celu pokazania możliwości zmiany dnia początku tygodnia. Jeśli chcemy wprowadzić zmianę dotyczącą dnia początku tygodnia możemy wpisać w formułę dodatkowy parametr zawierający nazwę dnia, jaki chcemy przyjąć za początek tygodnia. Załóżmy, że chcemy aby tydzień rozpoczynał się we wtorek, więc w formułę wpiszemy Day.Tuesday (rys. nr 9). Formuła powinna wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Tydzień roku" each Date.WeekOfYear([Data], Day.Tuesday), Int64.Type)

rys. nr 9 - Formuła funkcji Date.WeekOfYear
rys. nr 9 — Formuła funkcji Date.WeekOfYear

Otrzymamy zmienione dane, w których każdy nowy tydzień będzie rozpoczynał się we wtorek (rys. nr 10).

rys. nr 10 - Zmienione dane
rys. nr 10 — Zmienione dane

Zamiast Day.Tuesday możemy wpisać wartość 2 w formule. Zadziała ona prawidłowo. Zapis formuły powinien wtedy wyglądać następująco:

=Table.AddColumn(#"Zmieniono typ", "Tydzień roku" each Date.WeekOfYear([Data], 2), Int64.Type)

W Power Query dni są numerowane od niedzieli do soboty. Niedzieli odpowiada wartość 0, poniedziałek to wartość 1 itd., natomiast sobocie przypisana jest wartość 6.

Wszystkie kolejne funkcje, z których tutaj będziemy korzystać mają ten dodatkowy argument, w którym możemy przyjąć jaki dzień chcemy traktować jako początek tygodnia.

W kolejnym przykładzie wyznaczymy sobie numer tygodnia w miesiącu. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 11)  z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Tydzień miesiąca (punkt 4).

rys. nr 11 - Tydzień miesiąca
rys. nr 11 — Tydzień miesiąca

Otrzymamy nową kolumnę z numerem tygodnia w miesiącu (rys. nr 12).

rys. nr 12 - Numer tygodnia w miesiącu
rys. nr 12 — Numer tygodnia w miesiącu

Dzień uznawany za początek tygodnia wstawił się domyślnie (poniedziałek). Jeśli chcemy zmienić dzień początku tygodnia musimy to zrobić analogicznie jak w poprzednim przykładzie, czyli dopisać ten argument w formułę funkcji na pasku formuły (rys. nr 13). Zakładamy, że chcemy aby tydzień rozpoczynał się w piątek, czyli musimy wpisać argument Day.Friday lub wartość 5. Zapis powinien wyglądać następująco:

=Table.AddColumn(#"Wstawiono Tydzień roku", "Tydzień miesiąca", each Date.WeekOfMonth([Data],5) Int64.Type)

rys. nr 13 - Funkcja  Date.WeekOfMonth
rys. nr 13 — Funkcja Date.WeekOfMonth

Gdybyśmy nie mieli widocznego paska formuły, musimy go uruchomić w karcie Widok (rys. nr 14).

rys. nr 14 - Uruchomienie paska formuły
rys. nr 14 — Uruchomienie paska formuły

Otrzymamy dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek (rys. nr 15).

rys. nr 15 - Dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek
rys. nr 15 — Dane przedstawiające numerację rozpoczynającą nowy tydzień w piątek 

W kolejnym przykładzie dodamy kolumnę zawierającą Początek tygodnia. Zaznaczamy kolumnę Data a następnie rozwijamy polecenie Data (punkt 2 na rys. nr 16)  z karty Dodaj kolumnę, następnie rozwijamy polecenie Tydzień (punkt 3) i wybieramy interesującą nas informację, czyli w naszym przykładzie Początek tygodnia (punkt 4).

rys. nr 16 - Początek tygodnia
rys. nr 16 — Początek tygodnia

Dodamy również kolumnę zawierającą Koniec tygodnia w analogiczny sposób jak na rysunku nr 16. Otrzymamy wtedy dwie nowe kolumny przedstawione na rysunku nr 17.

rys. nr 17 - Dane z początkiem i końcem tygodnia
rys. nr 17 — Dane z początkiem i końcem tygodnia

Zostało tu zastosowana numeracja domyślna, czyli tydzień rozpoczyna się w poniedziałek, a kończy w niedzielę.

Użyliśmy w tych przykładach dwóch funkcji Date.StartOfWeek oraz Date.EndOfWeek.

Istotną informacją dla działań w Power Query jest to, że data 1 stycznia zawsze rozpoczyna pierwszy tydzień nowego roku. Możemy jedynie zmieniać dni początku i końca tygodnia. Nie ma możliwości zrobić kontynuacji numeru tygodnia z poprzedniego roku w nowym roku, tak żeby tydzień zawsze miał 7 dni, jak to było możliwe w Excelu.

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 18).

rys. nr 18 - Zamknij i załaduj do
rys. nr 18 — Zamknij i załaduj do

Otworzy nam się okno Ładowania do, gdzie ustawiamy sposób wyświetlania danych jako Tabela oraz wybieramy miejsce wstawienia danych – Istniejący arkusz i wskazujemy konkretną komórkę (rys. nr 19). Powyższe parametry zatwierdzamy przyciskiem Załaduj.

rys. nr 19 - Okno ładowania do
rys. nr 19 — Okno ładowania do

Otrzymamy dane wczytane do Excela przedstawione na rysunku nr 20.

rys. nr 20 - Dane zaczytane do Excela
rys. nr 20 — Dane zaczytane do Excela

Możemy sobie jeszcze odpowiednio sformatować dane korzystając ze skrótu klawiszowego Ctrl+1, aby przy tych datach wyświetlała się nazwa dnia tygodnia. Otworzy nam się okno formatowania komórek, gdzie wybieramy formatowanie Niestandardowe w karcie Liczby, następnie w polu Typ musimy dopisać zapis ddd (rys. nr 21).

rys. 21 - Formatowanie komórek
rys. 21 — Formatowanie komórek

Otrzymamy sformatowane dane przedstawione na rysunku nr 22.

rys. nr 21 - Sformatowane dane
rys. nr 21 — Sformatowane dane

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

Power Query 38 — Tekst przed ogranicznikiem po ograniczniku i między ogranicznikami

W dzisiejszym poście zajmiemy się wyciąganiem tekstu po ograniczniku, między ogranicznikami bądź przed ogranicznikiem. W pytaniu od widzów nr 104 napisaliśmy formułę w Excelu, która wyciągała tekst znajdujący się pomiędzy przedostatnim a ostatnim ogranicznikiem np. oznaczony na rysunku nr 1 na czerwono.

rys. nr 1 - Tekst który będziemy wyciągać
rys. nr 1 — Tekst który będziemy wyciągać

Formuły te w Excelu były skomplikowane. W Power Query możemy zrobić to samo, ale dużo szybciej. Ponadto możemy wyciągnąć tekst znajdujący się za konkretnym ogranicznikiem, przed lub pomiędzy dwoma konkretnymi ogranicznikami. Zagadnienie to omówimy na podstawie przykładowych danych przedstawionych na rysunku nr 2.

rys. nr 2 - Przykładowe dane
rys. nr 2 — Przykładowe dane

W przykładowych danych mamy zestawione różne ścieżki do plików, z których będziemy chcieli wyciągnąć konkretną informację. Pierwszym krokiem jest zaczytanie danych do Power Query. Otrzymamy Dane w Edytorze zapytań przedstawione na rysunku nr 3.

rys. nr 3 - Edytor zapytań
rys. nr 3 — Edytor zapytań

Z zastosowanych kroków usuwamy krok o nazwie Zmieniono typ. Będziemy dodawać kolumny zawierające wyciągnięte informacje z tekstu z danych wejściowych. Rozwijamy polecenie wyodrębnij (punkt 2 na rys. nr 4) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst przed ogranicznikiem (punkt 3 na rys. nr 4).

rys. nr 4 - Tekst przed ogranicznikiem
rys. nr 4 — Tekst przed ogranicznikiem

Otworzy nam się okno Tekstu przed ogranicznikiem, gdzie w polu Ogranicznik musimy wybrać lub wpisać ogranicznik oznaczający koniec zawartości, którą chcemy wyodrębnić. Wpisujemy / (backslash). Rozwijamy opcje zaawansowane, gdzie możemy wybrać dodatkowe parametry. Domyślnie Power Query czyta dane od lewej do prawej strony i znajdzie nam pierwsze wystąpienie ogranicznika. W opcjach zaawansowanych możemy wybrać ilość ograniczników, które chcemy pominąć. W polu liczba ograniczników do pominięcia wpisujemy wartość 1, czyli chcemy pominąć pierwsze wystąpienie ogranicznika (rys. nr 5). Tak ustawione parametry zatwierdzamy przyciskiem OK.

rys. nr 5 - Okno tekstu przed ogranicznikiem
rys. nr 5 — Okno tekstu przed ogranicznikiem

Otrzymamy dane z nową kolumną o nazwie Tekst przed ogranicznikiem przedstawione na rysunku nr 6.

rys. nr 6 - dane z kolumną z tekstem przed ogranicznikiem
rys. nr 6 — dane z kolumną z tekstem przed ogranicznikiem

Ważne jest, że domyślnie rozpatrujemy dane od lewej strony, a w opcjach zaawansowanych możemy ten parametr zmienić. Opcja ta jest zapisana w pasku formuły naszej funkcji (Text.BeforeDelimiter), co zostało zaznaczone kolorem zielonym na rys. nr 7.

rys. nr 7 - Funkcja  Text.BeforeDelimiter
rys. nr 7 — Funkcja Text.BeforeDelimiter

Możemy edytować nasze zapytanie klikając na koło zębate obok nazwy kroku Wstawiony tekst przed ogranicznikiem (rys. nr 8).

rys. nr 8 - Koło zębate do edycji kroku
rys. nr 8 — Koło zębate do edycji kroku

Ponownie otworzy nam się okno Tekstu przed ogranicznikiem gdzie możemy wybrać jedną z dwóch opcji rozpatrywania danych: Od początku danych wejściowych lub Od końca danych wejściowych (rys. nr 9).

rys. nr 9 - Parametry tekstu przed ogranicznikiem
rys. nr 9 — Parametry tekstu przed ogranicznikiem

Jeśli ustawimy parametr Od końca danych wyjściowych otrzymamy dane przedstawione na rysunku nr 10.

rys. nr 10 - Nowa kolumna z tekstem przed ogranicznikiem patrząc od końca
rys. nr 10 — Nowa kolumna z tekstem przed ogranicznikiem patrząc od końca

W tym przypadku Power Query będzie przeszukiwał dane od prawej strony. Ponieważ nie jest to domyślne działanie nasza formuła się rozbudowała (rys. nr 11).

rys. nr 11 - Rozbudowana formuła funkcji
rys. nr 11 — Rozbudowana formuła funkcji 

W kolejnej nowej kolumnie chcemy otrzymać tekst za ogranicznikiem. W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 12) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst za ogranicznikiem (punkt nr 3).

rys. nr 12 - Tekst za ogranicznikiem
rys. nr 12 — Tekst za ogranicznikiem

Otworzy nam się okno Tekstu za ogranicznikiem, gdzie w polu Ogranicznik wpisujemy znak (/).Identycznie jak dla pierwszego przypadku, czyli Tekstu przed ogranicznikiem. Następnie w opcjach zawansowanych, wybieramy czy chcemy rozpatrywać dane od lewej do prawej strony czy od końca oraz wpisujemy liczbę ograniczników, które chcemy pominąć. Wpisujemy w naszym przykładzie, że chcemy pominąć 2 ograniczniki oraz rozpatrywanie danych domyślne, czyli od lewej do prawej. Ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 13).

rys. nr 13 - Okno tekstu za ogranicznikiem
rys. nr 13 — Okno tekstu za ogranicznikiem

Otrzymamy dane przedstawione na rysunku nr 14. 

rys. nr 14 - Nowa kolumna z tekstem za ogranicznikiem
rys. nr 14 — Nowa kolumna z tekstem za ogranicznikiem

Ostatnie nasze przekształcenie danych będzie dotyczyć wyodrębnienia tekstu między konkretnymi ogranicznikami. W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 15) z karty Dodaj kolumnę, a następnie wybieramy polecenie Tekst między ogranicznikami (punkt 3).

rys. nr 15 - Tekst między ogranicznikami
rys. nr 15 — Tekst między ogranicznikami

Otworzy nam się okno Tekstu między ogranicznikami, gdzie wpisujemy ograniczniki w polach Ogranicznik początkowy i końcowy, czyli w naszym przykładzie w obu polach ten sam znak (/). W opcjach zaawansowanych możemy jak w innych omawianych dziś przypadkach ustawić czy chcemy przeszukiwać dane od początku do końca czy odwrotnie. W pytaniu od widzów było zadanie znalezienia tekstu między przedostatnim a ostatnim ogranicznikiem, więc ustawiamy rozpatrywanie danych od prawej do lewej strony (od końca). W polu Liczba ograniczników początkowych do pominięcia wpisujemy 1 ogranicznik, natomiast w polu Liczba ograniczników końcowych do pominięcia zostawiamy 0. Tak ustawione parametry zatwierdzamy przyciskiem OK (rys. nr 16).

rys. nr 16 - Okno tekstu między ogranicznikami
rys. nr 16 — Okno tekstu między ogranicznikami

Otrzymamy dane przedstawione na rysunku nr 17, czyli tekst między ostatnim a przedostatnim ogranicznikiem.

rys. nr 17 - Nowa kolumna z tekstem między ogranicznikami
rys. nr 17 — Nowa kolumna z tekstem między ogranicznikami

Możemy zagłębić się bardziej w opcje zaawansowane, ale wtedy łatwiej się zgubić, ponieważ opcja Skanuj w poszukiwaniu ogranicznika końcowego daje nam dwie możliwości. Po znalezieniu pierwszego ogranicznika w tej opcji możemy wybrać czy chcemy przeszukiwać dane w lewą stronę od tego ogranicznika czy w prawo. Pokażemy te opcje na przykładzie zmiany drugiego ogranicznika na znak @ oraz przy pominięciu jednego znaku @. Ustawione parametry powinny wyglądać jak na rysunku nr 18.

rys. nr 18 - Zmiany parametrów tekstu między ogranicznikami
rys. nr 18 — Zmiany parametrów tekstu między ogranicznikami

Otrzymamy wtedy dane przedstawione na rysunku nr 19. Możemy zauważyć, że w górnych wierszach nie ma znaków @ po znaku /, więc Power Query wyciągnie nam cały tekst do końca. W ostatnim wierszu otrzymamy fragment tekstu między ostatnim znakiem / a drugim znakiem @, zgodnie z ustawionymi parametrami.

rys. nr 19 - zmienione dane
rys. nr 19 — zmienione dane

Najlepiej przeczytać dokładnie wszystkie opcje i przetestować ich działanie.

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 20).

rys. nr 20 - Zamknij i załaduj do
rys. nr 20 — Zamknij i załaduj do

Otrzymamy dane załadowane do Excela. Zadanie to jest dużo szybsze w Power Query, musimy tylko pamiętać o dokładnym czytaniu parametrów oraz o tym w którą stronę czytamy dane (od początku do końca czy odwrotnie).


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

Power Query 37 — Dynamiczna ilość kolumn przy podziale ogranicznikiem — Funkcje języka M

W dzisiejszym poście zajmiemy się kontynuacją poprzedniego, a dokładnie drugim sposobem rozwiązania zadania polegającego na dynamicznym określeniu ilości kolumn przy podziale ogranicznikiem. Rozwiązanie to jest wykorzystywane przez Billa Szysza, jest ono przeznaczone dla bardziej zaawansowanych użytkowników, ponieważ wymaga większej znajomości funkcji i kodu M w Power Query. Zadanie to wykonamy po podstawie przykładowych danych przedstawionych na rysunku nr 1.

rys. nr 1 — Przykładowe dane

Pierwszym krokiem jest zaczytanie danych wejściowych do Power Query. W tym celu klikamy polecenie Z tabeli/zakresu z karty Dane (rys. nr 2).

rys. 2 — Z tabeli/ zakresu

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z danymi przedstawioną na rysunku nr 3.

rys. 3 — Edytor zapytań

Z zastosowanych kroków usuwamy niepotrzebny krok Zmieniono typ. Do dalszych działań potrzebujemy tylko kolumny z produktami więc klikamy prawym przyciskiem myszy na nagłówek kolumny Produkt i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 4).

rys. 4 — Usuń inne kolumny

Otrzymamy interesujące nas dane przedstawione na rysunku nr 5.

rys.5 — Dane po usunięciu innych kolumn

Do tego momentu kroki w działaniu były takie same jak w poprzednim poście https://exceliadam.pl/?p=9720 . Aby ułatwić zrozumienie tematu nie zaczniemy pisać od razu funkcji w pasku formuły tylko dodamy kolumnę niestandardową z karty Dodaj kolumnę (rys. nr 6). Dodamy tą kolumnę, żeby to w niej zacząć pisać funkcję.

rys. 6 — Kolumna niestandardowa

Otworzy nam się okno Kolumny niestandardowej, gdzie w polu Formuły kolumny niestandardowej wpiszemy funkcję. Zapis powinien wyglądać następująco:

=Text.Split([Produkt], ", ")

W funkcji Text.Split argumentem jest kolumna Produkt. Następnie wpisujemy ogranicznik, czyli w naszym przypadku — przecinek  spacja ", ". Po wpisaniu formuły zatwierdzamy naszą kolumnę niestandardową przyciskiem OK (rys. nr 7). 

rys. 7 — Parametry kolumny niestandardowej

Otrzymamy dane przedstawione na rysunku nr 8.

rys. 8 — Kolumna niestandardowa w postaci list

Polecenie to dzieli nasze kolumny na Listy. Pod każdym słowem List jest ukryta lista produktów (przykład przedstawiony został na rysunku nr 9).

rys. 9 — Dane ukryte pod nazwą List

Kolejnym krokiem jest policzenie elementów każdej z tych list. Użyjemy do tego funkcji List.Count. klikamy na nazwę kroku Dodano kolumnę niestandardową, aby otworzyć znowu okno Kolumny niestandardowej i wprowadzić zmiany w formule tej kolumny (rys. nr 10). Zapis powinien wyglądać następująco:

=List.Count(Text.Split([Produkt], ", "))

rys. 10 — zmiany wprowadzone w oknie kolumny niestandardowej

Zatwierdzamy zmienioną formułę przyciskiem OK i otrzymujemy dane przedstawione na rysunku nr 11.

rys. 11 — zmieniona kolumna niestandardowa

Podsumowując najpierw podzieliliśmy dane na Poszczególne listy za pomocą funkcji Text.Split a następnie policzyliśmy ilość elementów w każdej liście za pomocą funkcji List.Count. W pasku formuły możemy wprowadzić zmiany, aby uzyskać interesujący nas wynik. Zamieniamy funkcję Table.AddColumn na List.Transform oraz dodajemy argument Produkt. Formuła powinna wyglądać jak na rysunku nr 12.

=List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", ")))

rys. 12 — Formuła funkcji List.Transform

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 13.

rys. 13 — Wynik funkcji List.Transform

Otrzymaliśmy listę wszystkich elementów na konkretnych listach. Za pomocą funkcji List.Transform wyciągnęliśmy z danych tylko jedną kolumnę. Powinniśmy pamiętać, że nie potrzebujemy listy wszystkich elementów tylko maksymalną wartość. Wystarczy wstawić w formułę na pasku formuł funkcję List.Max. Formuła powinna wyglądać jak na rysunku nr 14.

=List.Max(List.Transform(#"Usunięto inne kolumny" [Produkt], each List.Count(Text.Split(_,", "))))

rys. 14 — Zapis funkcji List.Max

Kiedy zatwierdzimy formułę klawiszem Enter otrzymamy wynik przedstawiony na rysunku nr 15.

rys. 15 Wynik maksymalny

Kolejny krok pokrywa się z krokiem z wcześniejszego filmu, czyli Power Query odcinek 36.

Kopiujemy nazwę kroku Usunięto inne kolumny po zaznaczeniu go i wciśnięciu klawisza F2, za pomocą skrótu klawiszowego Ctrl+C. Klikamy przycisk fx przy pasku formuły i wklejamy skopiowaną nazwę kroku za pomocą skrótu klawiszowego Ctrl+V. Otrzymamy dane przedstawione na rysunku nr 16.

rys. 16 Dane z kroku Usunięto inne kolumny

Rozwijamy polecenie Podziel kolumny (punkt nr 2 na rysunku nr 17) z karty Narzędzia główne, a następnie wybieramy polecenie Według ogranicznika (punkt nr 3 na rysunku nr 17).

rys. 17 — Podziel kolumny według ogranicznika

Otworzy nam się okno Dzielenia kolumny według ogranicznika, gdzie w polu wybierz lub wprowadź ogranicznik wybieramy opcję Niestandardowe (punkt nr 1 na rysunku nr 18). Następnie w polu, które się pojawi wpisujemy nasz ogranicznik, czyli przecinek i spacja. Zaznaczamy Podziel przy każdym wystąpieniu ogranicznika (punkt nr 2), Podziel na Kolumny (punkt nr 3) i wpisujemy liczbę kolumn 2 (wpisujemy mniejszy parametr bo będziemy go zmieniać). Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK.

rys. 18 — parametry dzielenia kolumny

Otrzymamy dane przedstawione na rysunku nr 19. Usuwamy niepotrzebny krok o nazwie Zmieniono typ.

rys. 19 — Podzielone dane

W pasku formuły w tym zapytaniu zmieniamy nazwy poszczególnych kolumn (podświetlone na rysunku nr 20) na odwołanie do wcześniejszego kroku 

rys.20 zmiana nazw kolumn na odwołanie do poprzedniego kroku w pasku formuły

Klikamy nazwę wcześniejszego kroku — Dodano kolumnę niestandardową, wciskamy klawisz F2, a następnie kopiujemy nazwę kroku za pomocą skrótu klawiszowego Ctrl+C. Następnie tworzymy odwołanie do tego kroku, czyli w formule w miejscu zaznaczonym na rysunku nr 20 wklejamy nazwę naszego kroku poprzedzoną znakiem # (hash) i w podwójnym cudzysłowie. Zapis powinien wyglądać jak na rysunku nr 21.

rys. 21 — Zapis formuły

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rysunku nr 22.

rys. 22 — Dane uzyskane z działania funkcji Table.TransformColumn

Otrzymaliśmy jedenaście kolumn dynamicznych, czyli zmieniających się pod wpływem zmian w danych wejściowych.

Jeśli chcemy mieć mniej kroków w działaniach możemy usunąć krok Niestandardowe1, do którego się odwołaliśmy w formule. Wystarczy, że w pasku formuły z rysunku nr 21 zastąpimy nazwę kroku Niestandardowe1 na Usunięto inne kolumny, ponieważ oba kroki zawierają takie same dane. Zapis formuły po zastąpieniu nazw powinien wyglądać jak na rysunku nr 23.

rys. 23 zmiana nazw kroków w pasku formuły

Następnie możemy usunąć już zbędny krok Niestandardowe1. Klikamy na nazwę kroku i wciskamy przycisk Delete. Pojawi nam się komunikat Usuwanie kroku, gdzie potwierdzamy usunięcie go za pomocą przycisku Usuń (rys. nr 24).

rys. 24 — Usuwanie kroku

Tak przygotowane dane możemy załadować do Excela korzystając z polecenia Zamknij i załaduj do z karty Narzędzia główne (rys. nr 25).

rys. 25 — Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela i wskazujemy miejsce ich wstawienia, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak określone parametry zatwierdzamy przyciskiem OK (rys. nr 26)

rys. 26 — Okno importowania danych

Otrzymamy dane zaimportowane do Excela przedstawione na rysunku nr 27.

rys. 27 — Dane zaimportowane do Excela

Możemy w danych źródłowych dopisać dodatkowy wiersz z danymi, aby sprawdzić działanie dynamicznych kolumn. Po odświeżeniu danych w tabeli z Power Query otrzymamy aktualne dane. Tabelę odświeżamy klikając prawym przyciskiem myszy na jej obszar a następnie korzystając z polecenia Odśwież z podręcznego menu.


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