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 Pytanie „głupie” brzmi:
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, in Sciezka Czyli musiałem użyć dwóch funkcji tablicowych Table.SelectColumns() Nie mogę uwierzyć, że to musi być tak skomplikowane, dlatego c. Stworzenie funkcji, która podaje ścieżki zgodnie z liczbowym parametrem nr ścieżki. d. Modyfikacja zapytania do pliku Logistyka.xlsx Jak w punkcie c.
|
|
|
|
|
|
Załączniki
- PQ_Q_Param.xlsm ( 25K )