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

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.
• Nawiązywanie połączenia z różnymi źródłami danych.
• Połącz te źródła danych i utwórz model danych do użycia w raportach.

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.

Edytor Power Query rejestruje określone kroki (takie jak zmiana nazwy tabeli, przekształcanie typu danych lub usuwanie kolumny). 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 Ustawienia zapytania w obszarze ZASTOSOWANE KROKI. Omówimy poszczególne kroki opisane w tym artykule.

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

   

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

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

   

3. W oknie dialogowym Nawigator zaznacz pole wyboru dla wpisu rozpoczynającego się od Individual factor scores, a następnie wybierz pozycję Przekształć dane.

   

   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.

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

   • Źródło: Nawiązywanie połączenia z witryną internetową.
   • 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.

   

5. Zmień nazwę tabeli z domyślnej Individual factor scores... na Retirement Data, a następnie naciśnij Enter.

   

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

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

   

8. 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] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [Weather] + [#"Non-housing cost of living"] ) / 7
   

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

   

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

    

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.

   

   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.

   

3. Wybierz pozycję Przenieś przed z menu kontekstowego, aby przejść w górę do ostatniego kroku Usunięto kolumny, aby tuż nad krokiem Dodano kolumny niestandardowe. Możesz również użyć myszy, aby przenieść krok do żądanej pozycji.

   

4. Wybierz krok Dodano niestandardowy.

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

   

   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.

   

   Jeśli wybierzesz bezpośrednio słowo Błąd, Edytor Power Query utworzy zastosowany krok w okienku Ustawienia zapytania 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 7 do 6. Te zmiany można wprowadzić na dwa sposoby:

   1. Kliknij prawym przyciskiem myszy krok Dodano kolumnę Niestandardowa i wybierz pozycję Edytuj ustawienia lub kliknij ikonę koła zębatego obok nazwy kroku, aby wyświetlić okno dialogowe 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:

      

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

      

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

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [#"Non-housing cost of living"] ) / 6)
      

   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.

   

   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 mieszkań w Oregonie. Kliknij prawym przyciskiem myszy odpowiednią komórkę zawierającą wartość Koszt mieszkania w stanie Oregon, a następnie wybierz polecenie Zamień wartości. Zwróć uwagę, który zastosowany krok jest obecnie wybrany.

   

8. Wybierz pozycję Wstaw.

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

   

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, aby uzyskać skróty stanów, https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations, a następnie wybierz przycisk OK.

   Nawigator wyświetla zawartość witryny internetowej.

   

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>.

   

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

2. Ponieważ tabela Dane wycofania nie zawiera informacji dotyczących waszyngtonu lub terytoriów, musimy je filtrować z naszej listy. Wybierz listę rozwijaną kolumny Stan regionu, a następnie wyczyść wszystkie pola wyboru z wyjątkiem stanu i stanu (oficjalnie Commonwealth).

   

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

   

   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.

4. 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.

   

5. Zmień nazwy kolumn na State Name (Nazwa stanu) i State Code (Kod stanu). Aby zmienić nazwę tabeli, wprowadź state codes w polu Nazwa w okienku Ustawienia zapytania.

   

Łą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.

   

   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ącej kolumny przycisk OK jest włączony.

   

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ń.

   

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 Codes.

   Uwaga

   Jeśli chcesz dowiedzieć się, jak wprowadzić tabelę State Codes , możesz poeksperymentować nieco. Jeśli wyniki nie są podobne, po prostu usuń ten krok z listy ZASTOSOWANE KROKI w okienku Ustawienia zapytania, 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.

Powiązana zawartość

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

• Co to jest program Power BI Desktop?
• Omówienie zapytań w Power BI Desktop
• Źródła danych w programie Power BI Desktop
• Łączenie ze źródłami danych w programie Power BI Desktop
• Wykonywanie typowych zadań zapytań w programie Power BI Desktop