0
0 Produkty w koszyku

No products in the cart.

Jak pobrać wartość z komórki Excela do zapytania PowerQuery #10

Naszym zadaniem na dziś jest pobranie wartości z komórki Excela jako warunek do zapytania PowerQuery. Bezpośrednio nie jest to możliwe, ale można pobrać dane z tabeli, a później wyszczególnić komórkę, którą nam chodzi.
Mamy przygotowaną tabelę z prostymi warunkami – chcemy ograniczyć wyniki zapytania do tylko tych wskazanych w tabeli z warunkami. 

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 01

Mamy na to dwa podstawowe sposoby – albo będziemy pobierać wynik jednego zapytania do drugiego zapytania, albo dołożyć odpowiedni kod M do głównego zapytania.

Zaczniemy od prostszego sposobu odwoływania się do wyników zapytań. W pierwszej kolejności pobierzemy dane z tabeli warunków (standardowo za pomocą polecenia PowerQuery z tabeli).

Nie będzie nam potrzebny krok Zmieniania typów (domyślnie dodawany przez PowerQuery), więc go usuwamy. Następnie będziemy potrzebować zduplikować zapytanie, czyli rozwijamy listę zapytań z lewej strony edytora zapytań i klikamy prawym przyciskiem na zapytanie, a następnie wybieramy opcję Duplikuj z listy rozwijanej (możemy jeszcze ewentualnie zmienić nazwę zapytania na takie, które się nam bardziej podoba 😉 ).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 02

Następnie potrzebujemy wyszczególnić interesujące nas warunki, czyli klikamy prawym przyciskiem myszy na komórkę, która nas interesuje i wybieramy odpowiednią opcję z podręcznego menu.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 03

Dla obu zapytań wykonujmy analogiczne przejście do szczegółów i możemy po nim zauważyć, że nasze zapytania przestają mieć formę tabeli – zmieniają się na wartość.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 04

Przy tym kroku ważne jest to, że PowerQuery ma indeksowanie (liczenie wierszy) od zera (pamiętaj o zaznaczeniu checkboxa Pasek formuły na karcie Widok). Przykładowo (rys. 4) wyszczegółowiliśmy wartość Ciasteczka, która znajdują się w pierwszym wierszu ({0}) kolumny Produktu kroku Źródło – zostało to zapisane za pomocą formuły.

Potrzebujemy nasze zapytania załadować nasze zapytania tylko do pamięci PowerQUery, więc rozwijamy polecenie Załaduj do i wybieramy odpowiednie opcje.

Teraz pobieramy do nowego zapytania dane z tabeli, którą będziemy chcieli filtrować. Potrzebujemy poprawić domyślny krok Zmieniono typ, ponieważ PowerQuery, źle rozpoznał typ danych dla kolumny Data (wybrał Data i czas, zamiast samej daty). Najprościej kliknąć ikonkę w nagłówku kolumny lewym przyciskiem myszy i wybrać odpowiedni typ z podręcznego menu.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 05

Teraz filtrujemy kolumnę Sprzedawca po dowolnym sprzedawcy (np.: po Janie).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 06

W stworzonej formule (patrz pasek formuły dla kroku filtrowania):

= Table.SelectRows(#"Zmieniono typ", each ([Sprzedawca] = "Jan"))

Potrzebujemy zmienić wartość wpisaną na stałe ("Jan") na odwołanie do odpowiedniego zapytania – wystarczy wstawić w formule jego nazwę (pamiętając o tym, że PowerQuery zwraca uwagę na wielkość liter):

= Table.SelectRows(#"Zmieniono typ", each ([Sprzedawca] = tWarunki))

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 07

Analogicznie robimy dla drugiej kolumny – filtrujemy po dowolnym produkcie, a później podmieniamy wartość w formule na odwołanie do wyniku zapytania.

Potem możemy załadować zapytanie do arkusza Excela i zobaczyć, że jego wynik zmienia się w zależności od wartości w tabeli warunków.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 08

Chcemy jeszcze omówić rozwiązanie, które bardziej modyfikuje język M (w pierwszym modyfikowaliśmy tylko pojedyncze formuły). Ponieważ nie zawsze chcemy generować tyle zapytań przechowujących warunku/parametry, które wykorzystujemy.

Musimy wrócić do edytora zapytań (np.: edytując załadowane przez nas zapytanie).Będziemy je chcieli zduplikować (rys. 2).
Teraz potrzebujemy zobaczyć cały kod języka M dla jednego z zapytań pobierających warunki. Zaznaczamy je i z karty Narzędzia główne wybieramy Edytor zaawansowany.

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 09

Zobaczymy tam kod wszystkich kroków zapytania. Standardowo zapytanie rozpoczyna słowo kluczowe let. Każda linijka zakończona przecinkiem, to krok zapytania. Tylko ostatni krok nie jest zakończony przecinkiem. Po nim pojawia się słowo kluczowe in i nazwa kroku, które zwraca zapytanie. Domyślnie kolejny krok odwołuje się do wyniku wcześniejszego kroku przez jego nazwę.

let
Źródło = Excel.CurrentWorkbook(){[Name="tWarunki"]}[Content],
Sprzedawca = Źródło{0}[Sprzedawca]
in
Sprzedawca

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 10

Potrzebujemy stąd (rys. 10) skopiować kod 2 kroków. Następnie przechodzimy do edytora zaawansowanego nowego zapytania filtrującego dane. Wklejamy te dwie linijki zaraz po słowie kluczowym let, a dodatkowo przed i po dodajemy po dwa slashe (//), żeby wyróżnić ten fragment kodu (służą one jako znaczniki komentarza).


let
//
Źródło = Excel.CurrentWorkbook(){[Name="tWarunki"]}[Content],
Sprzedawca = Źródło{0}[Sprzedawca]
//
Źródło = Excel.CurrentWorkbook(){[Name="tProdukty"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Data", type date}, {"Sprzedawca", type text}, {"Produkt", type text}}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each ([Sprzedawca] = tWarunki)),
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Przefiltrowano wiersze", each ([Produkt] = tWarunki2))
in
#"Przefiltrowano wiersze1"

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 11

PowerQuery od razu nas informuje, że nasze zapytanie jest błędne. Po pierwsze brakuje przecinka na końcu 2 kroku, ale poważniejszym błędem jest to, że dwa kroki mają taką samą nazwę (Źródło). Załóżmy, że nazwę pierwszego kroku zmienimy na ‘Warunki’, co spowoduje, że będziemy musieli skorygować drugi krok, żeby odwoływał się do wyniku pierwszego kroku, a nie 3. Zmienimy też nazwę 2 kroku ze ‘Sprzedawca’ (czyli pierwszy wyraz w drugim kroku. Zapis ‘[Sprzedawca]’ to odwołanie do nazwy kolumny) na ‘Warunek1’. Musimy jeszcze zmienić krok #"Przefiltrowano wiersze" (jego nazwa jest w podwójnych cudzysłowach (") i poprzedzona hashem (#) ponieważ zawiera nietypowy znak – spację), żeby odwoływał się do kroku ‘Warunek1’, a nie do wyniku zapytania tWarunki (wystarczy zamienić nazwę). 

Uff. Jeśli pierwszy raz modyfikuje język M w edytorze zaawansowanym, to była ciężka przeprawa, ale Twój kod powinien teraz wyglądać tak:

let
//
Warunki = Excel.CurrentWorkbook(){[Name="tWarunki"]}[Content],
Warunek1 = Źródło{0}[Sprzedawca],
//
Źródło = Excel.CurrentWorkbook(){[Name="tProdukty"]}[Content],
#"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Data", type date}, {"Sprzedawca", type text}, {"Produkt", type text}}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono typ", each ([Sprzedawca] = Warunek1)),
#"Przefiltrowano wiersze1" = Table.SelectRows(#"Przefiltrowano wiersze", each ([Produkt] = tWarunki2))
in
#"Przefiltrowano wiersze1"

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 12

Wpisany kod po zatwierdzeniu przyciskiem gotowe powinien wygenerować odpowiednie kroki widoczne w edytorze zapytań (mylące jest dla nas to, że 2 krok wyświetla się jako ‘Nawigacja’, a nie ‘Warunek1’, ponieważ właśnie nazwy ‘Warunek1’ musimy używać, żeby prawidłowo pobrać parametr dla filtru).

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 13

Ważne –napisany przez nas kod (zapytanie) będzie działać bez zapytania z pierwszym warunkiem. Żeby to sprawdzić potrzebujemy je skasować, czyli kliknąć prawym przyciskiem myszy i wybrać odpowiednie polecenie z podręcznego menu. PowerQuery nie powinien Ci pozwolić na usunięcie tego zapytanie, ponieważ w innym zapytaniu (tProdukty – tym od filtrowania, które zduplikowaliśmy, przed modyfikowaniem języka M). 

PQ 10 - Jak pobierać wartość z Excela do PowerQuery 14

Musimy je najpierw usunąć, a dopiero potem usunąć zapytanie pobierające warunek. Przy okazji możemy zobaczyć, że zarówno zapytanie odwołujące się do innych zapytań jak i to, któremu zmodyfikowaliśmy język M dają ten sam wynik. To, z którego sposobu skorzystasz zależy od Ciebie. Ogólnie jeśli nie pobierasz dużo warunków i masz mało zapytań w PowerQuery wygodniejsze jest odwoływanie się do wyników zapytań, niż modyfikacja języka M w edytorze zaawansowanym.

Jeśli chcesz możesz wczytać zapytanie do Excela (ewentualnie zmodyfikować kod M, pod 2 warunek filtrowania).
Ponieważ usunęliśmy wcześniej załadowane zapytanie, przestanie się ono aktualizować, gdy będziemy odświeżać wszystkie zapytanie, ponieważ stało się teraz zwykłą tabelą (pozostałością, po ostatnim odświeżeniu zapytania)

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

Wyłączanie automatycznej zmiany typu danych i inne opcje PowerQuery #9

Czasami domyślne ustawienie programu lub dodatku utrudniają pracę, dlatego czasami chcemy je zmienić. Wspominałem o tym w PQ8, że nie chce, żeby PowerQuery automatycznie wykonywał mi krok zmiany typów parametrów, dlatego w tym wpisie zmienimy m.in. tą opcję PowerQuery.

W Excelu 2016 musimy na karcie Dane rozwinąć polecenie Nowe zapytanie i wybrać Opcje dodatku Query.

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 01

We wcześniejszych wersjach Excela to polecenie odnajdziemy na karcie PowerQuery.
PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 02

Przede wszystkim możemy zauważyć, że mamy podział opcji zapytań na Globalne i dla Bieżącego skoroszytu. Omówimy najistotniejsze dla nas opcje. Zaczynamy od opcji Ładowania danych.

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 03

Tutaj możemy zdecydować jak domyślnie będą ładowane zapytania PowerQuery – ustawienia standardowe oznaczają, że każde nowe zapytanie zostanie załadowane do nowego arkusza. 

Jednak w mojej pracy często okazuje się to niechcianą opcją (m.in. ze względu na dużą ilość danych). Dlatego korzystam z niestandardowych ustawień, gdzie mam odznaczone ładowanie do arkusza oraz modelu danych (dopiero zaczynam naukę PowerPivot 😉 ). Dzięki temu zapytania ładują się tylko jako połączenie i są przechowywane w pamięci PowerQuery. Nawet jeśli się pomylę i konkretne zapytanie jednak chciałem załadować do Excela, to łatwiej mi je załadować w odpowiednie miejsce, niż gdybym musiał usuwać niechciane ładowanie i czekać na wpisanie wszystkich danych do Excela.

Jest tutaj (rys. 3) też checkbox, Szybkie ładowanie danych. Jeśli pracujesz na większych zestawach danych i nie zależy Ci na responsywności Excela możesz śmiało go zaznaczyć – przyspieszy to trochę ładowanie zapytań do Excela.
Kolejna istotna zakładka to Edytor zapytań.

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 04

Są tutaj 2 ważne checkboxy:

      - Wyświetl okienko ustawienia zapytania (to z prawej strony edytora z krokami)
      - Wyświetl pasek formuły

Oba te ustawienia można zmienić również na karcie Widok edytora zapytań (PowerQuery). Dla mnie ich zaznaczenie jest obowiązkowe 😉

Kolejną zakładką, którą omówimy jest Prywatność. Zarówno dla ustawień globalnych

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 05

jak i dla bieżącego skoroszytu.

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 06

Ponieważ na zakładce bieżącego skoroszytu jest napisane, że wyłączenie poziomów prywatności może poprawić wydajność wybór dla mnie jest oczywisty – ignoruję je 😀

Jak do tej pory nigdy mi nie były potrzebne i przez to przeszkadzały w pracy, dlatego tym bardziej je ignoruję.
Kolejna ważna dla mnie opcja kryje się na zakładce Ładowanie danych dla bieżącego skoroszytu. 

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 07

Mowa o wspomnianej na początku opcji automatycznego typu kolumn dla źródeł i automatycznego wykrywania nagłówków. Mi często domyślne ustawianie typów danych dla kolumn przeszkadza (obcina wartości dużych liczb jak numery kont bankowych, usuwa wiodące zera itp.), dlatego w większości sytuacji odznaczam tą opcję. Poza tym krok zmiany typu danych dla kolumny jest jednym z bardziej obciążających procesor, dlatego chce mieć kontrolę co i kiedy zmieniam, bo zdarzają mi się często zapytania, gdzie kolejny krok po domyślnej zmiana typów danych dla kolumn zmienia całą strukturę danych i krok zamiany typów danych tylko zajmuje miejsce.

Niestety nie wiem, gdzie mogę ją zmienić globalnie i dla każdego pliku muszę ustawiać ją osobno :/

Jeszcze jedna opcja, która może Ci się przydać to ustawienia regionalne dla skoroszytu.

PQ 9 - Wyłączanie zmiany typu danych i inne opcje PowerQuery 08

Czasami, mimo, że jesteś Polakiem i pracujesz w Polsce 😉 Robisz raport dla np.: firmy niemieckiej i chcesz, żeby miesiące wyodrębniały się z daty w tym języku. Wystarczy, zmienić tutaj język (kulturę), a później wybrać odpowiednie polecenia w PowerQuery. Ustawienie regionalne ważną są też przy zapisie liczby. Przede wszystkim chodzi o to jaki znak oddziela część całkowitą od dziesiętnej (np.: czy to jest przecinek – w Polsce, czy kropka – w stanach zjednoczonych).

To moim zdaniem najważniejsze opcje dodatku PowerQuery, które warto czasem zmienić dla swojej wygody i przyspieszenia działania pracy.

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

Jak policzyć komórki zawierające konkretny tekst — Grupowanie danych — PowerQuery #8

Potrzebujemy policzyć komórki, które zawierają konkretne słowa/frazy. Utrudnieniem jest to, że musimy osobno policzyć, które mają wspólną część (np.: Ciasteczka i Ciasteczka1). Posłużymy się prostym przykładem, gdzie mamy ‘paczki’, do których wkładamy produkty.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 01

To trudne zadanie, ponieważ przykładowa formuła, której używaliśmy w poradzie Excel — Jak policzyć komórki zawierające konkretny tekst — Widzowie #89 (pod nią zostało właśnie zadane pytania o liczenie podobnych fraz) będzie zliczać więcej produktów niż potrzeba (Ciasteczka będą zliczały również Ciasteczka1).

=LICZ.WARUNKI($C$2:$C$25;"*"&F3&"*";$B$2:$B$25;E3)

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 02

Możemy sobie z tym poradzić ponieważ, poszczególne produkty, są oddzielone od siebie ogranicznikami (przecinkiem i spacją). Dzięki temu moglibyśmy w Excelu podzielić kolumnę za pomocą Tekst jako kolumny na poszczególne produkty i później je zliczać, ale to kłopotliwy i jednorazowy proces. Dlatego my wykorzystamy do tego PowerQuery.

Pobieramy nasze dane z tabeli do PowerQuery (z karty dane od Excel 2016 lub z karty PowerQuery od Excela 2010).

Nie jest nam potrzebny krok zmieniania typu danych, więc go usuwamy. Również nie potrzebujemy kolumny z datą, ale ją przy okazji usunie późniejszy krok. 

Jeśli nie szukalibyśmy podobnych słów, to moglibyśmy przefiltrować dane i wybrać tylko te wiersze, które je zawierają.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 03

My jednak chcemy na początek wziąć wszystkie produkty, żebyśmy je mogli policzyć. Dopiero później chcemy skupić się na tych podobnych (Ciasteczka i Ciasteczka1). Wracamy do tego, że nasze produkty w kolumnie są rozdzielane kombinacją znaków (przecinek i spacja). Ważne, że chcemy podzielić produkty w ‘paczce’ na wiersze, a nie kolumny. Wystarczy, że wybierzemy kolumnę Paczka i polecenie Podziel kolumny z karty Narzędzia główne.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 04

A następnie przejdziemy do opcji zaawansowanych, gdzie możemy zmienić standardowy podział po ogranicznikach na kolumny na wiersze. Musimy również ustawić nasz niestandardowy ogranicznik.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 05

Wyniki ograniczymy sobie (przefiltrujemy) tylko do produktów Ciasteczka i Ciasteczka1.

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 06

Teraz możemy je policzyć – wystarczy, że pogrupujemy dane (polecenie Grupowanie według na karcie Narzędzia główne.
PQ 5 - Jak policzyć komórki zawierające konkretny tekst 07

Chcemy grupować zarówno po kolumnie Sprzedawca jak i Paczka, więc w zależności od Twojej wersji PowerQuery możliwe, że będziesz musiał zaznaczyć opcje zaawansowane zanim będziesz musiał dodać kolejne kolumny do grupowania. Ważne, że operację jaką chcemy wykonać przy grupowaniu to Zliczanie wierszy. (Ten krok, również usunął kolumny, które nie biorą udziału w operacji grupowania – w tym przykładzie to kolumna Data).

PQ 5 - Jak policzyć komórki zawierające konkretny tekst 08

To jest właśnie nasz wynik (ograniczony do 2 produktów). Wystarczy, go załadować do Excela.

PQ 8 - Jak policzyć komórki zawierające konkretny tekst 09

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

Wiersze z wieloma rekordami do prawidłowego zestawu danych — scalanie 1 zapytania — PowerQuery #7

Kontynuujemy temat przekształcania danych, na łatwiejsze do analizy, z poprzednich wpisów (PQ 5 i PQ 6) 

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania  010202

Rozwiązanie w tym wpisie uwzględnia uwagi użytkownika The Power User (film https://www.youtube.com/watch?v=SOXvcBeXeyA) w komentarzu pod filmem (https://www.youtube.com/watch?v=jr2F-mrE1Uc) na podstawie, którego powstała seria 3 wpisów (PQ 5, PQ 6 i PQ 7). To rozwiązanie jest najbardziej skomplikowane, ale pozwala Ci się na wyższy poziom znajomości PowerQuery i języka M 😉

Standardowo nasze dane przechowywane są jako tabela, więc zaczytujemy je za pomocą polecenia Z tabeli (z karty dane od Excel 2016 lub z karty PowerQuery od Excela 2010).

W naszym zapytaniu nie potrzebujemy domyślnego kroku Zmieniania typów, więc możemy go usunąć klikając na czerwony x obok niego. 

W pierwszym kroku będziemy potrzebowali dodać kolumnę (polecenie Kolumna niestandardowa z karty Dodaj kolumnę) łączącą wartości z dwóch pierwszych kolumn (Województwo i Miasto), czyli kolumn wspólnych dla poszczególnych zestawów danych (rekordów).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 03

Formuła jest analogiczna jak w Excelu (nie jest istotna dla nas nazwa kolumny, która powstanie) i wykorzystuje symbol ampersand '&'. 

=[Województwo]&";"&[Miasto]

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 04

Jak mamy połączone wartości z 2 pierwszych kolumn to ich nie potrzebujemy, więc je kasujemy, a następnie przeciągamy stworzoną przez nas kolumnę z formułę na początek danych.

Po tych krokach wykonujemy transponowania danych (polecenie z karty Przekształć), czyli zamieniamy miejscami wiersze z kolumnami.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 05

W wyniku nasza pierwsza kolumna stanie się pierwszym wierszem i przechowuje całkiem inny zestaw danych. Dlatego potrzebujemy ją ochronić – najprostszym na to sposobem jest przeniesieniem ją do nagłówków (polecenie Użyj pierwszego wiersza jako nagłówków z karty Przekształć).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 06

Nasze zestawy danych mają po dwa wiersze (wcześniej kolumny) i potrzebujemy je zidentyfikować, a następnie połączyć. Zaczniemy od tego, że dodamy kolumnę z indeksem od zera (polecenie z karty Dodaj kolumnę).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 07

Tylko my nie potrzebujemy numeracji wierszy od początku danych, ale numeracji wierszy danych w grupach, czyli pierwszy wiersz będzie miał numer 0, drugi 1, trzeci 0 itd. Wykorzystamy do tego funkcję Modulo (po 2), którą znajdziemy na karcie Dodaj kolumnę, gdy będzie zaznaczona kolumna z indeksem.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 08

Wartość dla Modulo wpisujemy jako 2 i uzyskujmy numerowanie wierszy jakiego potrzebujemy, ale potrzebujemy jeszcze kolumny, która określi pogrupuje nam grupy danych – chodzi o to, żeby wiersze z tej samej grupy danych miały taki sam numer (nie koniecznie w kolejności).

My zrobimy to za pomocą Kolumny warunkowej (karta Dodaj kolumnę).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 09

Nazwijmy ją sobie L.p. i chcemy ją uzależnić od kolumny Wstawiono modulo. Jeśli wartość w tej kolumnie będzie równa zero to chcemy zwracać wartość z kolumny Indeks, jeśli wartość modulo będzie inna (1), to wtedy chcemy Indeks pomniejszony o jeden. Nie możemy dla kolumny warunkowej przypisać od razu wartości indeks minus jeden, ale możemy wybrać wartość z kolumny Indeks…

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 10

, a później odjąć jedynkę w stworzonej formule. Musi być zaznaczone pole wyboru (checbox) Pasek formuły na karcie Widok. Wtedy wystarczy w nim dopisać '-1' na koniec formuły przez zamykającym nawiasem.

= Table.AddColumn(#"Wstawiono modulo", "L.p.", each if [Wstawiono modulo] = 0 then [Indeks] else [Indeks]-1)

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 11

Nie będzie już nam potrzebna kolumna Indeks więc możemy ją usunąć.

Zaznaczamy teraz kolumny Modulo i L.p. i anulujemy przestawienie innych kolumn (np. dzięki poleceniu z podręcznego menu po kliknięciu prawym przyciskiem na zaznaczone kolumny).

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 12

Teraz czeka nas trudniejsze zadania, bo w dwóch kolejnych krokach potrzebujemy mieć dwa różne filtry na kolumnę Wstawiono modulo. W pierwszym chcemy widzieć zera, a w drugim jedynki. 

Stworzenie pierwszego z tych kroków jest banalne, bo wystarczy przefiltrować odpowiednio dane po kolumnie Wstawiono modulo. To stworzenie kolejnego kroku sprawia trudność. 

Mamy już widoczny pasek formuły, więc kopiujemy formułę dla pierwszego filtrowania.

= Table.SelectRows(#"Anulowano przestawienie innych kolumn", each ([Wstawiono modulo] = 0))

Widzimy dokładnie ile ma się równać wartość w kolumnie modulo. Potrzebujemy niemal identycznej formuły tylko musimy zamienić 0 na 1.

Zaczniemy od tego, że ustawiamy się na ostatnim kroku zapytania (filtrze po zerze) i klikamy w symbol fx znajdujący się obok paska z formułą.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 13

Gdy doda się nowy krok do paska formuły wklejamy skopiowaną formułę na przefiltrowanie wierszy tylko zamieniamy 0 na 1.
Mamy teraz 2 kroki z różnymi wynikami filtrowania. Potrzebujemy im zmienić nazwę, żeby łatwiej je później zidentyfikować. Klikamy na nazwę kroku, a następnie naciskamy klawisz F2, żebyśmy mogli zmienić nazwy na np. DataSet1 i DataSet2.

Uff. Ten krok zdecydowanie wykracza ponad rozwiązania, które stosujemy na co dzień. 

Teraz potrzebujemy scalić zapytania (polecenie na karcie Narzędzia główne).

Będziemy chcieli połączyć nasze z zapytanie z naszym zapytanie (bieżącym zapytaniem). Dokładnie tak dobrze przeczytałeś, chcemy scalić ze sobą to samo zapytanie. Trzymając klawisz Ctrl zaznaczamy u góry i u dołu kolumny L.p. i Atrybut (przechowuje wartości z nagłówka kolumn, te które wcześniej łączyliśmy). Jako rodzaj sprzężenia wybieramy wewnętrzne i klikamy przycisk OK.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 14

Do zapytania doda się Nowa kolumna z wartościami typu Table. Żeby zobaczyć co się pod nimi kryje wystarczy kliknąć na dowolną komórkę i na dole edytora zobaczymy wartości (tabelę) kryjącą się w zaznaczonej komórce. 

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 15

W tych komórkach kryją się identyczne wiersze tabeli zapytania. To dlatego, że jeszcze nie zmodyfikowaliśmy formuły kroku scalania. Teraz PowerQuery łączy ostatni krok zapytania (DataSet2) z ostatnim krokiem zapytania.

= Table.NestedJoin(DataSet2,{"L.p.", "Atrybut"},DataSet2,{"L.p.", "Atrybut"},"Nowa kolumna",JoinKind.Inner)

Musimy zmodyfikować formułę, żeby jedno z zapytań odwoływało się do wcześniejszego kroku (DataSet1), czyli wystarcz, że zmienimy jedną cyfrę 😉 

Załóżmy, że zmiany dokonamy w pierwszy odwołaniu, bo najpierw chcemy mieć wiersz z nazwą sklepu, a później ulicą na której jest.

= Table.NestedJoin(DataSet1,{"L.p.", "Atrybut"},DataSet2,{"L.p.", "Atrybut"},"Nowa kolumna",JoinKind.Inner)

Po zmianie kodu formuły możemy rozwinąć (nie agregować) kolumnę Nowa kolumna klikając na strzałki wskazujące w przeciwnym kierunku (patrz nagłówek kolumny). Potrzebujemy tylko kolumny Wartość i nie chcemy, żeby nadana jej nazwa używała prefiksu kolumny, z której ją rozwijamy.

PQ 7 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - scalanie 1 zapytania 16

Jesteśmy już prawie na końcu drogi 😀 Wystarczy, że usuniemy niepotrzebne już kolumny Wstawiono modulo i L.p.
Następnie musimy podzielić kolumnę Atrybut (tą z nagłówków danych, która przechowuje informacje o Województwie i Mieście) po ograniczniku (którym jest średnik) na kolumny (polecenie z karty Narzędzia główne).

Na koniec zostanie tylko zmiana nagłówków kolumn i możemy załadować nasze zapytanie do Excela. Jak z poprzednimi zapytaniami z serii nie ma problemu gdy dodajemy nowe wiersze danych do oryginalnej tabeli. Jest problemy, gdy dodamy nowe kolumny.
Żeby poprawić zapytanie musimy zmodyfikować 4 krok zapytania, gdzie zmieniamy kolejność kolumn (kolumnę z połączonym Województwem i Miastem przesuwamy na początek danych). Kolumny układają się w nieprawidłowej kolejności i musimy poprawić ten krok.

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

Wiersze z wieloma rekordami do prawidłowego zestawu danych — podział po ograniczniku na wiersze — PowerQuery #6

Kontynuujemy temat przekształcania danych, na łatwiejsze do analizy, z poprzedniego wpisu (PQ 5) 

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 0102

Rozwiązanie w tym wpisie uwzględnia funkcjonalność PowerQuery, która nie była jeszcze dostępna, gdy Mike Girvin tworzył film (https://www.youtube.com/watch?v=jr2F-mrE1Uc) na podstawie, którego powstała seria 3 wpisów (PQ 5, PQ 6 i PQ 7). Chodzi o podział po ograniczniku na wiersze, a nie kolumny.

Jeśli masz najnowszą wersję PowerQuery na pewno masz dostępne to polecenie.

Standardowo nasze dane przechowywane są jako tabela, więc zaczytujemy je za pomocą polecenia Z tabeli (z karty dane od Excel 2016 lub z karty PowerQuery od Excela 2010).

W naszym zapytaniu nie potrzebujemy domyślnego kroku Zmieniania typów, więc możemy go usunąć klikając na czerwony x obok niego. 

Teraz potrzebujemy dodać Kolumnę niestandardową z karty Dodaj kolumnę.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 03

Nazwa tej kolumny jest dla nas drugorzędna, więc może zostać domyślna. Najważniejsza jest sama formuła – będzie to po prostu odpowiednie łączenie danych z kolumn. Formuła będzie przypominać analogiczną w Excelu (moglibyśmy ją nawet zrobić w Excelu i dopiero zaczytać do PowerQuery, ale ma to być tylko formuła/kolumna pomocnicza, więc tworzymy ją w PowerQuery). 

Chodzi o to, żeby pomiędzy kolumnami, które mają znaleźć się w tym samym wierszu wstawić jeden ogranicznik (np. średnik ; ), a pomiędzy kolumnami, które mają znaleźć się już w nowym wierszu inny ogranicznik (np. znak małpy @). 

=[Sklep1]&";"&[Ulica1]&"@"&[Sklep2]&";"&[Ulica2]&"@"&[Sklep3]&";"&[Ulica3]

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 04

Niestety po zatwierdzeniu formuły okazuje się, że nie daje poprawnych wyników w wierszach, gdzie pojawia się wartość null.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 05

Najprościej dla nas będzie naprawić formułę zamieniając wartości null na pusty ciąg tekstowy, pustą wartość. Żeby to zrobić musimy zaznaczyć pierwszy krok naszego zapytania (Źródło), następnie zaznaczyć kolumny, gdzie chcemy zmienić wartości (powinna wystarczyć zmiana od kolumny Sklep2 do końca). 

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 06

W oknie Zamieniania wartości w pierwsze pole wpisujemy wartość null, a drugie pozostawiamy puste.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 07

Doda się nowy krok przed stworzoną przez nas formułą i tym razem zadziała ona tak jak chcemy.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 08

Kolejnym krokiem będzie usunięcie zbędnych już kolumn z danymi sklepu (w końcu mamy je stworzonej przez nas kolumnie). 

Wystarczy je zaznaczyć, kliknąć w dowolny z nagłówków prawym przyciskiem myszy i wybrać z podręcznego menu usuń kolumny.
Teraz wystarczy zaznaczyć kolumnę pomocniczą (Niestandardową) i wybrać polecenie Podziel kolumny z karty Narzędzia główne.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 09

W opcjach podziału wybieramy niestandardowy ogranicznik i wpisujemy ustaloną przez nas wcześniej małpę '@' (lub inny znak, którego użyliśmy). Rozwijamy również opcje zaawansowane i zaznaczamy, że podziału chcemy dokonać na wiersze, a nie na kolumny.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 10

Rozdzieliliśmy połączone przez nas dane na wiersze…

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 11

… a potrzebujemy jeszcze podzielić je na kolumny, więc wykorzystujemy jeszcze raz polecenie Podziału kolumny po ograniczniku, tylko tym razem po średniku i dzielimy na kolumny.

Dopiero przy tym kroku może nam się przydać zmiana typów w danych w kolumnach, więc jeśli dodał się automatycznie możemy go zostawić.

Pozostaje tylko odfiltrować puste komórki i zmienić nazwy kolumn danych i możemy wczytać dane do Excela.

PQ 6 - Wiersze z wieloma rekordami do prawidłowego zestawu danych - podział ogranicznikiem 12

Analogicznie jak we poprzednim wpisie ), nie ma problemu gdy dodamy kolejne wiersze do oryginalnej tabeli (wystarczy odświeżyć zapytanie). Problem pojawia się gdy dodamy nowe kolumny. Tutaj poprawka jest łatwiejsza niż we wcześniejszym wpisie, gdyż wystarczy te nowe kolumny uwzględnić w napisanej przez nas formule (np. klikając na koło zębate obok kroku dodawania kolumn formuły).

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