Bezpłatny kurs Power Query: przenoszenie zapytań, porównanie z DAX, SQL i VBA oraz dobre praktyki

W skrócieZapytanie zbudowane raz przeniesiesz między Excelem, Power BI i Fabric, bo wszędzie działa ten sam język M. Na koniec kursu porównujemy Power Query z formułami, VBA, Power Pivot, DAX, SQL i Pythonem, zbieramy osiem zasad dobrze zbudowanego zapytania i najważniejsze skróty klawiszowe.

Przenoszenie zapytań między Excelem, Power BI i Fabric

Ponieważ wszędzie działa ten sam język M, zapytania da się przenosić bez przepisywania. Są trzy drogi.

1. Kopiuj i wklej. W panelu zapytań zaznacz zapytania (z Ctrl albo Shift), naciśnij Ctrl+C, a w edytorze docelowym kliknij w panel zapytań i naciśnij Ctrl+V. Przenoszą się kroki, parametry, funkcje i grupy. Tak przeniesiesz zapytania z Excela do Power BI Desktop, z Power BI do Excela i z obu do Dataflow Gen2 w Fabric, co Microsoft opisuje krok po kroku w dokumentacji o migracji zapytań z Power Query Desktop do przepływów danych.

2. Import skoroszytu Excela w Power BI. Power BI Desktop ma polecenie Plik > Importuj > Power Query, Power Pivot, Power View, które przenosi wszystkie zapytania i model danych ze skoroszytu Excela naraz. Pokazujemy je w lekcji o Power BI.

3. Szablon .pqt. W Power Query Online gotowy przepływ zapiszesz jako plik szablonu Power Query (rozszerzenie .pqt) przyciskiem Eksportuj szablon na końcu wstążki. Szablon zawiera zapytania i kod M, a opcjonalnie także ustawienia miejsca docelowego. Inna osoba zaimportuje go w swoim przepływie linkiem Importowanie z szablonu dodatku Power Query na pustym edytorze i dostanie te same kroki, po podmianie adresów źródeł na swoje.

Po przeniesieniu prawie zawsze trzeba poprawić źródła. Poniżej kod M naszego zapytania sprzedaz z Fabric, z adresem OneDrive zastąpionym zmienną. Porównaj krok Źródło z wersją z Excela: zamiast Folder.Files("C:\...") jest SharePoint.Files(...), a reszta kroków się nie zmieniła.

let
    // adres główny OneDrive dla firm albo witryny SharePoint, z ukośnikiem na końcu
    AdresWitryny = "https://twojafirma-my.sharepoint.com/personal/twoje_konto/",
    Źródło = SharePoint.Files(AdresWitryny, [ApiVersion = 15]),
    #"Przefiltrowano wiersze" = Table.SelectRows(Źródło, each Text.Contains([Folder Path], "Sprzedaz_miesieczna")),
    #"Filtrowano pliki ukryte" = Table.SelectRows(#"Przefiltrowano wiersze", each [Attributes]?[Hidden]? <> true),
    #"Wywołaj funkcję niestandardową" = Table.AddColumn(#"Filtrowano pliki ukryte", "Przekształć plik", each #"Przekształć plik"([Content])),
    #"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Wywołaj funkcję niestandardową", {{"Name", "Source.Name"}}),
    #"Usunięto inne kolumny" = Table.SelectColumns(#"Zmieniono nazwy kolumn", {"Source.Name", "Przekształć plik"}),
    #"Rozwinięto kolumnę tabeli" = Table.ExpandTableColumn(#"Usunięto inne kolumny", "Przekształć plik", Table.ColumnNames(#"Przekształć plik"(#"Przykładowy plik"))),
    #"Zmieniono typ kolumny" = Table.TransformColumnTypes(#"Rozwinięto kolumnę tabeli", {{"Data", type date}, {"Nr paragonu", type text}, {"Sklep", type text}, {"Kod produktu", type text}, {"Ilość", Int64.Type}, {"Cena netto", type number}, {"Rabat %", Int64.Type}, {"Wartość netto", type number}, {"Kanał", type text}, {"Płatność", type text}}),
    #"Scalone zapytania" = Table.NestedJoin(#"Zmieniono typ kolumny", {"Kod produktu"}, tProdukty, {"Kod produktu"}, "tProdukty", JoinKind.LeftOuter),
    #"Rozwinięta kolumna tProdukty" = Table.ExpandTableColumn(#"Scalone zapytania", "tProdukty", {"Nazwa produktu", "Kategoria", "Marka"}, {"Nazwa produktu", "Kategoria", "Marka"})
in
    #"Rozwinięta kolumna tProdukty"

Lista kontrolna przy przenoszeniu:

• Ścieżki do plików lokalnych zamień na OneDrive lub SharePoint, albo zainstaluj lokalną bramę danych.
• Poświadczenia ustawiasz na nowo w każdym narzędziu, bo nie przenoszą się razem z kodem.
• Poziomy prywatności ustaw dla każdego źródła, inaczej przy scalaniu wróci pytanie o prywatność albo błąd Formula.Firewall.
• Łączniki dostępne tylko w jednym narzędziu (na przykład Excel.CurrentWorkbook, który czyta tabele z bieżącego skoroszytu) trzeba zastąpić innym źródłem.
• Nazwy: Dataflow Gen2 nie przyjmuje polskich liter w nazwie przepływu, ale kolumny z polskimi znakami (Ilość, Wartość netto, Płatność) zapisał w tabeli Lakehouse bez zmian, co widać na ekranie mapowania kolumn.

Power Query a formuły, VBA, Power Pivot, DAX, SQL i Python

Power Query nie jest jedynym sposobem na przygotowanie danych i nie zawsze najlepszym. Poniżej porównanie z narzędziami, które najczęściej konkurują z nim o to samo zadanie.

Z podziałem między Power Query a DAX związana jest zasada sformułowana przez Matthew Roche'a z zespołu Power BI w Microsofcie: dane przekształcaj tak wcześnie, jak to możliwe, i tak późno, jak to konieczne. W praktyce: jeśli coś da się zrobić w bazie danych, zrób to w bazie, jeśli nie, zrób to w Power Query, a w DAX licz tylko to, co naprawdę zależy od filtrów wybranych przez użytkownika raportu. Kolumna Segment cenowy z naszego przykładu to zadanie dla Power Query, a sprzedaż od początku roku do wybranego miesiąca to zadanie dla miary DAX.

Dla porządku dodajmy, że istnieją też samodzielne narzędzia ETL z graficznym interfejsem, które budują przepływy danych z bloczków. Jednym z nich jest KNIME, który pokazaliśmy w artykule Co to jest KNIME? Tutorial krok po kroku: od instalacji do pierwszego raportu. Power Query wygrywa z nimi tym, że jest już w Excelu i Power BI, które masz na komputerze.

Dobre praktyki i skróty, które oszczędzają godziny

Projektowanie zapytań

• Nazywaj zapytania i kroki po ludzku. „Sprzedaz_wg_sklepu_i_miesiaca" mówi więcej niż „Zapytanie3", a krok „Usunięto wiersz Razem" więcej niż „Przefiltrowano wiersze1". Zmiana nazwy kroku: prawy przycisk albo F2.
• Grupuj zapytania w foldery. Prawy przycisk na zapytaniu, Przenieś do grupy. Przy kilkunastu zapytaniach podział na źródła, słowniki i wyniki porządkuje pracę.
• Ładuj tylko wyniki. Zapytania pośrednie zostawiaj jako samo połączenie (w Excelu) albo z wyłączonym ładowaniem (w Power BI). Mniejszy plik, szybsze odświeżanie.
• Filtruj i usuwaj kolumny jak najwcześniej. Każdy kolejny krok pracuje na mniejszych danych, a przy bazach danych wczesne filtry zostaną wykonane przez serwer.
• Typy danych ustawiaj świadomie. Automatyczny krok zmiany typu tuż po źródle zapisuje nazwy wszystkich kolumn i psuje się przy pierwszej zmianie w źródle. W Excelu wyłączysz go w Opcjach dodatku Query (grupa Ładowanie danych, ustawienie wykrywania typów).
• Nie zmieniaj plików źródłowych ręcznie. Poprawka zrobiona ręcznie w eksporcie zniknie przy następnym eksporcie. Każdą poprawkę zapisuj jako krok w Power Query.
• Odwołanie zamiast duplikatu, jeśli kilka zapytań korzysta z tego samego oczyszczonego źródła.
• Parametry zamiast ścieżek wpisanych na sztywno. Przeniesienie raportu do innego folderu albo na inny serwer to wtedy zmiana jednej wartości.
• Testuj liczby. Po każdej większej zmianie porównaj liczbę wierszy i sumy kontrolne z poprzednim wynikiem. Komunikat zgodności w oknie scalania i profil całego zestawu danych to darmowe testy jakości.

Skróty klawiszowe

Podsumowanie: od eksportu do automatu

Przeszliśmy całą drogę na jednym przykładzie. W Excelu połączyliśmy cztery miesięczne eksporty z folderu, usunęliśmy wiersze techniczne, poprawiliśmy tekst i duplikaty, dociągnęliśmy dane produktów scalaniem, dołączyliśmy nowe sklepy, dodaliśmy kolumny wyliczane, pogrupowaliśmy sprzedaż, anulowaliśmy przestawienie budżetu i policzyliśmy realizację planu. Plik za maj dołączył się sam po jednym odświeżeniu. Potem te same zapytania przenieśliśmy do Power BI Desktop, podłączyliśmy bazę SQL Server i sprawdziliśmy składanie zapytań, a na końcu zbudowaliśmy Dataflow Gen2 w Fabric, który zapisuje wynik do Lakehouse według harmonogramu.

Najważniejsza myśl z całego kursu: w Power Query nie poprawiasz danych, tylko zapisujesz przepis na ich poprawienie. Dlatego każdy kolejny miesiąc kosztuje Cię jedno kliknięcie zamiast godzin ręcznej pracy, a ten sam przepis zadziała w Excelu, w Power BI i w Fabric.

Szkolenie Microsoft Excel - Power Query --> Dwa dni warsztatów z Excela: pobieranie danych z plików, folderów, SharePointa i baz SQL, ich czyszczenie i łączenie w Power Query, praca w języku M, a na koniec model danych i oparta na nim tabela przestawna. Prowadzi Sebastian Stasiak.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Szkolenie Power BI - Desktop, DAX i Online --> Pięć dni od pierwszego raportu do publikacji w usłudze Power BI: Power Query, model danych z wielu tabel, DAX, wizualizacje i Copilot. To szkolenie ma terminy gwarantowane.

To szkolenie może być dofinansowane dla Ciebie z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

✕