0
0 Produkty w koszyku

No products in the cart.

Power Query 64 — Suma ze zmiennej ilości kolumn rozwiązanie w Excelu

W tym poście omówimy sumę ze zmieniającej się ilości kolumn w danych. Będzie to rozwiązanie problemu z Power Query w Excelu.

Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.

Rys. nr 1 – przykładowe dane

Mamy tutaj wynik zapytania Power Query, który tworzy podsumowanie z danych z pierwszej tabeli. Mamy na razie przedstawione tylko 3 miesiące, ale pod spodem dojdą kolejne dane z dodatkowymi miesiącami (rys. nr 2).

Rys. nr 2 – dodatkowe dane

W podsumowaniu z Power Query chcemy mieć kolumnę "Suma". Można dodawać kolumny do zapytania Power Query, tylko trzeba mieć na uwadze, że ilość kolumn w Power Query może się zmienić. Tutaj standardowo chcielibyśmy otrzymać sumę. Użyjemy skrótu klawiszowego Alt + =. Excel wstawi nam automatycznie funkcję SUMA, jak widać na rys. nr 3.

Rys. nr 3 – funkcja SUMA

Możemy sobie uprościć zapis tej funkcji i wstawić w niej ręcznie odpowiedni zakres zamiast nazw tekstowych. Zapis będzie wtedy wyglądał następująco:

=SUMA(I2:K2)

Otrzymamy zsumowane dane w kolumnie L przedstawione na rys. nr 4.

Rys. nr 4 – zsumowane dane

Nasza suma przedstawia wynik z aktualnej sytuacji. Problem polega na tym, że jeśli wstawimy w dane kolejną kolumnę (Kwiecień) i dopiszemy jakieś wartości, to nie zostaną one uwzględnione w kolumnie Suma (rys. nr 5). Nasza Suma się nie rozszerzy, jej zakres się nie zmieni.

Rys. nr 5 – nieuwzględnione dane z Kwietnia w kolumnie Suma

Wróćmy do stanu sprzed dodania dodatkowej kolumny za pomocą skrótu klawiszowego Ctrl+Z. Jeśli natomiast wstawimy kolumnę między np. Lutym a Marcem i wpiszemy jakieś wartości liczbowe, to zostaną one uwzględnione w sumie (rys. nr 6). Zakres Sumy się rozszerzy.

Rys. nr 6 – uwzględnione dane w sumie

Problem pojawia się, gdy nasze dane zostaną rozszerzone o kolumnę obok zakresu uwzględnionego w formule funkcji SUMA. W takiej sytuacji funkcja się nie rozszerza automatycznie, nie dopasowuje do nowego zakresu. Najprostszym rozwiązaniem jest użycie funkcji Excela. Jest możliwe rozwiązanie tego problemu w Power Query, ale jest bardziej skomplikowane i wymaga użycia kilku funkcji. Rozwiązanie to pokażemy w kolejnym poście.

Naszym zadaniem jest dynamiczne ustalenie zakresu funkcji SUMA. Możemy użyć funkcji PRZESUNIĘCIE. Pierwszym argumentem funkcji jest odwołanie, czyli komórki z zakresu I2:L2 (obejmujemy zakresem kolumnę Suma). Musimy ograniczyć to przesunięcie do ilości kolumn minus jedna kolumna. Czyli nie przesuwamy o konkretną ilość wierszy (argument wiersze), czy kolumn (argument kolumny), ale musimy zmienić argument szerokość (czwarty argument). Trzeci argument, czyli szerokość wstawiamy wartość 1. Musimy policzyć liczbę kolumn. Możemy to zrobić za pomocą funkcji LICZBA.KOLUMN. Argumentem funkcji jest tablica, czyli zakres obejmujący nasze 4 kolumny minus jedna kolumna (I2:L2‑1). Zapis formuły powinien wyglądać następująco:

=SUMA(PRZESUNIĘCIE(I2:L2;0;0;1;LICZBA.KOLUMN(I2:L2)-1))

Powyższą formułę zatwierdzamy. Sprawdzimy teraz, czy działa prawidłowo. Zaznaczamy kolumnę L i za pomocą skrótu klawiszowego Ctrl + Shift + Add wstawiamy nową kolumnę przed tą zaznaczoną. Następnie ręcznie wpisujemy dowolną wartość w tej kolumnie. Wynik w kolumnie Suma automatycznie ulegnie zmianie co widać na rys. nr 7.

Rys. nr 7 – uwzględnione dane z nowej kolumny w Sumie

Teraz musimy usunąć dodaną kolumnę ręcznie za pomocą skrótu klawiszowego Ctrl+Shift+Subtract. Teraz sprawdzimy, czy jeśli dodamy dużą ilość danych, zakres kolumn i sumy rozszerzy i przeliczy się prawidłowo. Dodatkowe dane z rysunku nr 2 zaznaczamy i przesuwamy w górę, czyli doklejamy do naszych danych źródłowych. Następnie odświeżamy dane z zapytania, czyli klikamy prawym przyciskiem myszy w dowolnym punkcie tabeli i z podręcznego menu wybieramy polecenie Odśwież (rys. nr 8).

Rys. nr 8 – polecenie Odśwież w podręcznym menu

Otrzymamy odświeżone (zaktualizowane) dane przedstawione na rys. nr 9.

Rys. nr 9 – odświeżone dane

Jak widać na rysunku powyżej nasze dane się przeliczyły, zakres Sumy odpowiednio się rozszerzył i otrzymaliśmy prawidłowe wyniki.

Excel Power Query #62 — Wszystkie cyfry z tekstu

W dzisiejszym poście nauczymy się jak wyciągnąć wszystkie cyfry z tekstu w Power Query. W poprzednim odcinku na temat Power Query pokazywaliśmy jak wyciągnąć cyfry znajdujące się na początku tekstu bądź na jego końcu. W przykładowych danych mamy tabelę, gdzie tekst został pomieszany z cyframi (rys. nr 1).

Rys. nr 1 – przykładowe dane
Rys. nr 1 – przykładowe dane

W celu zaczytania danych do Power Query wybieramy polecenie Z tabeli/zakresu  z karty Dane (rys. nr 2).

Rys. nr 2 – polecenie Z tabeli/zakresu
Rys. nr 2 – polecenie Z tabeli/zakresu

Otworzy nam się edytor zapytań Power Query z wczytaną tabelą z przykładowymi danymi przedstawiony na rys. nr 3.

Rys. nr 3 – edytor zapytań Power Query z wczytanymi danymi
Rys. nr 3 – edytor zapytań Power Query z wczytanymi danymi

Teraz zajmiemy się odpowiednim przekształcaniem tych danych. W pierwszym kroku musimy skopiować naszą kolumnę. W tym celu wybieramy polecenie Duplikuj kolumnę  z karty Dodaj kolumnę (rys. nr 4).

Rys. nr 4 – polecenie Duplikuj kolumnę
Rys. nr 4 – polecenie Duplikuj kolumnę

Otrzymamy dane ze zduplikowaną kolumną przedstawione na rys. nr 5.

Rys. nr 5 – dane ze zduplikowaną kolumną
Rys. nr 5 – dane ze zduplikowaną kolumną

Teraz zmienimy sobie nazwę nowej kolumny w pasku formuły, aby nie dodawać nowego kroku. Zmieniamy tekst zaznaczony na rys. nr 6 na „Cyfry”. Zapis ze zmienioną nazwą kolumny powinien wyglądać następująco:

=Table.DuplikateColumn(#"Zmieniono typ", "Tekst", "Cyfry")

Rys. nr 6 – nazwa nowej kolumny, którą chcemy zmienić w pasku formuły
Rys. nr 6 – nazwa nowej kolumny, którą chcemy zmienić w pasku formuły

Jeśli nie pokazuje Ci się pasek formuły, możesz go uruchomić zaznaczając checkbox przy poleceniu Pasek formuły na karcie Widok (rys. nr 7).

Rys. nr 7 – uruchamianie paska formuły
Rys. nr 7 – uruchamianie paska formuły

Kiedy mamy już zduplikowaną kolumnę, zaznaczamy ją i rozwijamy polecenie Podziel kolumny (punkt 2 na rys. nr 8) z karty Strona główna (punkt 1), następnie wybieramy polecenie Według liczby znaków (punkt 3).

Rys. nr 8 – ścieżka dostępu do polecenia Według liczby znaków
Rys. nr 8 – ścieżka dostępu do polecenia Według liczby znaków

Otworzy nam się okno Dzielenia kolumny według liczby znaków, gdzie w polu Liczba znaków wpisujemy wartość 1, następnie wybieramy w polu Podziel checkbox przy opcji Powtarzalne. Ważne jest abyśmy rozwinęli opcje zaawansowane, gdzie w polu Podziel na wybieramy dzielenie na Wiersze. Tak ustawione parametry dzielenia kolumny zatwierdzamy przyciskiem OK (rys. nr 9).

Rys. nr 9 – parametry dzielenia kolumny według liczby znaków
Rys. nr 9 – parametry dzielenia kolumny według liczby znaków

Otrzymamy podzielone dane, których fragment został przedstawiony na rys. nr 10.

Rys. nr 10 – fragment danych podzielonych według liczby znaków
Rys. nr 10 – fragment danych podzielonych według liczby znaków

W kolumnie Tekst mamy zduplikowane nasze oryginalne wartości, a w kolumnie Cyfry mamy podział na wszystkie pojedyncze znaki. Nasza kolumna Cyfry zaimportowała się domyślnie jako dane tekstowe, musimy zmienić typ danych. W tym celu klikamy na ikonkę ABC przy nazwie kolumny i z listy rozwijanej wybieramy typ Liczna całkowita (rys. nr 11).

Rys. nr 11 – zmiana typu danych w kolumnie Cyfry
Rys. nr 11 – zmiana typu danych w kolumnie Cyfry

Pojawi nam się okienko, w którym musimy potwierdzić decyzję o zmianie typu danych za pomocą przycisku Zamień bieżącą (rys. nr 12).

Rys. nr 12 – okno Zmień typ kolumny
Rys. nr 12 – okno Zmień typ kolumny

Otrzymamy zmienione dane, gdzie w miejscach znaków tekstowych otrzymamy "Error", ponieważ Power Query nie rozpozna cyfry. Natomiast w miejscach, gdzie mieliśmy do czynienia ze spacją otrzymamy wartość "null" (rys. nr 13).

Rys. nr 13 – dane po zmianie typu danych z tekstu na liczby całkowite
Rys. nr 13 – dane po zmianie typu danych z tekstu na liczby całkowite

Dzięki temu, że otrzymaliśmy błędy (error), w miejscach znaków tekstowych, możemy je łatwo usunąć. Rozwijamy polecenie Usuń wiersze (punkt 2 na rys. nr 14) z karty Strona główna (punkt 1), następnie wybieramy polecenie Usuń błędy (punkt 3).

Rys. nr 14 – ścieżka dostępu do polecenia Usuń błędy
Rys. nr 14 – ścieżka dostępu do polecenia Usuń błędy

Otrzymamy dane po usunięciu wierszy z błędami przedstawione na rys. nr 15 .

Rys. nr 15 – dane po usunięciu wierszy z błędami (error)
Rys. nr 15 – dane po usunięciu wierszy z błędami (error)

W kolejnym kroku chcielibyśmy pozbyć się wierszy z wartościami null, które powstały w miejsce spacji. W tym celu rozwijamy filtry (ikona trójkąta obok nazwy kolumny) i z podręcznego menu wybieramy polecenie Usuń puste (rys. nr 16).

Rys. nr 16 – polecenie Usuń puste
Rys. nr 16 – polecenie Usuń puste

Otrzymamy dane bez pustych wierszy przedstawione na rys. nr 17. 

Rys. nr 17 – dane po usunięciu pustych wierszy
Rys. nr 17 – dane po usunięciu pustych wierszy

Możemy zauważyć, że przy konkretnym tekście mamy tylko te wiersze, gdzie znalazły się cyfry, bez względu czy były one na początku, na końcu czy w środku tekstu. Otrzymaliśmy wyciągnięte wszystkie cyfry z tekstu, lecz nie są one jeszcze odpowiednio zgrupowane. Zaznaczamy kolumnę Tekst i wybieramy polecenie Grupowanie według z karty Strona główna (rys. nr 18).

Rys. nr 18 – polecenie Grupowanie według
Rys. nr 18 – polecenie Grupowanie według

Otworzy nam się okno Grupowania według, gdzie wybieramy typ grupowanie Podstawowy. W polu Nazwa nowej kolumny zmieniamy domyślną nazwę Liczność na Cyfry, następnie w polu Operacja wybieramy z listy rozwijanej Wszystkie wiersze. Tak ustawione parametry grupowania według zatwierdzamy przyciskiem OK (rys. nr 19).

Rys. nr 19 – parametry Grupowania według
Rys. nr 19 – parametry Grupowania według

Otrzymamy pogrupowane dane przedstawione na rys. nr 20.

Rys. nr 20 – pogrupowane dane
Rys. nr 20 – pogrupowane dane

Pod skrótem Table ukryta jest tabelka zarówno z danymi z kolumny Tekst jak i z kolumny Cyfry. Nam zależy tylko na danych z kolumny cyfry, więc zrobimy małe przekształcenie w pasku formuły. Zamiast znaku podkreślenia, który oznacza cały wiersz wpiszemy nazwę kolumny. Zapis formuły powinien wyglądać następująco:

=Table.Group (#"Przefiltrowano wiersze", {"Tekst"}, {{"Cyfry" each [Cyfry], type table [Tekst=anynonnull, Cyfry=numer]}})

Otrzymamy dane tylko z kolumny Cyfry ukryte pod słowem List w naszej tabeli przedstawione na rys. nr 21.

Rys. nr 21 – dane pogrupowane jako lista
Rys. nr 21 – dane pogrupowane jako lista

W pasku formuły nadal mamy typ danych table oraz dodatkowe domyślne dane. Musimy wprowadzić dodatkowe zmiany, między innymi zamienić table na list. Zapis formuły powinien wyglądać następująco:

=Table.Group (#"Przefiltrowano wiersze", {"Tekst"}, {{"Cyfry" each [Cyfry], type list}})

Dzięki takiej zmianie oprócz słowa list mamy zaznaczony odpowiednią ikonką typ danych w kolumnie cyfry, co widać na rys. nr 22.

Rys. nr 22 – dane po zmianie typu na listopad
Rys. nr 22 – dane po zmianie typu na listopad

Dodatkowo dzięki takiej zmianie możemy bez problemu rozwinąć takie dane. W tym celu rozwijamy ikonkę ze strzałkami obok nazwy kolumny Cyfry i wybieramy polecenie Wyodrębnij wartości (rys. nr 23).

Rys. nr 23 – polecenie Wyodrębnij wartości
Rys. nr 23 – polecenie Wyodrębnij wartości

Otworzy nam się okno Wyodrębniania wartości z listy, gdzie z listy rozwijanej musimy wybrać Ogranicznik, jakiego Power Query ma użyć do połączenia danych. W naszym przykładzie, ze względu na to że mamy do czynienia z cyframi wybieramy Brak ogranicznika. Tak ustawione parametry wyodrębniania wartości zatwierdzamy przyciskiem OK (rys. nr 24).

Rys. nr 24 – parametry Wyodrębniania wartości z listy
Rys. nr 24 – parametry Wyodrębniania wartości z listy

Otrzymamy wyodrębnione cyfry w drugiej kolumnie przedstawione na rys. nr 25. 

Rys. nr 25 – dane wyodrębnione z listy
Rys. nr 25 – dane wyodrębnione z listy

Co istotne mimo że wyciągnęliśmy z danych cyfry mają one przypisany typ danych Tekst. Gdyby zależało nam żeby zmienić typ danych na liczby to stracilibyśmy zera wiodące, czyli nie otrzymalibyśmy wszystkich cyfr z tekstu.

Tak przygotowane dane możemy załadować do Excela za pomocą polecenia Zamknij i załaduj do z karty Strona główna (rys. nr 26).

Rys. nr 26 – polecenie Zamknij i załaduj do
Rys. nr 26 – polecenie Zamknij i załaduj do

W Excelu otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabela oraz wskazujemy miejsce wstawienia danych, czyli Istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 27).

Rys. nr 27 – okno Importowania danych
Rys. nr 27 – okno Importowania danych

Otrzymamy dane wstawione do Excela przedstawione na rys. nr 28.

Rys. nr 28 – dane wstawione do Excela
Rys. nr 28 – dane wstawione do Excela

Jak widać Power Query bez problemu poradził sobie z wyciągnięciem wszystkich cyfr z tekstu bez względu, gdzie się one znajdowały, czy były na końcu tekstu, czy na początku, czy też w środku.


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

Excel Power Query #60 — Pobieranie danych z pliku pdf do Excela

W dzisiejszym poście nauczymy się jak pobrać dane z pliku PDF do Power Query. Omówimy tutaj dwie możliwości. W Excelu, Power Query nie ma możliwości pobrania danych bezpośrednio z pliku PDF, natomiast taka możliwość pojawia się w Power BI Desktop (drugie rozwiązanie). Pierwsze rozwiązanie będzie wykonane na podstawie filmu znalezionego w sieci, gdzie najpierw przekształcimy dane z pliku PDF do takiego formatu, który Power Query będzie w stanie pobrać. Załóżmy, że mamy przykładowy plik PDF jak na rys. nr 1.

Rys. nr 1 – przykładowy plik pdf do pobrania
Rys. nr 1 – przykładowy plik pdf do pobrania

Musimy ten plik otworzyć za pomocą WORDA, czyli klikamy na niego prawym przyciskiem myszy i z podręcznego menu rozwijamy polecenie Otwórz za pomocą, a następnie wybieramy polecenie Wybierz inną aplikację (nie mamy na domyślnej liście programu WORD) – rys. nr 2.

Rys. nr 2 – polecenie Wybierz inną aplikację
Rys. nr 2 – polecenie Wybierz inną aplikację

Otworzy się okno o nazwie Jak chcesz otworzyć ten plik, gdzie musimy znaleźć na liście program WORD, zaznaczyć go i nasz wybór zatwierdzić przyciskiem OK (rys. nr 3).

 Rys. nr 3 – okno o nazwie Jak chcesz otworzyć ten plik?
Rys. nr 3 – okno o nazwie Jak chcesz otworzyć ten plik? 

Po zatwierdzeniu otwarcia pliku PDF w programie Word, plik PDF zostaje przekonwertowany na edytowalny dokument tekstowy. Funkcjonalność ta działa od WORDA 2016, we wcześniejszej wersji nie działało. Pojawi nam się okno , gdzie musimy zatwierdzić decyzję, że chcemy przekształcić plik PDF za pomocą przycisku OK. Otworzy nam się WORD z plikiem przedstawionym na rys. nr 4.

Rys. nr 4 – plik pdf otwarty w programie WORD
Rys. nr 4 – plik pdf otwarty w programie WORD

Mamy dane w wordzie, razem z tabelkami, jakie nas interesują. Nie potrzebujemy danych z Worda tylko danych zapisanych jako html, czyli stronę web. Przechodzimy do menu Plik, następnie wybieramy polecenie Zapisz jako (rys. nr 5).

Rys. nr 5 – polecenie Zapisz jako
Rys. nr 5 – polecenie Zapisz jako

Otworzy nam się panel Zapisywania jako, gdzie możemy wybrać lokalizację w jakiej chcemy zapisać plik, zmienić tytuł (nazwę) pliku a przede wszystkim wybrać typ pliku. Rozwijamy listę z rodzajami plików i wybieramy Strona sieci Web (rys. nr 6).

Rys. nr 6 – typ pliku Strona sieci Web
Rys. nr 6 – typ pliku Strona sieci Web

Po wybraniu odpowiedniego typu pliku, a ze względu na to że żyjemy w Polsce, musimy rozwinąć polecenie Więcej opcji (zaznaczone strzałką na rys. nr 7).

Rys. nr 7 – polecenie Więcej opcji
Rys. nr 7 – polecenie Więcej opcji

Otworzy nam się okno Zapisz jako, gdzie w menu Narzędzia wybieramy polecenie Opcje sieci Web (rys. nr 8).

Rys. nr 8 – Opcje sieci Web
Rys. nr 8 – Opcje sieci Web

Otworzy nam się okno Opcji sieci Web, gdzie przechodzimy na zakładkę Kodowanie, a następnie upewniamy się że wybrane jest kodowanie Unicode (UTF‑8), żeby Power Query mógł to prawidłowo odczytać. Tak ustawione kodowanie zatwierdzamy przyciskiem OK (rys. nr 9).

Rys. nr 9 – kodowanie Unicode (UTF-8)
Rys. nr 9 – kodowanie Unicode (UTF‑8)

Automatycznie nastąpi powrót do okna Zapisz jako, gdzie ustawione parametry zatwierdzamy przyciskiem Zapisz. Word stworzył nam plik html i dopiero ten plik będziemy mogli zaczytać do Power Query.

Przy pobieraniu danych Power Query może mieć problem z polskimi znaczkami, dlatego tak ważne było ustawienie kodu UTF‑8. Rozwijamy polecenie Z pliku (punkt 2 na rys. nr 10) z karty Dane (punkt 1), a następnie wybieramy polecenie Ze skoroszytu (punkt 3).

Rys. nr 10 – ścieżka dostępu do polecenia Ze skoroszytu
Rys. nr 10 – ścieżka dostępu do polecenia Ze skoroszytu

Otworzy nam się okno Importowania danych, gdzie na dole obok wybranej Nazwy pliku musimy wybrać opcję Wszystkie pliki a nie tylko pliki Excel. Dzięki tej opcji wyświetlą nam się wszystkie pliki w tym katalogu. Wybieramy plik html i zatwierdzamy nasz wybór przyciskiem Importuj (rys. nr 11).

Rys. nr 11 – okno Importowania danych
Rys. nr 11 – okno Importowania danych

Power Query domyśli się że nie importujemy pliku Excela i otworzy nam okno Nawigatora, gdzie możemy wybrać poszczególne elementy, które chcemy zaczytać do Power Query. Wybieramy tabelę nr 2, następnie przechodzimy do Power Query za pomocą przycisku Przekształć dane. Naszym zadaniem było tylko zaczytanie danych a nie ich przekształcanie więc możemy dane na tym etapie od razu załadować do Excela za pomocą przycisku Załaduj (rys. nr 12).

Rys. nr 12 – okno Nawigator, gdzie wybieramy dane, które chcemy zaczytać do Power Query
Rys. nr 12 – okno Nawigator, gdzie wybieramy dane, które chcemy zaczytać do Power Query

Otrzymamy wybrane dane załadowane do Excela przedstawione na rys. nr 13, dane w dokładnie takiej postaci, w jakiej były w pliku PDF.

Rys. nr 13 – wybrane  dane zaczytane do Excela
Rys. nr 13 – wybrane dane zaczytane do Excela

Istotne jest, że dzięki ustawieniu odpowiedniego kodowania widać polskie znaki w danych. W razie potrzeby dane te możemy edytować za pomocą polecenia Edytuj z karty Zapytanie (rys. nr 14).

Rys. nr 14 – polecenie Edytuj
Rys. nr 14 – polecenie Edytuj

Pierwsze rozwiązanie jest dość długie, ale nie wymaga instalacji dodatkowego oprogramowania, wszystkie działania wykonujemy w pakiecie Microsoft Office.

Drugie rozwiązanie wykorzystuje Power BI Desktop, gdzie rozwijamy polecenie  Pobierz dane  z karty Strona główna, następnie wybieramy polecenie Więcej (rys. nr 15). Power Bi ma możliwość pobierania danych bezpośrednio z pliku pdf.

Rys. nr 15 – Więcej opcji pobierania danych
Rys. nr 15 – Więcej opcji pobierania

Otworzy nam się okno Pobierania danych, gdzie w zakładce Wszystkie wyszukujemy plik typu PDF. Wybór rodzaju pliku do pobrania zatwierdzamy przyciskiem Połącz (rys. nr 16).

Rys. nr 16 – okno Pobierania danych, gdzie wybieramy typ pliku jaki chcemy pobrać
Rys. nr 16 – okno Pobierania danych, gdzie wybieramy typ pliku jaki chcemy pobrać

Otworzy się okno Otwieranie, gdzie musimy wskazać plik pdf, z którego chcemy pobrać dane. Power Bi pamięta ścieżkę dostępu do pliku, na którym wykonujemy poszczególne czynności w tym odcinku. Zaznaczamy plik, który chcemy pobrać i klikamy dwukrotnie lub zatwierdzamy przyciskiem Otwórz (rys. nr 17).

Rys. nr 17 – okno Otwieranie, gdzie wybieramy plik do pobrania
Rys. nr 17 – okno Otwieranie, gdzie wybieramy plik do pobrania

Trochę dłużej trwa wczytywanie (łączenie) niż dla standardowego pliku czy to z Excela, czy np. html, ale Power Bi radzi sobie z wyciąganiem danych z pliku pdf. Otworzy nam się okno Nawigator z wyświetlonymi elementami pliku pdf. Nas interesuje ta tabelka, która wybieraliśmy w pierwszym przykładzie, czyli tutaj Table004 (page 3) i dodatkowo inna tabelka, aby pokazać działanie pobierania danych. Nasze wybrane tabelki zatwierdzamy przyciskiem Załaduj (rys. nr 18).

Rys. nr 18 – okno Nawigatora, gdzie wybieramy interesujące nas elementy pliku pdf
Rys. nr 18 – okno Nawigatora, gdzie wybieramy interesujące nas elementy pliku pdf

Podsumowując pobieramy dwie tabelki z pliku pdf. Ważne jest, że plik pdf nie może być obrazkowy, tzn. nie mogą to być rysunki (zdjęcia) tabel tylko fizycznie utworzone tabelki np. w pliku Word lub Excel. Jeśli mamy do czynienia z plikiem pdf, gdzie znajdują się pliki jpg, czyli zdjęcia jakichś elementów to nic nie jest w stanie odczytać takich danych, przynajmniej ja nie znam takiego programu. Otrzymamy zaczytane do Power Bi dwie tabelki (rys. nr 19).

Rys. nr 19 – tabelki zaczytane do Power Bi
Rys. nr 19 – tabelki zaczytane do Power Bi

Jeśli chcemy je skopiować do Excela, to wystarczy kliknąć w obszar danej tabelki prawym przyciskiem myszy i z podręcznego menu wybrać polecenie Kopiuj tabelę (rys. nr 20).

Rys. nr 20 – polecenie Kopiuj tabelę z podręcznego menu
Rys. nr 20 – polecenie Kopiuj tabelę z podręcznego menu

Po skopiowaniu tabeli wystarczy przejść do okna Excela i ją wkleić za pomocą skrótu klawiszowego Ctrl+V. Dane zostały wklejone do Excela identycznie jak wyglądały w programie Power Bi (rys. nr 21).

Rys. nr 21 – Tabelka wklejona do Excela
Rys. nr 21 – Tabelka wklejona do Excela

Możemy wrócić do Power BI i zrobić szybkie filtrowanie danych, mianowicie kliknąć w ikonkę trójkąta obok nazwy województwa i odznaczyć checkboxy przy nazwach województw, które nas w tym momencie nie interesują. Parametry filtru zatwierdzamy przyciskiem OK (rys. nr 22).

Rys. nr 22 – filtrowanie danych w tabeli
Rys. nr 22 – filtrowanie danych w tabeli

Otrzymamy przefiltrowane dane przedstawione na rys. nr 23.

Rys. nr 23 – przefiltrowane dane w tabeli
Rys. nr 23 – przefiltrowane dane w tabeli

Przygotowaliśmy sobie przefiltrowaną tabelkę, na którą klikamy prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Kopiuj tabelę jak na rys. nr 20. Następnie przechodzimy do Excela, ustawiamy aktywną komórkę obok poprzednio wklejonej tabelki i za pomocą skrótu klawiszowego Ctrl+V, wklejamy przefiltrowaną tabelkę (rys. nr 24).

Rys. nr 24 – Druga tabelka wklejona do Excela (z uwzględnionymi filtrami)
Rys. nr 24 – Druga tabelka wklejona do Excela (z uwzględnionymi filtrami)

Podsumowując Power Bi tak samo jak Power Query przy kopiowaniu danych uwzględnia nałożone wcześniej filtry. Jest to istotne, szczególnie przy pobieraniu danych z pliku pdf.


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

Excel Power Query #59 — Lista wszystkich podfolderów we wskazanym katalogu

W dzisiejszym poście omówimy jak za pomocą Power Query możemy wyciągnąć listę wszystkich folderów, podfolderów wskazanego folderu. Rozwiązanie to jest proste, ale ma jedną wadę, o której pomówimy na końcu. W celu wykonania zadania rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 1) z karty Dane (punkt 1), następnie rozwijamy polecenie Z pliku (punkt 3) i wybieramy polecenie Z folderu (punkt 4).

Rys. nr 1 – ścieżka dostępu do polecenie Z folderu
Rys. nr 1 – ścieżka dostępu do polecenie Z folderu

Na potrzeby naszego zadania przygotowałem katalog z większą ilością podfolderów o nazwie WebinarPQ. Po użyciu polecenia z rys. nr 1 otworzy nam się okno Folder, gdzie musimy wkleić ścieżkę dostępu do tego folderu lub przy użyciu przycisku Przeglądaj, wyszukać folder ręcznie. Wklejoną ścieżkę do wybranego folderu zatwierdzamy przyciskiem OK (rys. nr 2).

Rys. nr 2 – okno Folder
Rys. nr 2 – okno Folder

Wtedy Power Query pobierze informacje o wszystkich plikach znajdujących się w folderze głównym i jego pod folderach. Otworzy się okno z wybranej ścieżki dostępu, gdzie wystarczy kliknąć przycisk Przekształć dane, aby przejść do ich edycji (rys. nr 3). Dzięki drobnym przekształceniom danych otrzymamy listę wszystkich elementów wybranego folderu.

Rys. nr 3 – okno z elementami wybranego folderu
Rys. nr 3 – okno z elementami wybranego folderu

Otworzy nam się Edytor zapytań Power Query z wczytanymi danymi przedstawiony na rys. nr 4.

Rys. nr 4 – fragment danych z edytora zapytań Power Query
Rys. nr 4 – fragment danych z edytora zapytań Power Query

Dla nas istotna jest tylko ostatnia kolumna o nazwie Folder Path, inne są zbędne ponieważ zawierają ind=formacje na temat poszczególnych plików. Klikamy na nazwę kolumny Folder Path prawym przyciskiem myszy i z podręcznego menu wybieramy polecenie Usuń inne kolumny (rys. nr 5).

Rys. nr 5 – polecenie Usuń inne kolumny
Rys. nr 5 – polecenie Usuń inne kolumny

Otrzymamy listę wszystkich folderów i pod folderów zawartych w katalogu WebinarPQ przedstawioną  na rys. nr 6.

Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ
Rys. nr 6 – lista wszystkich folderów i pod folderów katalogu WebinarPQ

Rys. nr 7 – polecenie Usuń duplikaty Jak łatwo zauważyć poszczególne ścieżki się powtarzają, więc musimy je usunąć. Zaznaczamy kolumnę i rozwijamy polecenie Usuń wiersze a następnie wybieramy polecenie Usuń duplikaty (rys. nr 7).

Rys. nr 7 – polecenie Usuń duplikaty
Rys. nr 7 – polecenie Usuń duplikaty

Otrzymamy listę ścieżek dostępu do wszystkich pod folderów wybranego katalogu przedstawioną na rys. nr 8.

Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu
Rys. nr 8 – Lista ścieżek dostępu do wszystkich pod folderów wybranego katalogu

Jeśli potrzebujemy jakieś dodatkowe dane to możemy dalej przekształcać te dane. Naszym zadaniem było przygotowanie listy wszystkich pod folderów. Nasza lista zawiera również główny folder, czyli WebinarPQ, możemy go usunąć rozwijając podręcznego menu (przycisk z trójkącikiem przy nazwie kolumny), a następnie odznaczając checkbox przy ścieżce dostępu do głównego katalogu. Zmiany w filtrowaniu zatwierdzamy przyciskiem OK (rys. nr 9).

Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)
Rys. nr 9 – odfiltrowanie danych (usunięcie ścieżki dostępu do głównego katalogu)

Otrzymamy dane z listą ścieżek dostępu do pod folderów, natomiast bez ścieżki dostępu do głównego katalogu (rys. nr 10).

Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ
Rys. nr 10 – lista ścieżek dostępu do pod folderów katalogu WebinarPQ

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

Rys. nr 11 – polecenie Zamknij i załaduj do
Rys. nr 11 – polecenie Zamknij i załaduj do

Otworzy nam się okno Importowania danych, gdzie ustawiamy sposób wyświetlania danych jako tabela i wskazujemy miejsce ich wstawienia, czyli istniejący arkusz i konkretna komórka. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 12).

Rys. nr 12 – okno Importowania danych
Rys. nr 12 – okno Importowania danych

Otrzymamy dane zaimportowane do Excela i przedstawione na rys. nr 13.

Rys. nr 13 – Dane zaimportowane z Power Query
Rys. nr 13 – Dane zaimportowane z Power Query

Jak wspominaliśmy na początku, rozwiązanie to jest proste, lecz ma jedną wadę. Mianowicie jeśli podejrzymy pliki w folderze WebinarPQ, widzimy że mamy dodatkowy folder o nazwie Nowy folder, który nie pojawił się na naszej liście (rys. nr 14). Dzieje się tak dlatego, że folder ten jest pusty.

Rys. nr 14 – pod foldery katalogu WebinarPQ
Rys. nr 14 – pod foldery katalogu WebinarPQ

Pusty folder nie pojawił się na liście otrzymanej przy użyciu funkcji, która wyciąga informacje o wszystkich plikach znajdujących się we wskazanym folderze, ponieważ nie zawierał żadnych plików. Podsumowując przedstawiony sposób działa poprawnie w 90%, ale musimy pamiętać o pustych folderach.


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

Excel Power Query 56 — Usuwanie ulic z adresów

W dzisiejszym poście omówimy usuwanie części adresów, czyli np. nazw ulic czy placów. Rozwiążemy to zadanie w Power Query. To samo zagadnienie omawialiśmy w poradzie 369, ale za pomocą Excela. Zagadnienie to omówimy na podstawie przykładowych danych przedstawionych na rys. nr 1.

Rys. nr 1 – Przykładowe dane do zadania
Rys. nr 1 – Przykładowe dane do zadania

Przykładowe dane musimy zaczytać do Power Query za pomocą polecenia Z tabeli/ zakresu z karty Dane (rys. nr 2).

Rys. nr 2 – polecenie Z tabeli/ zakresu
Rys. nr 2 – polecenie Z tabeli/ zakresu

Otworzy nam się Edytor zapytań Power Query z wczytaną tabelą z adresami, następnie na tych danych wykonamy przekształcenia, aby otrzymać pożądany efekt. Przekształcenia, które wykonywaliśmy ręcznie w Excelu, czyli zamiana poszczególnych nazw ulic, alei i placów na ulice (ujednolicenie danych), w Power Query możemy zapisać jako kroki. Nawet kiedy nasze dane się zmienią, Power Query znów zamieni te instancje na ulice i będziemy mogli wyciągnąć tylko miasto z naszych danych.

Zaznaczamy kolumnę Miasto, Adres i wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (rys. nr 3).

Rys. nr 3 – polecenie Zamienianie wartości
Rys. nr 3 – polecenie Zamienianie wartości

Polecenia tego musimy użyć dwukrotnie. Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy pl. (oznaczenie placu w adresie), następnie w polu Zamień na wpisujemy ul. (oznaczenie ulicy). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 4).

Rys. nr 4 – okno Zamieniania wartości
Rys. nr 4 – okno Zamieniania wartości

Otrzymamy dane, gdzie wszystkie wystąpienia skrótu pl. zostały zamienione na ul. Następnie musimy powtórzyć te czynności, czyli wybieramy polecenie Zamienianie wartości z karty Narzędzia główne. Otworzy nam się okno Zamieniania wartości, gdzie znowu musimy ustawić jego parametry. Tym razem w polu Wartość do znalezienia wpisujemy al. (oznaczenie alei), a w polu Zamień na wpisujemy ul. (analogicznie jak na rys. nr 4).

Otrzymamy zamienione dane przedstawione na rys. nr 5.

Rys. nr 5 – Zamienione dane
Rys. nr 5 – Zamienione dane

Otrzymaliśmy dane, gdzie różne określenia adresów zostały ujednolicone, zamienione na skrót od ulicy (ul.). Teraz wystarczy wyciągnąć z danych tekst przed ulicą.  W tym celu rozwijamy polecenie Wyodrębnij (punkt 2 na rys. nr 6) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Tekst przed ogranicznikiem (punkt 3).

Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem
Rys. nr 6 – ścieżka dostępu do polecenia Test przed ogranicznikiem

Otworzy nam się okno Tekstu przed ogranicznikiem, gdzie w polu Ogranicznik wpisujemy skrót ul. (ujednolicone oznaczenie ulicy w adresie). Tak ustawiony ogranicznik zatwierdzamy przyciskiem OK (rys. nr 7).

Rys. nr 7 – okno Testu przed ogranicznikiem
Rys. nr 7 – okno Testu przed ogranicznikiem

Otrzymamy wyodrębnione dane z nazwami miast, po usunięciu niepotrzebnego tekstu przedstawioną na rys. nr 8.

Rys. nr 8 – Wyodrębnione dane
Rys. nr 8 – Wyodrębnione dane

Musimy pamiętać, że mieliśmy takie adresy, gdzie po nazwie miast była spacja, a dopiero potem nazwa ulicy, jak również sytuacje, kiedy tej spacji nie było w ogóle. Na końcu nazw niektórych miast pozostały spacje, które są nam niepotrzebne (mimo że ich nie widać). W tym celu rozwijamy polecenie Format (punkt 2 na rys. nr 9) z karty Przekształć (punkt 1), a następnie wybieramy polecenie Przycięcie (punkt 3).

Rys. nr 9 – ścieżka dostępu do polecenia Przycięcie
Rys. nr 9 – ścieżka dostępu do polecenia Przycięcie

Polecenie Przycięcie usuwa zbędne spacje na końcu tekstu. Otrzymamy przycięte dane przedstawione na rys. nr 10.

Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)
Rys. nr 10 – przycięte dane (po usunięciu spacji na końcu tekstu)

Możemy zrobić jeszcze jedno przekształcenie, mianowicie przy dwuczłonowych nazwach miast powinien być myślnik pomiędzy słowami (Skarżysko – Kamienna). W tym celu wybieramy polecenie Zamienianie wartości z karty Narzędzia główne (jak na rys. nr 3). Otworzy nam się okno Zamieniania wartości, gdzie w polu Wartość do znalezienia wpisujemy spację, a w polu Zamień na wpisujemy myślnik (-). Tak ustawione parametry zamieniania wartości zatwierdzamy przyciskiem OK (rys. nr 11).

Rys. nr 11 – okno Zamieniania wartości
Rys. nr 11 – okno Zamieniania wartości

Otrzymamy zmienione dane. W kolejnym kroku powinniśmy zmienić nazwę kolumny, ponieważ po przekształceniach zawiera ona tylko nazwę miasta. Klikamy dwukrotnie na tytuł kolumny i wpisujemy nową nazwę, czyli Miasto (rys. nr 12).

Rys. nr 12 – Zmiana nazwy kolumny
Rys. nr 12 – Zmiana nazwy kolumny

Otrzymamy dane przedstawione na rys. nr 13.

Rys. nr 13 – Dane po przekształceniach
Rys. nr 13 – Dane po przekształceniach

Po tych wszystkich krokach przekształceń, powinniśmy otrzymać te same wyniki, co w poradzie nr 369. Dzięki temu, że zadanie to wykonaliśmy w Power Query i mamy zapisane poszczególne kroki to w sytuacji, kiedy zmieniłyby nam się dane, wystarczy odświeżyć dane aby otrzymać aktualne, poprawne wyniki. 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 14).

Rys. nr 14 – polecenie Zamknij i załaduj do
Rys. nr 14 – polecenie Zamknij i załaduj do

W Excelu otworzy nam się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako Tabel i wskazujemy miejsce wstawienia danych – istniejący arkusz oraz wskazujemy konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy przyciskiem OK (rys. nr 15).

Rys. nr 15 – okno Importowania danych
Rys. nr 15 – okno Importowania danych

Otrzymamy dane wstawione do Excela z wyciągniętymi nazwami miast z całych adresów przedstawione na rys. nr 16.

Rys. nr 16 – Dane wstawione do Excela
Rys. nr 16 – Dane wstawione do Excela

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

Excel Power Query 55 — Automatyczne usuwanie pustych kolumn

W tym poście zajmiemy się usuwaniem pustych kolumn w Power Query. Do mojego kursu na Udemy dostałem zapytanie czy jest możliwe szybkie usunięcie pustych kolumn z danych. W tego typu sytuacjach sam szukam odpowiedzi w gogle. Rozwiązanie znalazłem na stronie www.community.powerbi.com, mianowicie kod funkcji, którą będziemy mogli wykorzystać w naszym zadaniu (rys. nr 1).

 Rys. nr 1 – kod funkcji do usuwania pustych kolumn
Rys. nr 1 – kod funkcji do usuwania pustych kolumn 

Kopiujemy funkcję za pomocą skrótu klawiszowego Ctrl+C, a następnie wklejamy za pomocą skrótu Ctrl+V do Notepada ++, gdzie możemy podejrzeć elementy funkcji w kolorach (rys. nr 2).

Rys. nr 2 – formuła funkcji do usuwania pustych kolumn w Notepadzie ++
Rys. nr 2 – formuła funkcji do usuwania pustych kolumn w Notepadzie ++

Co istotne w tym zapisie – funkcja pobiera nam dane jako tabelę i usuwa puste kolumny z danych. Pobranej funkcji możemy użyć w Power Query. Załóżmy, że mamy przykładowe dane pokazane na rys. nr 3. Wynika z nich że zawierają one dużo pustych kolumn.

Rys. nr 3 – Przykładowe dane
Rys. nr 3 – Przykładowe dane

Rozwijamy polecenie Pobierz dane (punkt 2 na rys. nr 4) z karty Dane (punkt 1), następnie rozwijamy polecenie Z innych źródeł (punkt 3) i wybieramy polecenie Puste zapytanie (punkt 4).

Rys. nr 4 – ścieżka dostępu do polecenia Puste zapytanie
Rys. nr 4 – ścieżka dostępu do polecenia Puste zapytanie

Otworzy nam się pusty Edytor zapytań Power Query, gdzie musimy wkleić nasz kod M, czyli zapis funkcji skopiowany z Notepada++. Wybieramy polecenie Edytor zaawansowany z karty Narzędzia główne (rys. nr 5).

Rys. nr 5 – polecenie Edytor zaawansowany
Rys. nr 5 – polecenie Edytor zaawansowany

Otworzy nam się okno Edytora zaawansowanego, gdzie wklejamy za pomocą skrótu klawiszowego Ctrl+V naszą skopiowaną wcześniej funkcję (rys. nr 6). Zapis powinien wyglądać następująco:

(tbl as table) =>

let

            Headers = Table.ColumnNames(tbl),

            Result = Table.SelectColumns(tbl),

List.Select(Headers, each List.MatchesAny(Table.Column(tbl,_),

each_ <> null)))

in

Result

Rys. nr 6 – Edytor zaawansowany z zapisem funkcji
Rys. nr 6 – Edytor zaawansowany z zapisem funkcji

Wklejoną funkcję zatwierdzamy w Edytorze zaawansowanym przyciskiem Gotowe. Otrzymamy gotową funkcję przedstawioną na rys. nr 7.

Rys. nr 7 – Funkcja w Power Query
Rys. nr 7 – Funkcja w Power Query

Nie ma znaczenia, że zapis tej funkcji był w języku angielskim. Istotne jest, że ta funkcja pobiera tabelę i odpowiednio ją przekształca. Nasze zapytanie nazywa się Zapytanie 1, więc zmienimy jego nazwę w Ustawieniach zapytania na DeleteEmptyColumns, czyli Usuń puste kolumny. Co istotne nasze zapytanie ma postać funkcji co widać przy nazwie zapytania – skrót fx (rys. nr 8).

Rys. nr 8 – Zapytanie w postaci funkcji – oznaczenie fx
Rys. nr 8 – Zapytanie w postaci funkcji – oznaczenie fx

Sposób, którego używamy jest najprostszym sposobem użycia tej funkcji, czyli zaczytanie pustego zapytania i wstawienie do niego funkcji, następnie zaczytanie danych z Excela w formie tabeli do drugiego zapytania. Nie wychodząc z Power Query możemy rozwinąć sobie polecenie Nowe źródło (punkt 1 na rys. nr 9) na karcie Narzędzia główne, następnie rozwinąć polecenie Plik (punkt 2) i wybrać polecenie Excel (punkt 3).

Rys. nr 9 – ścieżka dostępu do polecenia Excel
Rys. nr 9 – ścieżka dostępu do polecenia Excel

Otworzy nam się okno Importowania danych, w którym musimy znaleźć plik z naszymi przykładowymi danymi z rys. nr 3. Wybieramy plik i klikamy na niego dwukrotnie lub zatwierdzamy nasz wybór przyciskiem Otwórz (rys. nr 10).

Rys. nr 10 – Okno Importowania danych
Rys. nr 10 – Okno Importowania danych

Otworzy nam się okno Nawigatora, gdzie na liście elementów wybieramy jeden arkusz, ten który chcemy przekształcić – o nazwie Dane. Nasz wybór zatwierdzamy przyciskiem OK (rys. nr 11).

Rys. nr 11 – okno Nawigator
Rys. nr 11 – okno Nawigator

Arkusz ten zostanie zaczytany do Power Query jako odrębne zapytanie co widać na rys. nr 12.

Rys. nr 12 – Arkusz zaczytany jako odrębne zapytanie
Rys. nr 12 – Arkusz zaczytany jako odrębne zapytanie

Dodatkowo możemy usunąć krok Zmieniono typ z Zastosowanych kroków (Ustawienia zapytania), ponieważ krok ten nic nie wnosi do naszych danych.

Aby usunąć puste kolumny wystarczy przejść na zapytanie z naszą funkcją i w polu tbl (Wprowadź parametr) wybrać naszą zaczytaną tabelę z drugiego zapytania. Wybór tabeli zatwierdzamy przyciskiem Wywołaj (rys. nr 13).

Rys. nr 13 – Wprowadzanie parametru funkcji (wybór tabeli z drugiego zapytania)
Rys. nr 13 – Wprowadzanie parametru funkcji (wybór tabeli z drugiego zapytania)

W wyniku zadziałania funkcji otrzymamy dane w formie tabeli, z których zostały usunięte wszystkie puste kolumny (rys. nr 14).

Rys. nr 14 – Dane w formie tabeli po usunięciu pustych kolumn
Rys. nr 14 – Dane w formie tabeli po usunięciu pustych kolumn

Co istotne do poprawnego zadziałania tej funkcji musi być całkowicie pusta kolumna (np. kolumna nr 7 ma test w przedostatnim wierszu i już nie została usunięta). Po nagłówkach kolumn doskonale widać jak dużo kolumn zostało usuniętych.

Podsumowując pokazaliśmy sposób usunięcia pustych kolumn w Power Query znaleziony w internecie. Jeśli jesteś bardziej zaawansowanym użytkownikiem możesz zaczytać dane do Power Query i wkleić tą funkcję pod razu w jednym zapytaniu. Innym sposobem wywołania funkcji jest polecenie Wywołaj funkcję niestandardową z karty Dodaj kolumnę (rys. nr 15).

Rys. nr 15 – polecenie Wywołaj funkcję niestandardową
Rys. nr 15 – polecenie Wywołaj funkcję niestandardową

Tak przygotowane dane musimy załadować do Excela, w tym celu wybieramy polecenie Zamknij i załaduj z karty Narzędzia główne (rys. nr 16).

Rys. nr 16 – polecenie Zamknij i załaduj
Rys. nr 16 – polecenie Zamknij i załaduj

Nie przekształcaliśmy dodatkowo danych, zależało nam jedynie na usunięciu pustych kolumn z danych. Zapytania zostały załadowane do Excela jako połączenia (ze względu na to, że mamy kilka zapytań) – rys. nr 17.

Rys. nr 17 – Zapytania załadowane do Excela jako połączenia
Rys. nr 17 – Zapytania załadowane do Excela jako połączenia

Zaznaczamy zapytanie Wywołano funkcję i klikamy na nie prawym przyciskiem myszy, następnie z podręcznego menu wybieramy polecenie Załaduj do (rys. nr 18).

Rys. nr 18 – polecenie Załaduj do
Rys. nr 18 – polecenie Załaduj do

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

Rys. nr 19 – okno Importowania danych
Rys. nr 19 – okno Importowania danych

Otrzymamy dane wstawione do Excela przedstawione na rys. nr 20.

Rys. nr 20 – Dane zaczytane do Excela
Rys. nr 20 – Dane zaczytane do Excela

Tak przygotowane dane wymagają jeszcze obróbki, mianowicie uporządkowania ich, usunięcia zbędnych wierszy i zbędnych informacji. Najważniejsze że udało nam się wykonać pierwszy krok, czyli automatyczne usuwanie niepotrzebnych kolumn za pomocą funkcji znalezionej w internecie.


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