Łączenie kolumn z danymi – Excel PowerQuery #2

Mamy problem, gdyż nasze dane rozdzielone są na wiele kolumn przechowujących te same informacje.

PowerQuery 2 - Łączenie kolumn z danymi 01

Takie dane bardzo trudno analizować, więc potrzebujemy je złączyć w pojedyncze kolumn. Wykorzystamy rozwiązanie Oza du Soleil, które opiera się o użycie dodatku do PowerQuery do Excela.

https://www.youtube.com/watch?v=hW9eVTbRbgI

Najpierw będziemy musieli zrobić pewną operację na danych – będziemy musieli połączyć informacje z dwóch kolumn do jednej. Tylko potrzebujemy dodać pomiędzy nimi ogranicznik, po którym później będziemy mogli podzielić je ponownie. Ważne, żebyśmy byli pewni, że ten ogranicznik nie występuje w naszych danych. Mogą to być np.: 2 symbole ^^

=A3&”^^”&B3

PowerQuery 2 - Łączenie kolumn z danymi 02

Następnie oprócz komórki z formułą zaznaczamy pustą komórkę po prawej stronie i przeciągamy je do końca danych. Następnie tak stworzony wiersz kopiujemy w dół do końca wszystkich danych. Musimy jeszcze dodać (skopiować) do naszych danych nagłówek, ponieważ dane do PowerQuery w Excelu należy pobierać z tabeli Excela.

PowerQuery 2 - Łączenie kolumn z danymi 03

Teraz możemy pobrać dane do zapytania PowerQuery. Korzystając z odpowiedniego polecenia z karty dodatku lub z karty Dane od Excela 2016.

PowerQuery 2 - Łączenie kolumn z danymi 04

Po wczytaniu danych do PowerQuery musimy wykonać następujące kroki:
1. Musimy przenieść nagłówki do pierwszego wiersza

PowerQuery 2 - Łączenie kolumn z danymi 05

2. Transponować dane

PowerQuery 2 - Łączenie kolumn z danymi 06

3. Odfiltrować wszystkie wiersze, które zawierają słowo Ilość (w naszym przykładzie), czyli te wiersze, które dla których kolumny były puste. Ważne, że nie możemy po prostu odznaczyć ich w filtrze, tylko musimy skorzystać z filtrów tekstowych i wybrać kryterium nie zawiera.

PowerQuery 2 - Łączenie kolumn z danymi 07

Ważne, że PowerQuery zwraca uwagę na wielkość liter, więc tutaj musimy odpowiednio wypełnić pole zgodnie z zapisanym przez nas tekstem.

4. Anulujemy przestawienie wszystkich kolumn poza pierwszą

PowerQuery 2 - Łączenie kolumn z danymi 08

5. Usuwamy środkową kolumnę, bo jej nie potrzebujemy

PowerQuery 2 - Łączenie kolumn z danymi 09

6. Dzielimy kolumnę po użytym przez nas ograniczniku

PowerQuery 2 - Łączenie kolumn z danymi 10

7. Odfiltrowujemy po jednej z kolumn wynikowych wiersze puste lub z wartością null. Możemy tak teraz postąpić, gdyż PowerQuery zapisze to jako filtr wartości różne od pustych (null).

PowerQuery 2 - Łączenie kolumn z danymi 11

8. Na koniec zmieniamy nazwy kolumn i wczytujemy dane do Excela.

PowerQuery 2 - Łączenie kolumn z danymi 12

Trzeba zwrócić uwagę, że wartości w pierwszej kolumnie nie są takie jakbyśmy chcieli ponieważ w pierwszym kroku skopiowaliśmy nagłówki bezpośrednio skopiowane z danych, które przekształcamy, a one nie przechowują dodatkowej informacji o produktach. W tym przykładzie powinniśmy zamiast nich wstawić jako nagłówki dane naszych sprzedawców, a następnie odświeżyć tabelę wynikową zapytania PowerQuery.

PowerQuery 2 - Łączenie kolumn z danymi 13

Niestety spowoduje to błąd :(, ale klikając dwukrotnie w oknie zapytań wrócimy do edycji zapytania i będziemy mogli je poprawić. Przy okazji PowerQuery podpowiada nam, gdzie wystąpił błąd.

PowerQuery 2 - Łączenie kolumn z danymi 14

Błędny okazuje się drugi krok ponieważ w pobieranych danych nie istnieje już kolumna Produkt itp. Najprostszym rozwiązaniem jest tutaj usunięcie tego kroku ponieważ mamy proste dane i jego usunięcie nie wpłynie negatywnie na wynik końcowy.

PowerQuery 2 - Łączenie kolumn z danymi 15

Teraz wystarczy ponownie załadować dane zapytania i w naszej tabeli wynikowej zapytania PowerQuery mamy wszystkie istotne dla nas dane.

PowerQuery 2 - Łączenie kolumn z danymi 16

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

Excel – Zamiana „angielskich” liczb na „polskie” za pomocą Tekst jako kolumny – Porada #292

W tym tygodniu na szkoleniu, które prowadziłem, został poruszony również wątek zamiany angielskiego zapisu liczb na polski za pomocą polecenia Tekst jako kolumny.
Zacznijmy od tego, ze mamy liczby, gdzie separatorem tysięcy jest przecinek, a część całkowitą od ułamkowej liczby oddziela kropka czasem też się trawi minus na końcu liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 01

Liczby te możemy łatwo zamienić na polskie (czy też takiej jakie wynikają z Twoich ustawień regionalnych) za pomocą polecenia Tekst jako kolumny, które znajduje się na karcie Dane. Musimy tylko zaznaczyć kolumnę z liczbami, które chcemy zamienić.
Przez pierwsze dwa kroki przechodzimy szybko upewniając się tylko, że nie jest zaznaczony żaden ogranicznik, który spowodowałby podział liczby na osobne kolumny. Musimy się na chwilę zatrzymać w kroku 3 i kliknąć przycisk Zaawansowane.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 02

W oknie, które się otworzy musimy wybrać takie separatory jakie są w liczbach, które chcemy zmienić, a Excel zamieni je na takie, które wynikają z ustawień regionalnych. Możemy też zaznaczyć checkbox, że znak minus znajduje się na końcu liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 03

Po tym wystarczy zatwierdzić opcje i wkleić liczby tam, gdzie chcesz np.: w kolumnę obok, żeby było widać wcześniej „angielski” zapis i aktualny „polski” zapis liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 04

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

Sumowanie całego wiersza na podstawie pojedynczego kryterium – Porada #291

Dzisiaj chcemy zsumować wartości z całego wiersza na podstawie pojedynczego kryterium. Czyli przykładowo jeśli wybierzemy Tadeusza to chcemy zsumować 3 wartości (cały wiersz).

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 01

Problem jest taki, że SUMA.JEŻELI, z której najczęściej korzystamy, gdy chcemy zsumować wartości po warunku nie zadziała tu poprawnie:

=SUMA.JEŻELI(A3:A9;G1;B3:D9)

Wynika to ze sposobu działania funkcji SUMA.JEŻELI – na 1 komórkę, która spełnia warunek funkcja SUMA.JEŻELI zsumuje 1 komórkę z zakresu podanego do sumowania. Dlatego tutaj musimy skorzystać z innej formuły.
Odpowiednią możliwość daje nam SUMA.ILOCZYNÓW z odpowiednimi obliczeniami w argumencie.

Zaczniemy od tego, że potrzebujemy sprawdzić warunki, czyli prosta operacja porównania A3:A9=G1. Następnie tą wartość musimy (wciąż wewnątrz funkcji) przemnożyć przez cały zakres danych do zsumowania (A3:A9=G1)*B3:D9

Wtedy dla każdej wartości PRAWDA z operacji porównania zostanie przemnożony cały wiersz z drugiego zakresu – po ewaluacji (klawisz F9), będzie to taki wynik w argumencie funkcji

SUMA.ILOCZYNÓW: {0\0\0;193\148\276;0\0\0;0\0\0;0\0\0;0\0\0;0\0\0}

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 03

Funkcja SUMA.ILOCZYNÓW bez problemu sumuje wszystkie wartości z takiej tablicy i uzyskujemy poprawny wynik, czyli sumę wartości z każdego wiersza, dla którego zostanie spełnione kryterium w pierwszej kolumnie.

=SUMA.ILOCZYNÓW((A3:A9=G1)*B3:D9)

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 04

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

SUMA.JEŻELI po zakresie 2D – Porada #290

Mamy taką sytuację, że sprzedaż poszczególnych sprzedawców mamy w osobnych kolumnach i każdy sprzedawca sprzedaje różne produkty w różnej ilości.

Porada 290 - SUMA.JEŻELI po zakresie 2D 01

Chcemy mieć możliwość zsumowania ilości sprzedaży dowolnego wybranego produktu za pomocą jednej formuły. Można to zrobić za pomocą funkcji SUMA.JEŻELI – trzeba tylko wiedzieć dokładnie jak ona działa.

Zacznijmy od tego, że wybierzemy sobie produkt powiedzmy kokos. Dołożone formatowanie warunkowe zaznaczy odpowiednie pozycje.

Porada 290 - SUMA.JEŻELI po zakresie 2D 02

Łatwo zauważyć, że zawsze chcemy sumować wartość z komórki o jedną kolumnę na prawo od komórki z kokosem. Jak możemy coś takiego zrobić za pomocą funkcji SUMA.JEŻELI?

Zaczniemy od tego, że poniżej naszych danych napiszemy prostą operację porównania przyrównującą wartość komórki do wybranego produktu. Dodatkowo pomalujemy komórki obok komórek z prawdami, bo to z nich tak naprawdę chcemy wziąć wartości do sumowania.

=A3=$K$1

Porada 290 - SUMA.JEŻELI po zakresie 2D 03

Dzięki temu widzimy jaką macierz/tablicę zbuduje sobie funkcja SUMA.JEŻELI. To będą pojedyncze komórki z wartością logiczną PRAWDA tam, gdzie warunek został spełniony. W takim razie jak zsumować wartości, które są obok wartości PRAWDA? To proste – wystarczy, że odpowiednio przesuniemy zakres, po którym będziemy sumować w odniesieniu do zakresu sprawdzającego wartości.
Czyli nasza funkcja SUMA.JEŻELI powinna wglądać tak:

=SUMA.JEŻELI(A3:G9;K1;B3:H9)

Porada 290 - SUMA.JEŻELI po zakresie 2D 04

Czyli zakres, po którym sumujemy musimy być odpowiednio przesunięty w stosunku do zakresu, po którym sprawdzamy kryterium. W tym przykładzie jest to dokładnie 1 kolumna w prawo: A3:G9 -> B3:H9.

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

Tworzenie tabeli „kalendarza” za pomocą funkcji w Power BI

Czasami przy pracy z danymi wczytywanymi do Power BI przydaje nam się tabela z rozpisanymi na różne sposób datami, gdzie oprócz samej daty mamy jeszcze np.: kolumny, z numerem roku, miesiąca, dnia, kwartału lub połączonych tych informacji. Żeby nie musieć tworzyć takiej tabeli ręcznie różni programiści stworzyli kod funkcji w języku M, żeby zautomatyzować to zadanie.

Ja korzystam z kodu Matta Massona, ze strony (dowiedziałem się o nim od Jona Acampora):
https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Który odrobinę zmodyfikowałem (głównie zmieniłem nazwy kolumn), żeby bardziej odpowiadał polskim ustawieniom.

Jak skorzystać z kodu funkcji w PowerBI?

Żeby skorzystać z tej funkcji w PowerBI musisz stworzyć najpierw puste zapytanie (karta Narzędzia główne -> rozwiń polecenie Pobierz dane -> wybierz z listy Puste zapytanie)

PowerBI 01 - Tworzenie tabeli kalendarza za pomocą funkcji w Power BI 01

Następnie w edytorze zapytań wybierz polecenie Edytor zaawansowany z karty Narzędzia główne.

PowerBI 01 - Tworzenie tabeli kalendarza za pomocą funkcji w Power BI 02

W oknie edytora zaawansowanego wklejasz skopiowany z początku wpisu kod, a następnie zatwierdzasz go przyciskiem Gotowe.

PowerBI 01 - Tworzenie tabeli kalendarza za pomocą funkcji w Power BI 03

Teraz przy aktywnym tym zapytaniu zobaczysz okno wprowadzania parametrów funkcji.

  • DataPoczątkowa – to data, od której będzie zaczynał się stworzony kalendarz
  • DataKońcowa – to data, na której będzie kończył się stworzony kalendarz
  • Język – parametr opcjonalny, który decyduje w jakim języku są prezentowane dane np.: nazwy miesiąca, czy dni tygodnia. Jeśli nie wypełnisz tego pola, to będą brane pod uwagę ustawienia aplikacji PowerBI. Możesz zobaczyć szczegóły tego parametru na stronie:
    [MS-LCID]: Windows Language Code Identifier (LCID) Reference

Wystarczy, że wypełnisz to okno parametrami zgodnie ze swoimi potrzebami, a następnie zatwierdzisz wybór przyciskiem Wywołaj.

PowerBI 01 - Tworzenie tabeli kalendarza za pomocą funkcji w Power BI 04

Żeby edytor zapytań stworzył dla Ciebie całą tabelę danych. Pamiętaj jeszcze załadować ją do aplikacji PowerBI Desktop (polecenie Zamknij i zastosuj na karcie Narzędzia główne).

PowerBI 01 - Tworzenie tabeli kalendarza za pomocą funkcji w Power BI 05

Chcesz lepiej poznać PowerBI wejdź na stronę mojego kursu:
http://exceliadam.pl/kursy/power-bi

Pozdrawiam
Adam Kopeć
Miłośnik Excela