Samouczek: kształtowanie i łączenie danych w programie Power BI Desktop

  • Artykuł

W programie Power BI Desktop możesz łączyć się z wieloma różnymi typami źródeł danych, a następnie kształtować dane zgodnie z potrzebami, umożliwiając tworzenie raportów wizualnych do udostępniania innym osobom. Kształtowanie danych oznacza przekształcanie danych: zmienianie nazw kolumn lub tabel, zmienianie tekstu na liczby, usuwanie wierszy, ustawianie pierwszego wiersza jako nagłówków itd. Łączenie danych oznacza łączenie z co najmniej dwoma źródłami danych, kształtowanie ich w razie potrzeby, a następnie konsolidowanie ich w jednym zapytaniu.

Z tego samouczka dowiesz się, jak wykonywać następujące czynności:

  • Kształtowanie danych przy użyciu Edytor Power Query.
  • Połączenie do różnych źródeł danych.
  • Połącz te źródła danych i utwórz model danych do użycia w raportach.

W tym samouczku pokazano, jak kształtować zapytanie przy użyciu programu Power BI Desktop, wyróżniając najbardziej typowe zadania. Zapytanie użyte w tym miejscu zostało opisane bardziej szczegółowo, w tym sposób tworzenia zapytania od podstaw w temacie Wprowadzenie do programu Power BI Desktop.

Edytor Power Query w programie Power BI Desktop używa menu prawym przyciskiem myszy i Przekształć wstążkę. Większość elementów, które można wybrać na wstążce, jest również dostępna, klikając prawym przyciskiem myszy element, taki jak kolumna, i wybierając z wyświetlonego menu.

Kształtowanie danych

Aby kształtować dane w Edytor Power Query, należy podać instrukcje krok po kroku dla Edytor Power Query, aby dostosować dane podczas ładowania i prezentowania danych. Nie ma to wpływu na oryginalne źródło danych; tylko ten konkretny widok danych jest dostosowywany lub kształtowany.

Określone kroki (takie jak zmiana nazwy tabeli, przekształcanie typu danych lub usuwanie kolumny) są rejestrowane przez Edytor Power Query. Za każdym razem, gdy to zapytanie łączy się ze źródłem danych, Edytor Power Query wykonuje te kroki, aby dane były zawsze kształtowane w określony sposób. Ten proces odbywa się za każdym razem, gdy używasz Edytor Power Query lub dla każdego, kto korzysta z udostępnionego zapytania, takiego jak w usługa Power BI. Te kroki są przechwytywane sekwencyjnie w okienku Zapytanie Ustawienia w obszarze ZASTOSOWANE KROKI. Omówimy poszczególne kroki opisane w tym artykule.

Screenshot of Power Query Editor with the Query Settings pane and Applied steps list.

  1. Zaimportuj dane ze źródła internetowego. Wybierz listę rozwijaną Pobierz dane, a następnie wybierz pozycję Sieć Web.

    Screenshot of Power Query Editor with the Get data menu and Web source selected.

  2. Wklej ten adres URL do okna dialogowego Z sieci Web i wybierz przycisk OK.

    https://www.fool.com/research/best-states-to-retire

    Screenshot of Power Query Editor's From Web dialog with the source page's URL entered.

  3. W oknie dialogowym Nawigator wybierz pozycję Table 1, a następnie wybierz pozycję Przekształć dane.

    Screenshot of Power Query Editor's Navigator dialog with HTML Table 1 selected and the Transform Data button highlighted.

Napiwek

Niektóre informacje w tabelach z poprzedniego adresu URL mogą ulec zmianie lub być od czasu do czasu aktualizowane. W związku z tym może być konieczne odpowiednie dostosowanie zaznaczeń lub kroków w tym artykule.

  1. Zostanie otwarte okno Edytor Power Query. Domyślne kroki zastosowane do tej pory można zobaczyć w okienku Zapytanie Ustawienia w obszarze ZASTOSOWANE KROKI.

    • Źródło: Połączenie do witryny internetowej.
    • Wyodrębniona tabela z kodu HTML: wybieranie tabeli.
    • Promowane nagłówki: zmiana górnego wiersza danych na nagłówki kolumn.
    • Zmieniono typ: zmiana typów kolumn, które są importowane jako tekst, na ich wywnioskowane typy.

    Screenshot of the Power Query Editor window with Query Settings highlighted.

  2. Zmień nazwę tabeli z domyślnej Table 1 na Retirement Data, a następnie naciśnij klawisz Enter.

    Screenshot of Power Query Editor showing how to edit a table name in Query Settings.

  3. Istniejące dane są uporządkowane według wyniku ważonego, zgodnie z opisem na źródłowej stronie internetowej w obszarze Metodologia. Dodajmy kolumnę niestandardową, aby obliczyć inny wynik. Następnie posortujemy tabelę w tej kolumnie, aby porównać klasyfikację wyników niestandardowych z istniejącą rangą.

  4. Na wstążce Dodawanie kolumny wybierz pozycję Kolumna niestandardowa.

    Screenshot of Power Query Editor's Add Column ribbon with the Custom Column button highlighted.

  5. W oknie dialogowym Kolumna niestandardowa w polu Nazwa nowej kolumny wprowadź nowy wynik. W polu Formuła kolumny Niestandardowe wprowadź następujące dane:

    ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8

  6. Upewnij się, że komunikat o stanie to Nie wykryto błędów składniowych, a następnie wybierz przycisk OK.

    Screenshot of Power Query Editor's Custom Column dialog showing the new column name, custom column formula, and no syntax errors.

  7. W obszarze Zapytanie Ustawienia lista ZASTOSOWANE KROKI zawiera teraz nowy krok Dodany niestandardowy, który właśnie zdefiniowaliśmy.

    Screenshot of Power Query Editor's Query Settings pane showing the Applied Steps list with the actions so far.

Dostosowywanie danych

Zanim będziemy pracować z tym zapytaniem, wprowadźmy kilka zmian w celu dostosowania jego danych:

  • Dostosuj klasyfikacje, usuwając kolumnę.

    Załóżmy na przykład, że pogoda nie jest czynnikiem w naszych wynikach. Usunięcie tej kolumny z zapytania nie ma wpływu na inne dane.

  • Napraw wszelkie błędy.

    Ponieważ usunęliśmy kolumnę, musimy dostosować obliczenia w kolumnie Nowy wynik , zmieniając jej formułę.

  • Sortuj dane.

    Posortuj dane na podstawie kolumny Nowy wynik i porównaj je z istniejącą kolumną Ranga .

  • Zastąp dane.

    Podkreślimy, jak zastąpić określoną wartość i jak wstawić zastosowany krok.

Te zmiany zostały opisane w poniższych krokach.

  1. Aby usunąć kolumnę Pogoda , wybierz kolumnę, wybierz kartę Narzędzia główne na wstążce, a następnie wybierz pozycję Usuń kolumny.

    Screenshot of Power Query Editor's Home menu with the Remove Columns button highlighted.

    Uwaga

    Nowe wartości wyników nie zostały zmienione ze względu na kolejność kroków. Edytor Power Query rejestruje kroki sekwencyjnie, ale niezależnie od siebie. Aby zastosować akcje w innej sekwencji, można przenieść każdy zastosowany krok w górę lub w dół.

  2. Kliknij prawym przyciskiem myszy krok, aby wyświetlić menu kontekstowe.

    Screenshot of Power Query Editor's Applied Steps context menu.

  3. Przenieś w górę ostatni krok Usunięto kolumny, aby tuż nad krokiem Dodano kolumny niestandardowe .

    Screenshot of Power Query Editor's Applied Steps list with the Removed Columns step now moved above the Custom Column step.

  4. Wybierz krok Dodano niestandardowy.

    Zwróć uwagę, że w kolumnie Nowy wynik jest teraz wyświetlany błąd , a nie obliczona wartość.

    Screenshot of Power Query Editor and the New score column containing Error values.

    Istnieje kilka sposobów uzyskania dodatkowych informacji na temat każdego błędu. Jeśli wybierzesz komórkę bez kliknięcia słowa Błąd, Edytor Power Query wyświetli informacje o błędzie.

    Screenshot of Power Query Editor showing the New score column with Error details.

    Jeśli wybierzesz słowo Błąd bezpośrednio, Edytor Power Query utworzy zastosowany krok w okienku zapytanie Ustawienia i wyświetli informacje o błędzie. Ponieważ nie musimy wyświetlać informacji o błędach nigdzie indziej, wybierz pozycję Anuluj.

  5. Aby naprawić błędy, potrzebne są dwie zmiany, usunięcie nazwy kolumny Weather i zmiana dzielnika z zakresu od 8 do 7. Te zmiany można wprowadzić na dwa sposoby:

    1. Kliknij prawym przyciskiem myszy krok Kolumna niestandardowa i wybierz polecenie Edytuj Ustawienia. Spowoduje to wyświetlenie okna dialogowego Kolumna niestandardowa użyta do utworzenia kolumny Nowy wynik . Edytuj formułę zgodnie z wcześniejszym opisem, dopóki nie będzie wyglądać następująco:

      Screenshot of Power Query Editor's Custom Column dialog with formula errors fixed.

    2. Wybierz kolumnę Nowy wynik , a następnie wyświetl formułę danych kolumny, włączając pole wyboru Pasek formuły na karcie Widok .

      Screenshot of Power Query Editor showing the New score column and its data formula with errors fixed.

      Edytuj formułę zgodnie z wcześniejszym opisem, dopóki nie będzie wyglądać tak, a następnie naciśnij klawisz Enter.

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)

    Edytor Power Query zastępuje dane poprawionymi wartościami i Dodano krok niestandardowy bez błędów.

    Uwaga

    Możesz również wybrać pozycję Usuń błędy, korzystając ze wstążki lub menu po kliknięciu prawym przyciskiem myszy, które usuwa wszystkie wiersze z błędami. Jednak w tym samouczku chcemy zachować wszystkie dane w tabeli.

  6. Posortuj dane na podstawie kolumny Nowy wynik . Najpierw wybierz ostatni zastosowany krok Dodano niestandardowy, aby wyświetlić najnowsze dane. Następnie wybierz listę rozwijaną znajdującą się obok nagłówka kolumny Nowy wynik i wybierz pozycję Sortuj malejąco.

    Screenshot of Power Query Editor showing the New score column with Sort Descending highlighted.

    Dane są teraz sortowane według nowego wyniku. Możesz wybrać zastosowany krok w dowolnym miejscu na liście i kontynuować kształtowanie danych w tym momencie w sekwencji. Edytor Power Query automatycznie wstawia nowy krok bezpośrednio po aktualnie wybranym kroku zastosowanym.

  7. W obszarze ZASTOSOWANE KROKI wybierz krok poprzedzający kolumnę niestandardową, czyli krok Usunięto kolumny . W tym miejscu zastąpimy wartość rankingu Kosztów mieszkaniowych w Oregonie. Kliknij prawym przyciskiem myszy odpowiednią komórkę zawierającą wartość kosztu mieszkania oregonu, a następnie wybierz polecenie Zamień wartości. Zwróć uwagę, który zastosowany krok jest obecnie wybrany.

    Screenshot of the Power Query Editor window showing the Housing cost column with the Replace Values right-click menu item highlighted.

  8. Wybierz pozycję Wstaw.

    Ponieważ wstawiamy krok, Edytor Power Query przypomina nam, że kolejne kroki mogą spowodować przerwanie zapytania.

    Screenshot of Power Query Editor's Insert Step verification dialog.

  9. Zmień wartość danych na 100.0.

    Edytor Power Query zastępuje dane dla oregonu. Podczas tworzenia nowego zastosowanego kroku Edytor Power Query nazwę na podstawie akcji, w tym przypadku zamieniono wartość. Jeśli masz więcej niż jeden krok o tej samej nazwie w zapytaniu, Edytor Power Query dołącza rosnącą liczbę do każdej kolejnej nazwy zastosowanego kroku.

  10. Wybierz ostatni zastosowany krok, posortowane wiersze.

    Zwróć uwagę, że dane zmieniły się w odniesieniu do nowego rankingu Oregonu. Ta zmiana występuje, ponieważ wstawiliśmy krok Zamieniono wartość w prawidłowej lokalizacji przed krokiem Dodano niestandardową.

    Teraz ukształtowaliśmy nasze dane w zakresie, w jakim musimy. Następnie połączmy się z innym źródłem danych i połączmy dane.

Łączenie danych

Dane dotyczące różnych stanów są interesujące i będą przydatne do tworzenia dalszych działań i zapytań analizy. Jednak większość danych dotyczących stanów używa dwuliterowego skrótu kodów stanów, a nie pełnej nazwy stanu. Potrzebujemy sposobu skojarzenia nazw stanów z ich skrótami.

Istnieje inne publiczne źródło danych, które zapewnia to skojarzenie, ale wymaga odpowiedniego kształtowania, zanim będziemy mogli połączyć je z naszą tabelą wycofania. Aby ukształtować dane, wykonaj następujące kroki:

  1. Na wstążce Narzędzia główne w Edytor Power Query wybierz pozycję Nowa źródłowa > sieć Web.

  2. Wprowadź adres witryny internetowej pod kątem skrótów stanów , https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviationsa następnie wybierz pozycję Połączenie.

    Nawigator wyświetla zawartość witryny internetowej.

    Screenshot of Power Query Editor's Navigator page showing the Codes and abbreviations table selected.

  3. Wybierz pozycję Kody i skróty stanów USA, okręgu federalnego, terytoriów i innych regionów.

    Napiwek

    Analizowanie danych tej tabeli w dół do tego, czego chcemy, zajmie trochę kształtowania. Czy istnieje szybszy lub łatwiejszy sposób wykonania poniższych kroków? Tak, możemy utworzyć relację między dwiema tabelami i kształtować dane na podstawie tej relacji. Poniższe przykładowe kroki są przydatne do nauki pracy z tabelami. Jednak relacje mogą pomóc w szybkim użyciu danych z wielu tabel.

Aby uzyskać dane do kształtu, wykonaj następujące kroki:

  1. Usuń górny wiersz. Ponieważ jest to wynik sposobu utworzenia tabeli strony internetowej, nie potrzebujemy jej. Na wstążce Narzędzia główne wybierz pozycję Usuń wiersze Usuń pierwsze wiersze>.

    Screenshot of Power Query Editor highlighting the Remove Rows dropdown and the Remove Top Rows item.

    Zostanie wyświetlone okno dialogowe Usuwanie pierwszych wierszy . Określ 1 wiersz do usunięcia.

  2. Podwyższ poziom nowego wiersza górnego do nagłówków za pomocą opcji Użyj nagłówków Pierwszy wiersz jako z karty Narzędzia główne lub na karcie Przekształć na wstążce.

  3. Ponieważ tabela Dane wycofania nie zawiera informacji dotyczących waszyngtonu lub terytoriów, musimy je filtrować z naszej listy. Wybierz listę rozwijaną Pola wyboru Nazwa i stan kolumny region_1 , a następnie wyczyść wszystkie pola wyboru z wyjątkiem stanu.

    Screenshot of Power Query Editor showing a column filter with only the State value selected.

  4. Usuń wszystkie niepotrzebne kolumny. Ponieważ potrzebujemy tylko mapowania każdego stanu na jego oficjalny dwuliterowy skrót (Nazwa i stan kolumn regionów i ANSI ), możemy usunąć inne kolumny. Najpierw wybierz kolumnę Nazwa i stan regionu , a następnie przytrzymaj naciśnięty klawisz CTRL i wybierz kolumnę ANSI . Na karcie Narzędzia główne na wstążce wybierz pozycję Usuń kolumny > Usuń inne kolumny.

    Screenshot of Power Query Editor highlighting the Remove Columns dropdown and the Remove Other Columns item.

    Uwaga

    Sekwencja zastosowanych kroków w Edytor Power Query jest ważna i wpływa na kształt danych. Ważne jest również, aby rozważyć, jak jeden krok może mieć wpływ na kolejny krok. Jeśli na przykład usuniesz krok z zastosowanych kroków, kolejne kroki mogą nie zachowywać się zgodnie z oczekiwaniami.

    Uwaga

    Po zmianie rozmiaru okna Edytor Power Query, aby zmniejszyć szerokość, niektóre elementy wstążki są skondensowane, aby jak najlepiej wykorzystać widoczne miejsce. Po zwiększeniu szerokości okna Edytor Power Query elementy wstążki rozszerzają się, aby jak najlepiej wykorzystać zwiększony obszar wstążki.

  5. Zmień nazwy kolumn i tabeli. Istnieje kilka sposobów zmiany nazwy kolumny: Najpierw wybierz kolumnę, a następnie wybierz polecenie Zmień nazwę na karcie Przekształć na wstążce lub kliknij prawym przyciskiem myszy i wybierz polecenie Zmień nazwę. Na poniższej ilustracji przedstawiono obie opcje, ale wystarczy wybrać jedną z nich.

    Screenshot of Power Query Editor highlighting the Rename button and also the Rename right-click item.

  6. Zmień nazwy kolumn na State Name (Nazwa stanu) i State Code (Kod stanu). Aby zmienić nazwę tabeli, wprowadź kody stanów nazww okienku Zapytanie Ustawienia.

    Screenshot of Power Query Editor window showing the results of shaping state codes source data into a table.

Łączenie zapytań

Teraz, gdy ukształtowaliśmy tabelę State Codes w żądany sposób, połączmy te dwie tabele lub zapytania w jedną. Ponieważ tabele, które teraz mamy, są wynikiem zapytań zastosowanych do danych, są one często określane jako zapytania.

Istnieją dwa podstawowe sposoby łączenia zapytań: scalanie i dołączanie.

  • W przypadku co najmniej jednej kolumny, którą chcesz dodać do innego zapytania, scalisz zapytania.
  • W przypadku co najmniej jednego wiersza danych, które chcesz dodać do istniejącego zapytania, dołącz zapytanie.

W tym przypadku chcemy scalić zapytania:

  1. W lewym okienku Edytor Power Query wybierz zapytanie, do którego ma zostać scalone inne zapytanie. W takim przypadku są to dane o wycofaniu.

  2. Wybierz pozycję Scal zapytania scalania zapytań > na karcie Narzędzia główne na wstążce.

    Screenshot of Power Query Editor's Merge Queries dropdown with the Merge Queries item highlighted.

    Może zostać wyświetlony monit o ustawienie poziomów prywatności, aby upewnić się, że dane są łączone bez dołączania lub przesyłania danych, których nie chcesz przesyłać.

    Zostanie wyświetlone okno Scalanie . Zostanie wyświetlony monit o wybranie tabeli, którą chcesz scalić z wybraną tabelą, oraz pasujących kolumn do użycia na potrzeby scalania.

  3. Wybierz pozycję State (Stan ) z tabeli Retirement Data (Dane wycofania), a następnie wybierz zapytanie State Codes (Kody stanów).

    Po wybraniu pasujących kolumn przycisk OK jest włączony.

    Screenshot of Power Query Editor's Merge dialog.

  4. Wybierz przycisk OK.

    Edytor Power Query tworzy nową kolumnę na końcu zapytania, która zawiera zawartość tabeli (zapytania), która została scalona z istniejącym zapytaniem. Wszystkie kolumny ze scalonego zapytania są skondensowane do kolumny, ale możesz rozwinąć tabelę i dołączyć dowolne kolumny.

  5. Aby rozwinąć scaloną tabelę i wybrać kolumny do uwzględnienia, wybierz ikonę rozwijania ( ).

    Zostanie wyświetlone okno Rozwiń.

    Screenshot of Power Query Editor's column Expand dialog showing the State Code column highlighted.

  6. W tym przypadku chcemy tylko kolumny State Code . Wybierz tę kolumnę, wyczyść pole Wyboru użyj oryginalnej nazwy kolumny jako prefiksu, a następnie wybierz przycisk OK.

    Jeśli zaznaczono pole wyboru Użyj oryginalnej nazwy kolumny jako prefiksu, scalona kolumna będzie miała nazwę State Codes.State Code.

    Uwaga

    Jeśli chcesz dowiedzieć się, jak wprowadzić tabelę State Codes , możesz poeksperymentować nieco. Jeśli nie podobają Ci się wyniki, po prostu usuń ten krok z listy ZASTOSOWANE KROKI w okienku Zapytanie Ustawienia, a zapytanie powróci do stanu przed zastosowaniem tego kroku Rozwiń. Możesz to zrobić tyle razy, ile chcesz, dopóki proces rozwijania nie będzie wyglądał tak, jak chcesz.

    Mamy teraz jedno zapytanie (tabela), które łączy dwa źródła danych, z których każdy został ukształtowany zgodnie z naszymi potrzebami. To zapytanie może być podstawą dla interesujących połączeń danych, takich jak statystyki kosztów mieszkaniowych, jakość życia lub wskaźnik przestępczości w dowolnym stanie.

  7. Aby zastosować zmiany i zamknąć Edytor Power Query, wybierz pozycję Zamknij i zastosuj na karcie wstążki Narzędzia główne.

    Przekształcony model semantyczny jest wyświetlany w programie Power BI Desktop, gotowy do użycia do tworzenia raportów.

    Screenshot of Power Query Editor's Close & Apply button.

Powiązana zawartość

Aby uzyskać więcej informacji na temat programu Power BI Desktop i jego możliwości, zobacz następujące zasoby: