Dodatek Power Query posiada mnóstwo przydatnych funkcji, które oszczędzają czas, automatyzują pracę i zmieniają procesy w firmach. Należy jednak pamiętać, że dodatek ten zawiera też pułapki, na które należy uważać. Tych kilka najważniejszych pułapek opisałem w tym bardzo praktycznym artykule.
Pułapka nr 1 – niewidoczny pasek formuły
Widoczny pasek formuły jest niezwykle pomocny, żeby zrozumieć składnię formuły i jej słabe punkty. Pasek formuły daje też możliwość szybkiej modyfikacji zapytania tak, aby było efektywne. Aby wyświetlić pasek formuły wystarczy otworzyć Edytor Zapytania i zaznaczyć Pasek Formuły w Widoku – teraz pasek będzie zawsze widoczny.
Można to także zrobić poprzez Plik, następnie wybrać Opcje i ustawienia, potem Opcje zapytania, kolejnym krokiem będzie wybór Edytora zapytań i zaznaczenia Wyświetl pasek formuły.
Pułapka nr 2 – Automatyczne kroki
Przy każdorazowym imporcie tabeli lub np. podziale kolumn, automatycznie generowane zmiany określą typ kolumn bez naszej ingerencji. Kroki te jednak zawierają formułę, która odnosi się do wszystkich kolumn w tabeli. Kiedy jedna z kolumn zostanie usunięta lub zostanie zmieniona jej nazwa w danych źródłowych przy odświeżaniu napotkamy na błąd. Aby przejrzeć formuły odnoszące się do poszczególnych zmian wystarczy kliknąć na nazwę zmiany po prawej stronie, aby wyświetliła się dana formuła.
Pułapka nr 3 – Filtry uwzględniające lub wykluczające
Po zastosowaniu filtrów w Edytorze zapytań należy sprawdzić pasek formuły i upewnić się, że filtr został wykonany właściwie – szczególnie przy filtrze wykluczającym, który zachowuje się czasem jak filtr uwzględniający tylko wartości, które chcemy wykluczyć.
Jako przykład zastosujmy filtr, w którym będziemy chcieli wykluczyć wartość 155. Zacznijmy wpisywać tę wartość, następnie odznaczmy liczbę.
Filtr wykluczył niestety wszystkie wartości oprócz 15 i 151- jest to widoczne w danych i w formule.
Aby filtr działał poprawnie należy zmienić formułę jak poniżej:
Teraz dane wykluczają tylko wartość 155.
Pułapka nr 4 – Zmiana kolejności kolumn
Zmiana kolejności kolumn w edytorze tworzy wzór, który odnosi się do wszystkich kolumn w tabeli. Jeżeli brakuje jednej z kolumn, odświeżenie danych się nie powiedzie. Aby uniknąć tej pułapki i zmienić kolejność kolumn, możemy wykorzystać zaawansowaną funkcję zapytania.
Pułapka nr 5 – Usuwanie i wybieranie kolumn
Kiedy usuwamy niepotrzebne kolumny za pomocą Edytora zapytań i chcemy uniknąć błędów odświeżania, które wynikają z braku konkretnych nazw kolumn, możemy zmienić formułę, żeby odwoływała się do pozycji kolumny zamiast jej nazwy.
Zamiast używania nazw kolumn w formule jak np. poniżej:
=Table.SelectColumns(Source,{“Column1”, “Column2”})
Możemy zmienić formułę na poniższą, która zamiast do nazw odnosi się do pozycji:
=Table.SelectColumns(Source,List.FirstN(Table.ColumnNames(Source), 2))
Pułapka nr 6 – Zmiana nazw kolumn
Po imporcie danych do Edytora zapytań może się okazać, że kilka nazw kolumn wymaga zmiany, jednakże zmiana nazw kolumn w Edytorze spowoduje błąd odświeżenia danych. Dodatkowo jeżeli zmienią się nazwy kolumn w danych źródłowych, również będzie problem z odświeżeniem danych.
Pułapka nr 7 – Podział kolumn według ogranicznika
Przy podziale kolumn za pomocą ogranicznika Edytor zapytań tworzy nowe kolumny, aby uwzględnić wydzielone wartości.
Edytor zapytań podpowiada według jakiego ogranicznika dzielić dane i na ile kolumn (oczywiście można to zmieniać w poniższym oknie).
Wybrany podział będzie zastosowany do wszystkich danych, również po odświeżeniu. Gdy w danych źródłowych pojawi się kolejny człon numeru zamówienia po myślniku, nie będzie on niestety widoczny po odświeżeniu danych.
Pułapka nr 8 – Scalanie kolumn
Polecenie scalania wcześniej dzielonych kolumn jest rzadziej używane, jednak jest jak najbardziej dostępne.
Możemy wskazać separator oraz nową nazwę kolumny. Ważne jest jednak, że operacja wykorzystuje znowu wszystkie nazwy łączonych kolumn, więc przy zmianie nazw, może się nie powieść.
Pułapka nr 9 – Rozwijanie tabel
Rozwijanie tabel można wykorzystać podczas łączenia dwóch zapytań lub importowania danych z folderu. Ten krok jest dostępny po kliknięciu przycisku rozwijania, który umożliwia wybór kolumn do rozszerzenia – kolumny określa się checkboxem, a po wybraniu zostają zakodowane w formule. Jeśli dojdzie jakaś kolumna lub pragniemy jakąś wykluczyć, domyślne zachowanie będzie sprawę utrudniać.
Pułapka nr 10 – Usuwanie duplikatów
Dziesiąta pułapka jest związana z usuwaniem duplikatów. Operacja nie traktuje jako duplikat słów, które mają małe i duże litery. Może to spowodować, że do modelu danych załadujemy poprzez Power Pivot lub Power BI tabelę, która zawiera duplikaty, ale nie w rozumieniu Power Query.
Co do 10 punktu to warto dopowiedzieć, że PQ jest wrażliwe w każdym miejscu na wielkość liter, zarówno przy scalaniu jak i formułach typu IF.
Potwierdzam, wrażliwość na małe i duże litery jest wszędzie, co ma swoje plusy i minusy.
Mam pytanie odnosnie scalania kolumn. Dzis napotkalam problem z tym iz po polaczeniu dwoch kolumn (wyszla wartosc liczbowa nr samochodu) i odswierzeniu danych. W arkuszu obok chcialam skorzystac z formuly wyszukaj.pionowo niestety formula nie zagrala. Nie odnalazla szukanej wartosci. Wyskoczy blad nd. Natomiast jak dalam ctr+f to wyszukalo ta wartosc. Co moglam zrobic nie tak. Dopowiem ze dopiero sie ucze korzystac z. pq.
Dzięki za pytanie. Proszę podesłać plik, zerknę i spróbuję pomóc.
Czy możecie pomóc, jaką formułe wpisac aby utworzyć kolumnę, sumującą wszystkie pozostałe kolumny poza dwoma np. o nazwie A i B? Dodam, że liczba kolumn będzie zmienna.
Przy wprowadzaniu poniższego kodu:
Table.AddColumn(
#”Renamed Columns”,
„x”,
each List.Sum(
Record.ToList(
Record.SelectFields(
_,
List.RemoveItems(Table.ColumnNames(#”Renamed Columns”), {„a”, „b”})))))
wyskakuje błąd, że „Expression.Error: Obliczanie spowodowało przepełnienie stosu i nie może być kontynuowane.” baza ma 14000 wierszy…
Dzięki
Karolina
Cześć! Formuła działa i ciężko mi powiedzieć, czemu zwraca błąd u Ciebie. Spróbuj zrobić tak:
1. Dodaj nową kolumnę niestandardową o wartości „a”
2. Otrzymasz krok w stylu:
= Table.AddColumn(#”Dodano kolumnę niestandardową”, „Niestandardowe”, each „a”)
3. Z Twojej formuły wstaw w miejsce each „a” tylko ten fragment od each Sum.List… do końca z pominięciem ostatniego nawiasu.
PowerQuery jest niesamowitym narzędziem i w swojej karierze oprócz takich niedopatrzeń w ustawieniach doświadczyłem dwóch sytuacji znacznie problematycznych z tym narzędziem. Pierwsze to instalując Office 2021 okazało się, że PQ się nie ładuję ponieważ excel nie jest w stanie załadować właściwej wersji .net i tutaj pomogło mi narzędzie NetFxRepairTool.exe. Drugim przypadkiem, który nie był zbytnio do rozwiązania kiedy niektóre addiny branżowe po aktualizacji Excela weszły w konflikt z PQ, i wtedy może działać tylko jedno lub drugie.
Dzięki, Pawlo, za podpowiedzi dla innych użytkowników Power Query.
Jak zabezpieczyć się przed dodawaniem automatycznych kroków?
Opcja 1. Można je wyłączyć w opcjach Power Query.
Opcja 2. Zostawić domyślne ustawienia, ale zawsze te kroki sprawdzać.
A ja dziś spotkałam się z zupełnie nowa sytuacja. Niejednokrotnie już edytowalam dane zawierające długie teksty w komórkach i wszystko działało super. Dziś przygotowując pliki do składu kolejnej książki zauważyłam, że dane w niektórych komórkach są ucięte. Przeszukałam na szybko internet pod kątem limitów znaków w komórce dla dodatku PQ i to jest aż 1 milion, więc ta sytuacja mnie nie dotyczy. Pobieram dane z innego pliku Excel. Przesledzilam cała ścieżkę i wychodzi na to, że od razu po pobraniu dane są uciete. Masz może pomysł dlaczego tak się dzieje?
Hej Kasia, podgląd w komórce Power Query mieści tylko 1024 znaki, ale sama komórka przechowuje więcej. Po kliknięciu Zamknij i załaduj otrzymasz w Excelu właściwy wpis. Trzeba tylko uważać przy różnych transformacjach w PQ, bo moją mieć swoje limity.