Najlepsze praktyki przepływu języka dodatku Power Query

W tym samouczku poznasz przepływ języka Power Query i niektóre z jego najlepszych rozwiązań. Dowiesz się również, jak łatwo przekształcać dane, aby uzyskać jak najlepsze wyniki. Transformacje danych zoptymalizują Twój raport i sprawią, że będzie wyglądał atrakcyjnie.

Spis treści

• Ustaw typy danych bez przepływu języka dodatku Power Query
  • Ustaw typy danych za pomocą karty Przekształć
  • Ustaw typy danych za pomocą nagłówka kolumny
  • Ustaw typy danych za pomocą ikon kolumn
• Ustaw typy danych za pomocą przepływu języka dodatku Power Query
  • Usuń niepotrzebne kolumny
  • Przypisz odpowiednie typy danych do kolumn i ogranicz wiersze
  • Skonsoliduj zbędne kroki w przepływie języka dodatku Power Query
• Organizuj zapytania, aby uzyskać lepszy przepływ języka Power Query
• Podsumowanie przepływu języka dodatku Power Query
• Wniosek

Ustaw typy danych bez przepływu języka dodatku Power Query

Absolutne minimum przekształceń, które należy zawsze wykonywać, to zmiana nazwy i ustawienie typów danych dla kolumn.

Typy danych służą do klasyfikowania wartości w M . Ikona obok nazwy kolumny pokazuje przypisany typ danych dla pola. W tej przykładowej tabeli obok nazw kolumn widać ikonę ABC123. Ta ikona reprezentuje typ danych Dowolny, który wskazuje, że typ danych nie został przypisany do tej kolumny. 

Poza książkami włączone jest automatyczne wykrywanie typu. Spowoduje to automatyczne wygenerowanie kroków typu Changed w zapytaniach. Nawet jeśli ta opcja jest włączona, zawsze upewnij się, że dodatek Power Query prawidłowo odgaduje typy danych dla każdej kolumny we wszystkich tabelach.

Aby włączyć lub wyłączyć to ustawienie, wykonaj następujące kroki.

W opcji Globalny możesz wybrać jedną z trzech dostępnych opcji Wykrywania typu .

W opcji Bieżący plik możesz przełączać opcje Wykrywania typu w zależności od ustawień globalnych.

Ustaw typy danych za pomocą karty Przekształć

Interfejs użytkownika oferuje wiele sposobów ustawiania typów danych dla kolumn. W sekcji Dowolna kolumna karty Przekształć znajdziesz przycisk Wykryj typ danych .

Jeśli wybierzesz wszystkie kolumny i klikniesz ten przycisk, automatycznie ustawi typy danych dla wszystkich kolumn. Ponieważ jednak skanuje tylko 200 pierwszych wierszy, nadal musisz sprawdzić, czy dodatek Power Query ma prawidłowy typ danych.

Jeśli wybierzesz kolumnę z tabeli, możesz zobaczyć jej typ danych w sekcji Dowolna kolumna.

Jeśli klikniesz przycisk rozwijany i wybierzesz Tekst, ikona w wybranej kolumnie zmieni się z ABC123 na tylko ABC .

Zobaczysz wtedy krok Zmieniony typ w okienku Zastosowane kroki.

Na karcie Strona główna znajdziesz również opcję ustawienia typu danych.

Ustaw typy danych za pomocą nagłówka kolumny

Innym sposobem jest kliknięcie nagłówka prawym przyciskiem myszy i wybranie opcji Zmień typ . Stamtąd możesz wybrać odpowiedni typ danych dla swoich kolumn.

Ustaw typy danych za pomocą ikon kolumn

Najczęstszym sposobem ustawiania typów danych jest kliknięcie ikony obok nazwy kolumny. Pokaże Ci wszystkie dostępne typy danych.

W przykładowej tabeli ustaw typ danych kolumny OrderDate na Date .

W zależności od typu danych wybranej kolumny dodatek Power Query udostępnia zestaw przekształceń i opcji, które mają zastosowanie do tego określonego typu danych na karcie Przekształć , Dodaj kolumnę i w sekcji Inteligentny filtr .

Jeśli więc wybierzesz kolumnę daty i przejdziesz do karty Przekształć, zobaczysz opcje pod przyciskiem Data.

Jeśli jednak wybierzesz kolumnę tekstową, opcje pod przyciskiem Data nie będą już dostępne.

W przypadku inteligentnego filtrowania kolumny tekstowe mają filtry tekstowe, a kolumny dat — filtry dat.

W następnej kolumnie zmień nazwę kolumny na Klucz klienta i typ danych na Liczba całkowita .

Następnie zmień typ danych w kolumnach Kanał, Kod waluty i Kod magazynu na Tekst.

Następnie zmień nazwę Indeksu Regionu Dostawy na Klucz Regionu Dostawy i jego typ danych na Whole Number . Następnie zrób to samo dla kolumn Product Description Index i Order Quantity .

Na koniec dla kolumn Cena jednostkowa, Suma wiersza i Koszt jednostkowy ustaw typ danych na Stała liczba dziesiętna .

Ustaw typy danych za pomocą przepływu języka dodatku Power Query

Ze względu na zmiany w kolumnach w okienku Zastosowane kroki utworzono wiele kroków. Problem polega na tym, że wielokrotne wykonywanie tego samego typu przekształceń sprawia, że ​​zapytanie o przykładową tabelę staje się nieefektywne. To jest coś, czego powinieneś unikać.

Aby zapytanie było wydajne, spróbuj utworzyć pojedynczy krok, stosując tę ​​konkretną transformację do wszystkich kolumn przed utworzeniem kolejnego kroku.

Zduplikuj zapytanie tabeli przykładowej i zmień jego nazwę Najlepsze praktyki . W okienku Zastosowane kroki kliknij prawym przyciskiem myszy pierwszą transformację wykonaną w poprzednim zapytaniu i wybierz opcję Usuń do końca . Następnie potwierdź usunięcie kroku w oknie dialogowym Usuń krok .

Ponadto, oto kilka najlepszych praktyk. Pierwszą rzeczą jest utworzenie parametrów zawierających lokalizację źródła danych. Ułatwia to rozwiązywanie problemów w przypadku zmiany nazwy pliku.

Aby utworzyć parametry, kliknij Zarządzaj parametrami na karcie Narzędzia główne i wybierz Nowy parametr .

Innym sposobem jest kliknięcie prawym przyciskiem myszy okienka Zapytanie i wybranie opcji Nowy parametr .

Następnie pojawi się okno dialogowe Zarządzaj parametrami . Nazwij parametr FileLocation i ustaw Typ na Tekst. W polu Sugerowane wartości ustaw listę wartości , aby móc wkleić ciąg i zmienić lub dodać wiele lokalizacji, między którymi można się przełączać.

Następnie przejdź do Eksploratora plików i wybierz swój plik. Skopiuj ścieżkę i wklej parametry. Po zakończeniu naciśnij OK .

Wróć do kwerendy Best Practice i kliknij krok Źródło w okienku Zastosowane kroki. Następnie zmień zakodowaną na stałe ścieżkę pliku na pasku formuły za pomocą FileLocation .

Usuń niepotrzebne kolumny

Usuń wszystkie niepotrzebne kolumny, aby zaoszczędzić miejsce i poprawić wydajność. Przynieś tylko potrzebne dane, ponieważ dodawanie tabel i kolumn jest znacznie łatwiejsze niż ich usuwanie.

Najłatwiej to zrobić, wykonując krok Wybierz kolumny na karcie Narzędzia główne. Jeśli klikniesz ten przycisk, pojawi się okno dialogowe, w którym możesz wybrać kolumny do zachowania. Powinieneś projektować i kształtować swoje tabele w określonym celu, aby jak najlepiej pasowały do ​​analizy, którą zamierzasz przeprowadzić.

Stamtąd usuń zaznaczenie kolumn, których nie potrzebujesz w tabeli. W przypadku tej przykładowej tabeli liczność kolumny OrderNumber jest wysoka. Najlepiej usunąć zaznaczenie tej kolumny, ponieważ wpływa ona na rozmiar pliku i ogólną wydajność.

Jeśli nie potrzebujesz danych o lokalizacji do analizy przykładowego zapytania, najlepiej też odznaczyć Indeks regionu dostawy . Wreszcie, ponieważ tabela zawiera już cenę jednostkową i ilość, kolumna Linia sumy nie jest potrzebna.

Po odznaczeniu kolumn naciśnij OK . Jeśli chcesz zmienić wybór kolumn, po prostu wyczyść ikonę koła zębatego obok nazwy kroku w okienku Zastosowane kroki.

Przypisz odpowiednie typy danych do kolumn i ogranicz wiersze

Następnie przypisz typy danych do wszystkich kolumn. Zaznacz wszystkie kolumny i kliknij opcję Wykryj typ danych na karcie Przekształć.

Detect Data Type automatycznie wykrywa typy danych w kolumnach na podstawie skanowania 200 pierwszych wierszy. Sprawdź więc i upewnij się, że Power Query ustawia prawidłowe typy danych.

Najlepszą praktyką jest ograniczenie liczby wierszy. Jeśli Twój rok finansowy rozpoczyna się 1 lipca, możesz pominąć dane z czerwca lub ustawić parametr.

Istnieje jednak haczyk, jeśli chcesz mieć możliwość zmiany wartości parametru w usłudze LuckyTemplates . Po opublikowaniu raportu wartości parametrów muszą być typu tekstowego lub dziesiętnego.

Aby sobie z tym poradzić, utwórz filtr danych, klikając przycisk rozwijany w kolumnie OrderDate. Następnie kliknij Filtry dat i wybierz Po .

W oknie dialogowym zmień pierwszy parametr na jest po lub równy, a następnie wprowadź datę. W tym przykładzie wprowadzono datę 1 lipca 2014 r.

Po naciśnięciu OK filtr zostanie zastosowany w Twojej tabeli. Następnie utwórz parametr i nadaj mu nazwę DatesFrom . Ustaw Typ na Tekst i wprowadź datę w parametrze Wartość bieżąca.

Teraz wróć do kwerendy Best Practice i zastąp wewnętrzną #date wartością Date.From(DatesFrom) .

Kliknięcie znaku wyboru bez funkcji Date.From spowoduje zwrócenie błędu. Dzieje się tak, ponieważ DatesFrom jest typem Text, podczas gdy pole ma typ Date. Funkcja Date.From konwertuje tekst na datę.

Zmień nazwy wszystkich kolumn, które nie będą ukryte w modelu danych. Nazwa musi być zwięzła, samoopisująca się i przyjazna dla użytkownika. Pamiętaj, że projektujesz model danych dla osób, które będą korzystać z Twojego raportu.

Skonsoliduj zbędne kroki w przepływie języka dodatku Power Query

Następną rzeczą do zrobienia jest skonsolidowanie zbędnych kroków (takich jak zmiana nazwy, usunięcie i zmiana typów danych kolumn). Co więcej, kroki takie jak zmiana kolejności kolumn to rzeczy, na które należy zwrócić uwagę, szukając zwolnień.

Tabele ładowane do modelu danych nigdy nie są wyświetlane w raporcie. To sprawia, że ​​kolejność kolumn nie ma znaczenia.

Inną najlepszą praktyką jest zmiana nazwy kroków w okienku Zastosowane kroki. Nazwy kroków są samoopisujące i są używane jako zmienne w kodzie M.

Nazwy zawierające spacje lub znaki specjalne są zapisywane w cudzysłowach. Oznacza to, że nazwy są ujęte w zestaw podwójnych cudzysłowów i mają przed sobą znak krzyżyka lub krzyżyka, co utrudnia odczytanie kodu M. Możesz pominąć spacje lub umieścić między nimi podkreślenie.

Dodawanie dodatkowych szczegółów dokumentacji poprzez umieszczanie komentarzy w oknie Edytora zaawansowanego jest również najlepszym rozwiązaniem w dodatku Power Query. Możesz to również zrobić w opisie właściwości Step . Są one wyświetlane jako adnotacje podpowiedzi, gdy najedziesz kursorem na krok z wykrzyknikiem w okienku Zastosowane kroki.

Wiedza o tym, dlaczego dokonałeś określonego wyboru w początkowej fazie rozwoju, jest niezwykle pomocna, gdy po pewnym czasie musisz ponownie odwiedzić plik. Aby dodać szczegóły dokumentacji, kliknij prawym przyciskiem myszy krok w okienku Zastosowane kroki i wybierz opcję Właściwości .

Pojawi się okno dialogowe Właściwości kroku , w którym możesz wpisać przyczynę filtrowania lub przekształcania.

Organizuj zapytania, aby uzyskać lepszy przepływ języka Power Query

Jedną z najczęstszych najlepszych praktyk w dodatku Power Query jest organizowanie zapytań . Utwórz foldery dla parametrów, funkcji, zapytań przemieszczania i zapytań, które zostaną załadowane do modelu danych. W tym przykładzie wybierz zapytania FileLocation i DatesFrom i kliknij je prawym przyciskiem myszy. Następnie wybierz Przenieś do grupy i kliknij Nowa grupa .

Następnie dodaj nazwę dla wybranych zapytań i naciśnij OK .

Po zgrupowaniu zapytań okienko zapytania będzie wyglądać następująco.

W przypadku wszystkich zapytań dotyczących przemieszczania pamiętaj, aby wyłączyć ładowanie, odznaczając opcję Włącz ładowanie.

Kolejną kwestią do omówienia w tym samouczku jest przepływ języka. Każdy krok w okienku Zastosowane kroki przekształca wartość, którą można zobaczyć po ich kliknięciu.

Początkowe dane pojawiły się i rozpoczęły w kroku Nawigacja, a kolumny zostały wybrane. Następnie zmieniono typy danych i ustalono zakres dat. Zmieniono również nazwy kolumn.

Wszystkie kroki zwracają wartość typu tabeli. Jeśli otworzysz okno Edytora zaawansowanego, zobaczysz wyrażenie let i klauzulę in . Pomiędzy nimi znajduje się lista kroków lub nazw zmiennych z przypisanymi im wyrażeniami.

Zapytanie zwraca wszystko, co następuje po klauzuli in , która odnosi się do ostatniego kroku na liście zmiennych. Silnik M będzie następnie podążał za łańcuchem zależności z powrotem od klauzuli in , aby wyeliminować wszystko, co niepotrzebne i, jeśli to możliwe, wypchnąć transformacje z powrotem do źródła.

Podsumowanie przepływu języka dodatku Power Query

Jeśli spojrzysz na formułę, zobaczysz przepływ kroków wykonanych w zapytaniu. Zobaczysz również funkcje używane przez interfejs użytkownika w każdym kroku.

Pierwszym krokiem, który został wykonany w zapytaniu, był wybór kolumny. Gdy krok został wykonany przy użyciu interfejsu użytkownika, została wywołana funkcja Table.SelectColumns . Jako pierwszy parametr przyjął tabelę, która odwołuje się do nazwy zmiennej z poprzedniego kroku. Następnie wymienił wszystkie wybrane nazwy kolumn.

Drugi krok przekształcił typy kolumn, wywołując funkcję Table.TransformColumnTypes . Jego pierwszy parametr zwany wynikiem poprzedniego kroku. Następnie wymieniono zestaw list transformacji.

W trzecim kroku ustaw filtr zakresu dat za pomocą funkcji Table.SelectRows . Jako pierwszy argument przyjął zapytanie typu tabela. W tym przykładzie odwołuje się do danych wyjściowych kroku zmiany typu.

W ostatnim kroku zmieniono nazwy kolumn za pomocą funkcji Table.RenameColumns . Dane wyjściowe z poprzedniego kroku zostały użyte jako pierwszy argument. Następnie wymienił zestaw list zmiany nazw.

Wszystkie funkcje stosowane w interfejsie użytkownika zaczynają się od słowa Tabela . Wszyscy przyjęli wartość tabeli jako pierwszy parametr, a następnie przekształcili tę wartość.

Chociaż kod wygląda na sekwencyjny, ponieważ każdy krok odwołuje się do poprzedniego kroku, sekwencja nie jest wymagana. Jeśli przesuniesz się o krok, zapytanie będzie nadal działać, ponieważ silnik M zawsze podąża za łańcuchem zależności.

Posiadanie interfejsu użytkownika jest wygodne, ale zawsze zakłada, że ​​chcesz przekształcić wyniki poprzedniej transformacji. W większości przypadków prawdopodobnie będzie to prawda, a jeśli tak nie jest, będziesz musiał ręcznie zaktualizować tę wartość odniesienia w kodzie.

Power Query Typy danych i łączniki
Zestawy danych usługi LuckyTemplates: typy i konwencje nazewnictwa
Ładowanie i przekształcanie danych Najlepsze praktyki

Wniosek

Transformacja danych jest konieczna, aby dane były pogrupowane i uporządkowane. Przyspiesza opracowywanie danych, ponieważ można łatwo śledzić problemy w przepływie języka Power Query i modyfikować zmiany w raporcie.

Melisa