excel-654 - Power Query - zapytanie z parametrami dotyczącymi jednej kolumny

[@pinolwro]

Świetny materiał! Jasno i klarownie przedstawione. Testowałem i działa świetnie. Używam do zasilenia raportu finansowego obrotówką z systemu ERP. Wcześniej ponad 70k rekordów wrzucałem do skoroszytu i excel dochodził do granic możliwości, trudno było z tego korzystać. Teraz dane trzymam w osobnym pliku, a skoroszyt pobiera tylko skonsolidowane, przetworzone i podsumowane dane przez PowerQuery. Powyższej funkcjonalności używam, żeby móc wyświetlić szczegóły danej pozycji (zapisy księgowe na wybranych kontach i np. obiektach kontrolingowych). Ale tu pojawia się problem - mam kilka parametrów, które filtrują dane źródłowe (np. konto, obiekt CO, dział itp.) ale nie wszystkie muszą być uzupełnione. Jak wymusić na takim scalonym zapytaniu, żeby wartość parametru 'null' traktował jako wszystkie możliwe? (przykład z Twojego odcinka - filtr zawiera 3 parametry: Odbiorca=null, Handlowiec="Marek", Data=null)

[@pmsocho]

Hmm, pierwsze co przychodzi mi do głowy to instrukcja warunkowa if. Niestety trzeba wejść troszkę w język M. Zapytanie o nazwie "Wybrane" w pliku do tego odcinka mogłoby wyglądać wówczas tak: let Źródło = if Parametry{0}[Odbiorca] = null then Dane else Table.NestedJoin(Dane,{"Odbiorca"},Parametry,{"Odbiorca"},"Nowa kolumna",JoinKind.Inner), #"Zmieniono typ" = Table.TransformColumnTypes(Źródło,{{"Data", type date}}) in #"Zmieniono typ"

[@pinolwro]

@@pmsocho Dzięki za podpowiedź. Jak dobrze rozumiem powyższe powinno zadziałać niezależnie, czy w parametrach będzie podany jeden odbiorca, kilku, czy żaden. Tylko, że jak mam tabelę, która ma 18 pól i filtrowanie może być robione na większości z nich, to trochę zaczyna się robić skomplikowane i mało czytelne. Ale póki co nie znalazłem lepszego rozwiązania. Dziś robię tak, że dane z zewnętrznego źródła ładuję do modelu danych, i wyświetlam w postaci pivota sformatowanego do postaci prostej tabeli. Dzięki temu można filtrować każde pole. Mam też drugie pytanie. Chciałem w sposób dynamiczny odwoływać się do danych zawartych w takim modelu danych za pomocą formuł (coś na zasadzie suma.warunków(wartość;konto;xxx;dział;yyy;okres;zzz) ). Próbowałem robić to przy pomocy funkcji WARTOŚĆ.MODUŁU ale jest ona strasznie nieporęczna, a główną jej wadą jest to, że musi odwoływać się elementów modułu. jest na to jakiś przejrzysty sposób?

[@pmsocho]

@@pinolwro Z tego co mi wiadomo, do modelu danych mogą odwoływać się jedynie funkcje modułowe.

[@NihonDream]

Ładnie, prosto i na temat. Dziękuję.

[@kreo4366]

Ze wszystkich odcinków można się wiele nauczyć i chwała Ci za to😃 Dotychczasowa tematyka dość uniwersalna dla wszystkich😃 Power query jak najbardziej rozwojowe i tą tematykę również z ciekawością śledzę😃 Proponuje dorzucić jeszcze tematykę BAZ DANYCH, stosowałem kiedyś reguły typu bd.pole i myślę że również można z tego wyciągnąć coś ciekawego (to tak gdyby brakło pomysłów) 😃 Jeszcze raz dzięki za całość :-)

[@pmsocho]

Dzięki za miłe słowa. Funkcje bazodanowe pokazywałem już w którymś z odcinków ale nie pamiętam w którym :)

[@maciejmyslinski7820]

Bardzo fajny i przydatny materiał! Na podobnej zasadzie buduje pliki gdzie muszę mieć sparametryzowaną np. ścieżkę do pliku. :)

[@pmsocho]

Super! Dzięki za info i lajka! :)

[@malgorzatastarosta3619]

Genialne, bardzo pomogło mi to w zrobieniu raportu dla koleżanki. Wielkie dzięki :)

[@pmsocho]

Super! Dzięki za oglądanie, komentarz i lajka! :)

[@olimaj5556]

Mega !!a zamiast wpisywać to dodałam listę rozwijalną , którą również nauczyłam się z Twoich filmów. Działa :)

[@mirekkryskapriv7445]

Super! Dziękuję Czy to zadziała też, w obecnym MS 365, czyli np. w desktopowym excelu zrobię taki arkusz, a potem otworzę go on-line z poziomu OneDrive?

[@kamilmenartowicz2005]

Filmik super, a czy jest jakiś sposób by tabela z parametrem dopasowywała wyniki na zasadzie"zawiera", a nie tak ja w tym przypadku " równa się"?

[@pmsocho]

Przy prezentowanej w filmie metodzie możesz osiągnąć to co opisałeś korzystając z dopasowania rozmytego (na filmie nie ma tej opcji bo w momencie nagrywania tego filmu jeszcze tego nie było).

[@tomekkomar6313]

krótko, jasno i węzłowato :-)

[@pmsocho]

Dzięki :) Świetnie, że Ci się podobało.

[@damian_dkx]

Cześć Piotrze, jako, że nie mogłem znaleźć filmiku o tematyce bardziej zbliżonej do mojego problemiku, pozwolę sobie zadać pytanie tutaj (może Twoja odpowiedź z rozwiązaniem przyda się także jeszcze komuś). Mam taką potrzebę w PQ: chciałbym by w edytorze utworzona kolumna niestandartowa pobrała (i wypełniła się cała) wartością pobraną z konkretnej komórki w arkuszu. Chodzi mi konkretnie o pobranie daty i wypełnienie tą datą całej kolumny niestandartowej. Jak powinien wyglądać kod (funkcja) by tak się stało? Ręcznie wpisana data w kodzie kolumny niestandartowej miała by taki zapis: ="2022-06-10" a ja chciałbym, by ta data była pobrana z jednej komórki z arkusza. (musiałoby jakoś być napisane odwołanie do tego pliku, następnie arkusza no i adresu tej komórki z datą. Czy byłbyś uprzejmy podać taki kod funkcji, jaki należy wpisać w polu: Formuła kolumny niestandartowej? Tak wygląda ścieżka dostępu do komórki z datą: ='[REFAKTURY.xlsx]2022'!$D$1. Pozdrawiam Cię

[@pmsocho]

Najprościej będzie pobrać wartość z tej jednej komórki jako osobne zapytanie. Następnie w tym zapytaniu można kliknąć na komórce z datą prawym przyciskiem myszki i wybrać polecenie Wyszczególnij - sprawi to, że zapytanie będzie dawało w wyniku wartość a nie tabelę. Następnie można w głównym zapytaniu utworzyć kolumnę niestandardową i w formule użyć nazwy tego pierwszego zapytania.

[@damian_dkx]

@@pmsocho Dziękuję Piotrze za podpowiedź, to było to rozwiązanie jakiego potrzebowałem. No i coś zupełnie nowego się nauczyłem. Tego nie widziałem jeszcze na żadnym z filmików. Dzięki i pozdrawiam!

[@ExcelInstructor]

szukałem tego rozwiązania! Dziękuję!! jednak mam małe pytanie o Data validation. gdy próbuję w data validation List umieścić taka formułę: =UNIQUE(Dane[Odbiorca];0;0) to niestety otrzymuję błąd i musze formułę wpisać do innego zakresu pomocniczego i dopiero z niego pobrać dane do data validation. da rade to obejść?

[@pmsocho]

Nie da rady tego obejść na chwilę obecną.

[@ExcelInstructor]

@@pmsocho Like dla Pana i dislike dla microsoftu - to byłaby dość przydatna funkcjonalność

[@MrBeliever69]

Chciałem stworzyć dynamiczne zapytanie które pobiera dane z różnych baz danych. W związku z tym utworzyłem tabelę gdzie użytkownik wpisuje sobie bazę danych z jakiej chce korzystać. Nazwa bazy danych z tej tabeli jest wyszczególniona i zapisana jako połączenie. W innej zapytaniu w kroku źródło mam ścieżkę, gdzie zamiast nazwy bazy zapisanej w cudzysłowie mam nazwę tego zapytania. U mnie działa. Niestety gdy przesłałem ten plik koledze wyskakują mu błędy. Co może być przyczyną?

[@przemysawborkowski8658]

Dzień dobry. Bardzo interesujący film jak również cały kanał. Niestety nie udało mi się znaleźć potrzebnej informacji. A mianowicie: za pomocą Power Query pobieram dane ze strony NBP z historycznymi kursami walut (api.nbp.pl/api/exchangerates/rates/A/EUR/last/40?format=xml). Za pomocą edytora ustawiam widoczność odpowiednich danych (data, numer tabeli, kurs). Korzystając z formuły WYSZUKAJ.PIONOWO (=WYSZUKAJ.PIONOWO(H2-1;Tabela_kursów;Tabela_kursów[No];PRAWDA)) próbuję się odwołać do stworzonej tabeli, aby pobrać dane do obliczenia wartości z EUR na PLN z dnia poprzedzającego wystawienie rachunku. Niestety, za każdym razem dostaję odpowiedź #N/D!. Natomiast kopiując dane z tabeli Power Query obok wszystko działa jak należy. Czy jest Pan w stanie pomóc z odwołaniem się do tabeli Power Query?

[@pmsocho]

Tabela będąca wynikiem działania zapytania stworzonego w PQ zachowuje się jak każda zwykła tabela w Excelu i tak samo można się do niej odwoływać. Nie wiem niestety w jaki sposób przekształciłeś pobrane dane w PQ i jak wygląda tabela będąca wynikiem zapytania więc ciężko pomóc. Na pewno źle zbudowana jest funkcja, którą napisałeś: =WYSZUKAJ.PIONOWO(H2-1;Tabela_kursów;Tabela_kursów[No];PRAWDA) Trzecim argumentem funkcji WYSZUKAJ.PIONOWO powinna być liczba a nie wskazanie całej kolumny z tabeli tak jak jest to w Twoim przypadku.

[@przemysawborkowski8658]

Dziękuję! Rozwiązałem problem. Miałem błąd w formule WYSZUKAJ.PIONOWO. Szukana wartość powinna być po lewej stronie zwracanej wartości, którą chciałem otrzymać. Pozdrawiam ( ͡° ͜ʖ ͡°)

[@pmsocho]

Przemku, skoro zabrałeś się za PQ to założyłem, że WYSZUKAJ.PIONOWO masz w małym paluszku :) Świetnie, że sobie poradziłeś :)

[@tomekkomar6313]

A ja dziękuję Przemkowi za podesłanie linku do NBP, bo nie mogłem go ustalić. W poradnikach jest tylko mowa o kursie aktualnym, a dzięki temu linkowi można ściągać kursy z ostatniego okresu ( ja ściągam ostatni m-c). Dzięki :-)

[@pmsocho]

:)

[@excelisfun]

Totallly Cool Right Outer Join to Extract Records with Criteria!!! : )

[@pmsocho]

It's great that we have many ways to do things in Excel or Power Query :)

[@excelisfun]

: ) Almost as rad as BMX or Boomerangs!?!? Or is it the other way around!?!?

[@pmsocho]

ExcelIsFun Yes! For sure. I did try boomerangs - they are super fun! I did not try BMX but I used to ride dual slalom and downhill when I was younger and my knees were in a better condition. That was also super cool :)

[@excelisfun]

So many cool ways to fun, both in Excel and out! It is great to be on a Team with you!!! : )

[@garyallan69]

Totally agree Mike but it's not in English??

[@FRANKWHITE1996]

Pięknie!,,

[@pmsocho]

Dzięki za bycie wiernym fanem :)

[@wojciechrybak3477]

Fajny sposób.

[@pmsocho]

Dzięki :)

[@ASWO_PL]

A czy istnieje sposób aby zamiast pobierać całą tabelę z bazy , edytować zapytanie SQL w segmencie "WHERE" z parametru znajdującego się w komórce i pobrać tylko wartości spełniające warunek ?

[@pmsocho]

Hmm, osobiście tego nie testowałem ale wydaje mi się to do zrobienia. Googluj a jak znajdziesz działające rozwiązanie - daj koniecznie znać!

[@ASWO_PL]

@@pmsocho wygooglać się nie udało , ale nagrywając makro od momentu importu z bazy danych uzyskałem kod w VBA, który w łatwy sposób mogę modyfikować aby parametry były pobierane z arkusza , problem jest nowy , utworzony kod VBA tworzy zawsze nowe zapytanie i arkusz o tej samej nazwie co poprzedni, i aby działało przed odpaleniem makra muszę usuwać poprzedni arkusz i zapytań również nie potrzebnie tworzy się bardzo dużo, ale to pokazuje że jest to możliwe, potrzeba tylko kogoś mądrzejszego, kto umiał by w VBA edytować połączenie jakim jest zapytanie :D .

[@pmsocho]

@@ASWO_PL Niestety nie jestem specem w VBA więc nie pomogę w tej materii ale jeśli znajdziesz działające rozwiązanie albo dojdziesz do niego - daj koniecznie znać :)

[@fajnefajne4339]

Love

[@pmsocho]

Dzięki za oglądanie :)

[@MrVego86]

Dzień dobry, próbowałem to zrobić na swoim zbiorze danych, ale pojawił się problem i taki oto komunikat: DataFormat.Error: Nie możemy przekonwertować na typ Number. Szczegóły: internet Co może być przyczyną błędu? Dziękuję za pomoc.

[@pmsocho]

Ciężko powiedzieć, gdy nie widzę danych. Podejrzewam, że przyczyną może być niezgodność typów danych spowodowana automatycznym rozpoznawaniem typu danych dla każdej z kolumn. Zauważ, że Power Query, bardzo często automatycznie dodaje krok o nazwie "Zmieniono typ danych" ("Changed Type"). Usuń ten krok jak tylko się pojawi i samodzielnie ustaw odpowiednie typy danych dla kolumn. Na szkoleniu "live" omawiam tego typu problemy - zapraszam :)

[@MrVego86]

pmsocho miałeś rację, wystarczyło usunąć tylko ten krok! Dzięki wielkie.

[@pmsocho]

Cieszę się, że pomogłem :)

[@zefirekdk]

Witam, a co z datami? Z dnia, z okresu itp.?

[@pmsocho]

Na filmie pokazałem tylko jeden z wielu sposobów na sparametryzowanie zapytania Power Query. W przypadku dat zastosowałbym inne podejście ale to zbyt obszerny temat jak na komentarz YT :)

[@zefirekdk]

To może osobny filmik?

[@pmsocho]

Na pewno kiedyś i ten temat się pojawi :)