Power Query świetnie nadaje się do tworzenia zapytań, które będą obsługiwane przez cały zespół lub dział. Przy okazji ich nagrywania warto je uporządkować, aby każda osoba, które je otworzy, łatwo połapała się w ich strukturze. Docenisz to, gdy tylko pójdziesz na urlop i przekażesz Twoje zapytania w cudze ręce. Oto nasze dobre praktyki projektowe.
1. Utwórz foldery
Zapytania odkładają się w kolejności odświeżania w prawym panelu. Ich kolejność powinna pozostać od góry do dołu. Gdy jest ich dużo, utwórz Grupy, w których umieścisz podobne zapytania. Najczęściej wykorzystywane przez nas nazwy: Dane wejściowe, Dane wyjściowe, Słowniki, Dane do modelu.
2. Nazwij kroki
Ważnym krok możesz nadać nową nazwę. Jeśli będzie ona zawierać wcięcie (np. –>), krok wyróżni się na tle pozostałych. Nie ma sensu opisywać w ten sposób wszystkich kroków (np. takich jak Zmieniono Typ), a tylko najważniejsze czy nietypowe.
3. Opisz właściwości
Jeśli sama nazwa to za mało, możesz dodać dla niego właściwości. Tworzy to jasny schemat: krok wyróżniony zawiera komentarz po najechaniu. Komentarze są też dobrze widoczne w oknie Edytora zaawansowanego, gdzie Power Query gromadzi cały kod języka M.
4. Stwórz krok rozdzielenia
Jeśli zapytanie jest bardzo długie, warto podzielić je sobie na etapy. Można to zrobić poprzez wydzielenie nowego zapytania (Odwołanie) lub po prostu w oknie Zastosowane kroki. Sztuczny krok rozdzielający wstawisz klikając Wstaw krok po.
5. Twórz zapytania jako nowe
Nowe zapytania dużo lepiej wyglądają na diagramie zależności zapytań. Dlatego przy tworzeniu zapytania, warto wybierać operację Dołącz jako nowe oraz Scal jako nowe. Jedynym wyjątkiem jest raport już załadowany do modelu danych lub tabeli przestawnej, który musisz kontynuować, aby zachować ciągłość.
6. Umieść w nazwie operację źródłową
Nowe zapytania mogą pochodzić z różnych źródeł lub z operacji Power Query: Dołączania (Append), Scalania (Merge) lub Odwołania (Reference). Warto dodać taki dopisek w nazwie zapytania lub tylko skrót (D, S, O).
7. Utwórz zapytania sprawdzające
Zapytania sprawdzające służą do weryfikacji jakości danych i sprawdzania jej na bieżąco. Pamiętajmy, że przy kolejnym odświeżaniu raczej nie będzie już otwierać Edytora zapytań Power Query, tylko klikniemy Odśwież wszystko. Dlatego warto utworzyć zapytania sprawdzające i załadować je do jednego arkusza. Możesz na ich bazie utworzyć też raport jakości danych. Przykładowe nazwy:
- Błędy – Brakujące kody pocztowe
- Błędy – Produkty, których nie ma w słowniku
- Błędy – Brak ID pracownika
- Błędy – Zduplikowane numery zamówienia
- Błędy – Błędne daty
Zapytanie sprawdzające najczęściej wstawiam jako Odwołanie lub Scal jako nowe z rodzajem sprzężenia Lewe anty.
Zapytania pośrednie często wyłączamy z ładowania (Załaduj do… > Tylko połączenie), ale zapytania sprawdzające powinny zostać w Excelu tabelami. Dzięki temu po kliknięciu Odśwież wszystko, zobaczymy liczbę błędów przy każdym zapytaniu sprawdzającym.
Można też zapytania podsumować, np.
Utwórz puste zapytanie i i wpisz:
=#shared
Następnie przekonwertuj je na tabelę.
Ustaw filtr w taki sposób, aby zostały na liście tylko zapytania sprawdzające (np. Zaczyna się od „Błędy”). Na koniec zlicz wiersze, np. nową kolumną niestandardową:
=Table.RowCount([Value])
8. Nazywaj arkusze tak jak zapytanie
Zapytania domyślnie lądują do kolejnych arkuszy – Arkusz2, Arkusz3 itd. Warto zadbać też o ich nazewnictwo, trzymając się nazwy samego zapytania.
9. Dodaj datę ostatniego odświeżania
Datę uzyskamy, tworząc nowe puste zapytanie o zawartości:
=DateTime.LocalNow()
Ponownie można je przekonwertować na tabelę.
Takie zapytanie można załadować do pierwszego arkusza.
10. Dodaj użytkownika odświeżającego
Tu nie ma prostego sposobu, gdyż żadna domyślna formuła Excel nie jest w stanie wydobyć nazwy użytkownika. Można ją wydobyć:
– za pomocą VBA na moment otwarcia pliku
Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1").Value = Environ("Username")
End Sub
Takie zapytanie wczytujemy następnie do tabeli i zwracamy w widocznym miejscu.
– kombinując z zapytaniem do folderu C:\Users\
Sortuj malejąco po Date accessed > Zostaw 1 wiersz na liście > Znajdź folder użytkownika. U mnie wczytuje to aż 150 000 plików, więc trochę trwa, ale działa.
Znasz inne dobre praktyki organizowania pracy w Power Query w Excelu lub w Power BI?
Podziel się w komentarzu.
