Kategorie szkoleń | Egzaminy | Kontakt
  • 3
  • 0
  • 1

Pytanie o działanie PowerQuery (Język M).

  • Opis problemu:

Po kursie z PQ (9-10 lipca) zafascynowany możliwościami ale i pełen niewyjaśnionych wątpliwości, co chyba zrozumiałe, w tak krótkim czasie, postanowiłem trochę poćwiczyć przykłady.

Nasz instruktor wgrał nam swoje rozwiązane przykłady w formie pliku zip na stronie klasy kursu oraz umożliwił ściągnięcie naszych prób, które były obarczone nieuniknionymi brakami.

Jak się okazało, wszystkie przykłady na źródłach plikowych nie działały ani w wersji trenera ani w wersji studenta i wymagały dostosowania dostosowania ścieżek.

Spowodowane to było faktem, że nie uznałem za stosowne odtworzyć ścieżki dostępu do folderu ćwiczeniowego, zgodnego z tym, jaki był na zajęciach. Jest to znany problem „sztywnych ścieżek”, który istnieje od kilkudziesięciu lat w MS Query(który daje się rozwiązać przy pomocy VBA).

Tutaj to podejście jest nieprzydatne, bo PQ jest „ciałem obcym” dla Excela, posiadającym własny język i własny kompilator (język M i jego „silnik”).

Postanowiłem zmierzyć się z rozwiązaniem tego problemu i trochę głębiej poznać język M, który na zajęciach był omawiany skrótowo, ale dało mi to kopa.

Oto moje rozwiązanie: (plik PQ_Q_Param.xlsm – dalej plik rozwiązania w skrócie plik R)

  • Próba rozwiązania
    a. Zapewnienie płynnych parametrów tekstowych – ścieżek dostępu do plików źródłowych dla PQ.

W pliku R, w arkuszu Parametry stworzyłem  tabelę z parametrami toParametry, która zawiera kilka formuł tworzących ścieżki do źródłowych plików PQ.

Rys 1

Indeks

Scieżka

                     

1

C:\Exc\PowerQuery\KursAltkomInstruktor\ppq\08_01 funkcje

 

 

 

2

C:\Exc\PowerQuery\KursAltkomInstruktor\ppq\09 przelicznik kursów

 

 

3

C:\Exc\PowerQuery\KursAltkomInstruktor\ppq\extra

 

 

   

 

(Trzy) formuły w tych kolejnych komórkach są następujące:

=LEWY(KOMÓRKA("nazwa_pliku");ZNAJDŹ("[";KOMÓRKA("nazwa_pliku"))-2)&"\08_01 funkcje"

 

=LEWY(KOMÓRKA("nazwa_pliku");ZNAJDŹ("[";KOMÓRKA("nazwa_pliku"))-2)&"\09 przelicznik kursów"

=LEWY(KOMÓRKA("nazwa_pliku");ZNAJDŹ("[";KOMÓRKA("nazwa_pliku"))-2)&"\extra"

 

Czyli zakładamy, że pliki źródłowe znajdują się w podfolderze folderu bieżącego (w którym znajduje się plik R).

Aktualny folder bieżący , jak widać na rysunku 1 to –

         C:\Exc\PowerQuery\KursAltkomInstruktor\ppq

Dalsza część ścieżki zawiera znak ” \” i podfolder, w którym znajduje się interesujący nas plik źródłowy.

 

Jeśli przeniesiemy pliki do dowolnego folderu ale odtworzymy podfoldery z plikami źródłowymi

 zgodnie z tym samych schematem i wgramy do tegoż podfolderu nasz plik R, nasze zapytania powinny

działać bez żadnego dostosowywania ścieżek.

 

b.      Przeczytanie ścieżek z Excela do PQ (tego dotyczy moje zapytanie)

Najtrudniejsza (dla mnie) część zadania, która jest przedmiotem mojego pytania
zawiera się właśnie w punkcie 2.b.
Mógłbym właściwie zadać proste pytanie o ten problem, ale chciałem jednak ”pochwalić się”
swoim rozwiązaniem (ryzykując kompromitację).
Gdybym zapytał od razu o to i odpowiedź byłaby „banalna” - nie byłoby efektu.
Chciałem ją umieścić w kontekście rzeczywistego problemu z zakresu
raportowania korporacyjnego a nawet międzykorporacyjnego.

Pytanie „głupie” brzmi:
Jaka jest prawidłowa metoda odczytu wartości z pola tabeli  dla określonej wartości indeksu?

 

W SQL jest to banalna operacja:

SELECT Scieżka FROM toParametry WHERE Indeks = 1

W języku M przestudiowałem prawie wszystkie funkcje języka, które wydawały mi się przydatne

I nie znalazłem równie eleganckiego rozwiązania.

Rozwiązanie moje jest następujące:

let

    Źródło = Excel.CurrentWorkbook(){[Name="toParametry"]}[Content],

    Sciezka = Table.FirstValue(Table.SelectColumns(Table.SelectRows(Źródło,
                     each [Indeks] = 1),"Scieżka"))

in

    Sciezka

Czyli musiałem użyć dwóch funkcji tablicowych Table.SelectColumns()
oraz Table.SelectRows() w ten sposób, żeby otrzymać jednoelementową tablicę
a następnie odczytać tę wartość funkcją Table.FirstValue().

Nie mogę uwierzyć, że to musi być tak skomplikowane, dlatego
proszę o korektę tego topornego zapytania.

c.       Stworzenie funkcji, która podaje ścieżki zgodnie z liczbowym parametrem nr ścieżki.
Dalej już poszło bez większych problemów i nie będę się nad tym rozwodził, odsyłając do pliku R.

d.      Modyfikacja zapytania do pliku Logistyka.xlsx

Jak w punkcie c.

 

 

 

 

 

 

 

     

 

 

Załączniki

  • xlsm

    PQ_Q_Param.xlsm ( 25K )
Krzysztof_Rumiński
  • Zapytał
  • @ Krzysztof_Rumiński | 27.07.2020
    • 0
    • 0
    • 0
Zaloguj się aby zadać pytanie
Pokrewne

Odpowiedzi (3)

  • 1

Słowem wstępu: podzielam Twoją fascynację PQ, a im bardziej go poznaję, tym bardziej ona wzrasta :) Oczywiście wszystko zależy od tego, do czego używasz Excela - u mnie na szczęście PQ idealnie wpasował się w potrzeby zawodowe, także praktycznie wszystkie moje excelowe narzędzia tworzę przy jego pomocy (z mniejszym lub większym udziałem VBA). Gorąco polecam tę ścieżkę! :)

Ad rem:

a) O ile dobrze pamiętam, funkcja KOMÓRKA odnosi się do aktywnego skoroszytu, może więc stwarzać problemy przy większej liczbie otwartych plików. Skoro i tak używasz VBA i zdarzenia Workbook_Open, bezpieczniej będzie odczytać ścieżkę przy pomocy ThisWorkbook.Path i zapisać ją do wybranych komórek. Dodatkowo od wersji 2016 Excela PQ już nie jest mu taki obcy i posiada własną klasę ThisWorkbook.Queries, w której możesz edytować parametry i same zapytania z poziomu VBA, zatem te ścieżki możesz wstrzyknąć bezpośrednio do kwerend.

b) Zarówno w SQL, jak i w PQ musisz wskazać kolumnę i wiersz, z przecięcia których chcesz otrzymać dane. Składnia SQL jest prostsza dlatego, że z jednej strony to Ty wybrałeś (to nie zarzut ;) "skomplikowaną" drogę w PQ*, a z drugiej te funkcje mają dużo szerszy wachlarz zastosowań, gdzie potrafią pokazać swoją przydatność (szczególnie gdy budujesz zapytania z warunkowym doborem wierszy i kolumn lub w sytuacjach, gdy np. nazw kolumn w ogóle nie znasz). Tradycyjnie metod dojścia do celu jest wiele, a z tych najpopularniejszych:

Sciezka = Table.SelectRows(Źródło, each [Indeks] = nrSciezki){0}[Scieżka]

Sciezka = Źródło{[Indeks = nrSciezki]}[Scieżka]

...lub - ale z mocnym zastrzeżeniem, że indeksy będą zawsze ułożone rosnąco i mając w pamięci, że numeracja wierszy tabeli zaczyna się od zera (bo tu odnosimy się do bezwzględnego położenia wiersza, zupełnie nie związanego z zawartością kolumny "Indeks"):

Sciezka = Źródło{nrSciezki - 1}[Scieżka]

Żaden z powyższych przykładów nie da Ci jednak możliwości dynamicznego doboru kolumny (czyli np. nie przypiszesz jej nazwy do zmiennej, musi być na sztywno wpisana w kodzie**) - i tu właśnie przydaje się funkcja Table.SelectColumns, ale to już temat na odrębną dyskusję.

 

* - wystarczyło kliknąć prawą myszą na pole, z którego chcesz pobrać wartość i wybrać opcję "Przejdź do szczegółów", żeby PQ sam podpowiedział Ci to trzecie rozwiązanie :)

** - oczywiście i to da się obejść dzięki funkcji Expression.Evaluate, ale ją zostaw sobie na moment, gdy już będziesz myślał, że o PQ wiesz wszystko :)

Mirosław_Janiak
  • Odpowiedział
  • @ Mirosław_Janiak | 28.07.2020
    • 1
    • 7
    • 1
Komentarze
Bardzo dziękuję za obszerną i rzeczową odpowiedź. Czuję się umocniony w swojej fascynacji :) Pozdrawiam serdecznie.
Skomentował : @ Krzysztof_Rumiński ,04.08.2020
  • 0
  • 0
  • 0
  • 0

Jeszcze w ramach dyskusji do bardzo interesującej odpowiedzi:

1) 

O ile dobrze pamiętam, funkcja KOMÓRKA odnosi się do aktywnego skoroszytu, może więc stwarzać problemy przy większej liczbie otwartych plików. Skoro i tak używasz VBA i zdarzenia Workbook_Open, bezpieczniej będzie odczytać ścieżkę przy pomocy ThisWorkbook.Path

Słuszna uwaga: Funkcja KOMÓRKA działa, tak jak napisałeś, jak się okazuje (sprawdziłem to przede chwilą). Zawsze mi się wydawało, że opisuje ten skoroszyt, w którym formuła się znajduje, a ona opisuje stan aktywnego skoroszytu.

Porażka. Moja i MS.

ThisWorkbook rzeczywiście jest bezpieczniejszy, bo odkreśla  po prostu skoroszyt z kodem VBA, więc tu nie ma niespodzianek.

Dziękuję za oświecenie doświadczonego gapy.

Chciałem po prostu minimalizować kod VBA. Jeśli założymy, że skoroszyt z PQ jest aktywny, to moje rozwiązanie praktycznie się broni, ale jest teoretycznie nieprawidłowe, 

... bo przy wielu skoroszytach mamy długo problem, "że nie działa i nie wiadomo dlaczego". (z czterech kombinacji działania i wiedzy o jego przyczynach ta jest zawsze najgorsza :)) .

Dziękuję, będę pamiętał.

2) 

Dodatkowo od wersji 2016 Excela PQ już nie jest mu taki obcy i posiada własną klasę ThisWorkbook.Queries, w której możesz edytować parametry i same zapytania z poziomu VBA, zatem te ścieżki możesz wstrzyknąć bezpośrednio do kwerend.

Super informacja!  Mam nadzieję, że integracja z Excelem będzie postępować z czasem. Bo takie "obce ciało" jest pewnym problemem.

3)

No i wreszcie trzy dodatkowe możliwości. To jest pokazanie klasy experta. Szacun.
Jestem teraz naprawdę zaopatrzony na dalszą drogę.

4) Zachęcony - podaję mój problem nad którym utknąłem - i (nawet najbardziej topornymi :)) metodami nie mogę obejść:

Modyfikując działające (stworzone przez instruktora rozwiązanie) internetowe zapytanie internetowe połączone z zapytaniem do pliku Excela dostałem komunikat:

„Formula.Firewall: Element Zapytanie „rok_archiwum” (krok „Dodano kolumnę niestandardową”) próbuje uzyskać dostęp do źródeł danych z poziomami prywatności, które nie mogą być używane razem. Utwórz ponownie tę kombinację danych.”

Instruktor zrobił przykład, który działa.
Jedno źródło – Internet (strona NBP) daje w wyniku funkcję tablicową {data waluta kurs}, drugie źródło – plik Excela z transakcjami w walutach.
Ja robię przykład w pliku trenera, zmieniając tylko lokalizację mojego pliku Excela. Dlaczego jemu chodzi a zmianie ścieżek mnie opluwa?

 

Krzysztof_Rumiński
  • Odpowiedział
  • @ Krzysztof_Rumiński | 04.08.2020
    • 0
    • 0
    • 0
  • 1

 Podejrzewam, że u Ciebie problemem jest użycie w jednym zapytaniu podłączenia do pierwszego źródła bezpośrednio, a do drugiego odnośnikiem do innej kwerendy, czyli coś w tym stylu:

let
    Źródło = Excel.CurrentWorkbook(){[Name="tabela_excel"]}[Content],
    #"Scalone zapytania" = Table.NestedJoin(Źródło,{"ID"},kwerenda_NBP,{"ID"},"Nowa kolumna",JoinKind.LeftOuter)
in
    #"Scalone zapytania"

U mnie przeważnie sprawdza się jedno z poniższych rozwiązań:

1) W opcjach PQ w ustawieniach prywatności ustaw ignorowanie ich poziomów. Z tymi poziomami z grubsza chodzi o to, że przy wymaganych wysokich standardach bezpieczeństwa danych, wysłanie zapytania do bazy może wiązać się z ujawnieniem w kwerendzie jakichś danych wrażliwych (np. wysłanie do serwera select * from tabela where name='Janiak' or pesel='12345' może skutkować zapisaniem tej informacji w logach i dostępem do niej przez osoby nieuprawnione). Wyższe poziomy powodują ściągnięcie pełnej tabeli i dokonują filtrowania już lokalnie, bez przekazywania szczegółów na zewnątrz. Do celów edukacyjnych można je wyłączyć, ale miej z tyłu głowy, że w przyszłości trzeba będzie się tym głębiej zainteresować :)

2) Utwórz połączenia do pliku i do strony NBP jako dwie odrębne kwerendy, a w trzeciej (docelowej, którą załadujesz do arkusza) użyj odniesień do nich.

3) Odwrotnie niż w poprzednim punkcie: utwórz oba połączenia, ich złączenie i całą transformację w pojedynczej kwerendzie, żeby nie łączyć jej z żadną inną (cały kod umieść w jednym zapytaniu).

 

Mirosław_Janiak
  • Odpowiedział
  • @ Mirosław_Janiak | 14.08.2020
    • 1
    • 7
    • 1