Tutorial: Membentuk dan menggabungkan data di Power BI Desktop

  • Artikel

Dengan Power BI Desktop, Anda dapat terhubung ke berbagai tipe sumber data, lalu membentuk data tersebut untuk memenuhi kebutuhan Anda, memungkinkan Anda membuat laporan visual untuk dibagikan dengan orang lain. Membentuk data berarti mengubah data: mengganti nama kolom atau tabel, mengubah teks menjadi Angka, menghapus baris, mengatur baris pertama sebagai header, dan seterusnya. Menggabungkan data berarti menyambungkan ke dua sumber data atau lebih, membentuknya sesuai kebutuhan, lalu mengonsolidasikannya ke dalam satu kueri.

Dalam tutorial ini, Anda akan mempelajari cara:

  • Membentuk data dengan menggunakan Editor Power Query.
  • Menghubungkan ke sumber data yang berbeda.
  • Menggabungkan sumber data tersebut dan membuat model data untuk digunakan dalam laporan.

Tutorial ini mendemonstrasikan cara membentuk kueri dengan menggunakan Power BI Desktop, menyoroti tugas yang paling umum. Kueri yang digunakan di sini dijelaskan secara lebih detail, termasuk cara membuat kueri dari awal, di Memulai dengan Power BI Desktop.

Editor Power Query di Power BI Desktop menggunakan menu klik kanan, dan Ubah pita. Sebagian besar dari apa yang bisa Anda pilih di pita juga tersedia dengan mengeklik kanan item, seperti kolom, dan memilih dari menu yang muncul.

Membentuk data

Untuk membentuk data dalam Editor Power Query, Anda memberikan instruksi langkah demi langkah bagi Editor Power Query untuk menyesuaikan data saat dimuat dan menyajikan data. Sumber data yang asli tidak terpengaruh; hanya tampilan data tertentu yang disesuaikan, atau dibentuk.

Langkah-langkah yang Anda tentukan (seperti mengganti nama tabel, mengubah tipe data, atau menghapus kolom) direkam oleh Editor Power Query. Setiap kali kueri ini terhubung ke sumber data, Editor Power Query melakukan langkah-langkah tersebut, sehingga data selalu berbentuk seperti yang Anda tentukan. Proses ini terjadi setiap kali Anda menggunakan Editor Power Query atau untuk siapa saja yang menggunakan kueri bersama Anda, seperti pada layanan Power BI. Langkah-langkah tersebut diambil, secara berurutan, di panel Kueri Pengaturan, di bawah LANGKAH YANG DITERAPKAN. Kita akan membahas masing-masing langkah tersebut dalam artikel ini.

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

  1. Impor data dari sumber web. Pilih menu dropdown Dapatkan data, lalu pilih Web.

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

  2. Tempelkan URL ini ke dialog Dari Web dan pilih 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. Dalam dialog Navigator, pilih Table 1, lalu pilih Ubah Data.

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

Tip

Beberapa informasi dalam tabel dari URL sebelumnya dapat berubah atau diperbarui sesekali. Akibatnya, Anda mungkin perlu menyesuaikan pilihan atau langkah-langkah dalam artikel ini.

  1. Jendela Editor Power Query terbuka. Anda dapat melihat langkah-langkah default yang diterapkan sejauh ini, di panel Kueri Pengaturan di bawah LANGKAH YANG DITERAPKAN.

    • Sumber: Menghubungkan ke situs web.
    • Tabel yang Diekstraksi dari Html: Memilih tabel.
    • Header yang Dipromosikan: Mengubah baris atas data menjadi header kolom.
    • Jenis yang Diubah: Mengubah jenis kolom, yang diimpor sebagai teks, ke jenis yang disimpulkan.

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

  2. Ubah nama tabel dari default Table 1 menjadi Retirement Data, lalu tekan Enter.

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

  3. Data yang ada diurutkan berdasarkan skor tertimbang, seperti yang dijelaskan pada halaman web sumber di bawah Metodologi. Mari kita tambahkan kolom kustom untuk menghitung skor yang berbeda. Kita kemudian akan mengurutkan tabel pada kolom ini untuk membandingkan peringkat skor kustom dengan Peringkat yang ada.

  4. Dari pita Tambahkan Kolom, pilih Kolom Kustom.

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

  5. Dalam dialog Kolom Kustom, di Nama kolom baru, masukkan Skor baru. Untuk rumus Kolom kustom, masukkan data berikut ini:

    ( [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. Pastikan pesan statusnya adalah Tidak ada kesalahan sintaks yang terdeteksi, lalu pilih OK.

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

  7. Di Pengaturan Kueri, daftar LANGKAH YANG DITERAPKAN sekarang memperlihatkan langkah Kustom Tambahan baru yang baru saja kita tentukan.

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

Menyesuaikan data

Sebelum kita bekerja dengan kueri ini, mari kita buat beberapa perubahan untuk menyesuaikan datanya:

  • Sesuaikan peringkat dengan menghapus kolom.

    Misalnya, asumsikan Cuaca bukan faktor dalam hasil kami. Menghapus kolom ini dari kueri tidak memengaruhi data lain.

  • Perbaiki kesalahan apa pun.

    Karena kita menghapus kolom, kita perlu menyesuaikan perhitungan kita di kolom Skor baru dengan mengubah rumusnya.

  • Urutkan data.

    Urutkan data berdasarkan kolom Skor baru, dan bandingkan dengan kolom Peringkat yang sudah ada.

  • Ganti data.

    Kami akan menyoroti cara mengganti nilai tertentu dan cara menyisipkan langkah yang diterapkan.

Perubahan ini dijelaskan dalam langkah-langkah berikut.

  1. Untuk menghapus kolom Cuaca , pilih kolom , pilih tab Beranda dari pita, lalu pilih Hapus Kolom.

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

    Catatan

    Nilai Skor baru belum berubah, karena urutan langkah-langkahnya. Editor Power Query merekam langkah-langkah secara berurutan, namun independen satu sama lain. Untuk menerapkan tindakan dalam urutan yang berbeda, Anda dapat memindahkan setiap langkah yang diterapkan ke atas atau ke bawah.

  2. Klik kanan langkah untuk melihat menu konteksnya.

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

  3. Naikkan langkah terakhir, Kolom yang Dihapus, ke tepat di atas langkah Kustom yang Ditambahkan.

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

  4. Pilih langkah Kustom yang Ditambahkan.

    Perhatikan kolom Skor baru sekarang memperlihatkan Kesalahan daripada nilai terhitung.

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

    Ada beberapa cara untuk mendapatkan informasi lebih lanjut tentang setiap kesalahan. Jika Anda memilih sel tanpa mengeklik kata Kesalahan, Editor Power Query akan menampilkan informasi kesalahan.

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

    Jika Anda memilih kata Kesalahan secara langsung, Editor Power Query akan membuat Langkah yang Diterapkan di panel Pengaturan Kueri dan menampilkan informasi tentang kesalahan tersebut. Karena kita tidak perlu menampilkan informasi kesalahan di tempat lain, pilih Batal.

  5. Untuk memperbaiki kesalahan, ada dua perubahan yang diperlukan, menghapus nama kolom Cuaca dan mengubah pembavisor dari 8 menjadi 7. Anda dapat membuat perubahan ini dengan dua cara:

    1. Klik kanan langkah Kolom Kustom dan pilih Edit Pengaturan. Ini memunculkan dialog Kolom Kustom yang Anda gunakan untuk membuat kolom Skor baru. Edit rumus seperti yang dijelaskan sebelumnya, hingga terlihat seperti ini:

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

    2. Pilih kolom Skor baru, lalu tampilkan rumus data kolom dengan mengaktifkan kotak centang Bilah Rumus dari tab Tampilan.

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

      Edit rumus seperti yang dijelaskan sebelumnya, hingga terlihat seperti ini, lalu tekan 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)

    Editor Power Query mengganti data dengan nilai yang direvisi dan langkah Kustom yang Ditambahkan selesai tanpa ada kesalahan.

    Catatan

    Anda juga dapat memilih Hapus Kesalahan, dengan menggunakan pita atau menu klik kanan, yang menghapus baris yang memiliki kesalahan. Namun, dalam tutorial ini kita ingin mempertahankan semua data dalam tabel.

  6. Urutkan data berdasarkan kolom Skor baru. Pertama, pilih langkah terakhir yang diterapkan, Tambahkan Kustom untuk menampilkan data terbaru. Lalu, pilih menu drop-down yang terletak di samping header kolom Skor baru dan pilih Urutkan Menurun.

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

    Data sekarang diurutkan sesuai dengan Skor baru. Anda dapat memilih langkah yang diterapkan di mana saja dalam daftar, dan melanjutkan membentuk data pada saat itu dalam urutan. Editor Power Query secara otomatis menyisipkan langkah baru langsung setelah langkah yang saat ini dipilih diterapkan.

  7. Di LANGKAH YANG DITERAPKAN, pilih langkah sebelum kolom kustom, yang merupakan langkah Kolom yang Dihapus. Di sini kita akan menggantikan nilai peringkat biaya Perumahan di Oregon. Klik kanan sel yang sesuai yang berisi nilai biaya Perumahan Oregon, lalu pilih Ganti Nilai. Perhatikan Langkah yang Diterapkan mana yang saat ini dipilih.

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

  8. Pilih Sisipkan.

    Karena kami menyisipkan langkah, Editor Power Query mengingatkan kami bahwa langkah-langkah berikutnya dapat menyebabkan kueri rusak.

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

  9. Ubah nilai data menjadi 100.0.

    Editor Power Query menggantikan data untuk Oregon. Saat Anda membuat langkah baru yang diterapkan, Editor Power Query menamainya berdasarkan tindakan, dalam hal ini, Nilai Yang Diganti. Jika Anda memiliki lebih dari satu langkah dengan nama yang sama dalam kueri Anda, Editor Power Query menambahkan jumlah yang meningkat ke setiap nama langkah yang diterapkan berikutnya.

  10. Pilih Langkah yang Diterapkan terakhir, yaitu Baris yang Disortir.

    Perhatikan bahwa data telah berubah mengenai peringkat baru Oregon. Perubahan ini terjadi karena kita menyisipkan langkah Nilai yang Diganti di lokasi yang benar, sebelum langkah Kustom yang Ditambahkan.

    Sekarang, kita telah membentuk data sejauh yang kita butuhkan. Selanjutnya, mari kita sambungkan ke sumber data lain dan gabungkan data.

Menggabungkan Data

Data tentang berbagai status menarik, dan akan berguna untuk membangun upaya dan kueri analisis lebih lanjut. Namun, sebagian besar data tentang status menggunakan singkatan dua huruf untuk kode status, bukan nama lengkap status. Kita membutuhkan cara untuk mengasosiasikan nama negara bagian dengan singkatannya.

Ada sumber data publik lain yang menyediakan asosiasi tersebut, tetapi membutuhkan cukup banyak pembentukan sebelum kita dapat menghubungkannya ke tabel penghentian kita. Untuk membentuk data, ikuti langkah-langkah berikut:

  1. Dari pita Beranda di Editor Power Query, pilih Sumber Baru > Web.

  2. Masukkan alamat situs web untuk singkatan negara bagian, https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations, kemudian pilih Hubungkan.

    Navigator akan menampilkan konten situs web.

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

  3. Pilih Kode dan singkatan untuk negara bagian AS, distrik federal, wilayah, dan wilayah lainnya.

    Tip

    Dibutuhkan sedikit pembentukan untuk memanjakan data tabel ini ke apa yang kita inginkan. Apakah ada cara yang lebih cepat atau lebih mudah untuk mencapai langkah-langkah berikut? Ya, kita dapat membuat hubungan antara kedua tabel dan membentuk data berdasarkan hubungan tersebut. Contoh langkah-langkah berikut berguna untuk dipelajari untuk bekerja dengan tabel. Namun, hubungan dapat membantu Anda menggunakan data dengan cepat dari beberapa tabel.

Untuk membentuk data, ikuti langkah-langkah berikut:

  1. Hapus baris teratas. Kita tidak membutuhkannya karena baris itu adalah hasil dari cara tabel halaman web dibuat. Dari pita Beranda, pilih Hapus Baris > Hapus Baris Atas.

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

    Dialog Hapus Baris Atas muncul. Tentukan 1 baris yang akan dihapus.

  2. Promosikan baris atas baru ke header dengan Gunakan Baris Pertama Sebagai Header dari tab Beranda , atau dari tab Transformasi di pita.

  3. Karena tabel Data Penghentian tidak memiliki informasi untuk Washington DC atau wilayah, kita perlu memfilternya dari daftar kita. Pilih nama dan status drop-down kolom region_1, lalu kosongkan semua kotak centang kecuali Status.

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

  4. Hapus semua kolom yang tidak perlu. Karena kita hanya memerlukan pemetaan setiap status ke singkatan dua huruf resminya (Nama dan status wilayah dan kolom ANSI ), kita dapat menghapus kolom lainnya. Pertama pilih Nama dan status kolom wilayah , lalu tahan tombol CTRL dan pilih kolom ANSI . Dari tab Beranda pada pita, pilih Hapus Kolom > Hapus Kolom Lain.

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

    Catatan

    Urutan langkah-langkah yang diterapkan dalam Editor Power Query penting, dan memengaruhi bagaimana data dibentuk. Penting juga untuk mempertimbangkan bagaimana satu langkah dapat berdampak pada langkah berikutnya. Misalnya, jika Anda menghapus langkah dari langkah yang diterapkan, langkah-langkah berikutnya mungkin tidak berulah seperti yang semula dimaksudkan.

    Catatan

    Saat Anda mengubah ukuran jendela Editor Power Query untuk memperkecil lebar, beberapa item pita diringkas untuk memanfaatkan ruang yang terlihat semaksimal mungkin. Saat Anda menambah lebar jendela Editor Power Query, item pita diperluas untuk memanfaatkan area pita yang ditingkatkan secara maksimal.

  5. Ganti nama kolom dan tabel. Ada beberapa cara untuk mengganti nama kolom: Pertama pilih kolom, lalu pilih Ganti Nama dari tab Transformasi pada pita, atau klik kanan dan pilih Ganti Nama. Gambar berikut menunjukkan kedua opsi, tetapi Anda hanya perlu memilih salah satunya.

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

  6. Ganti nama kolom menjadi Nama Negara Bagian dan Kode Negara Bagian. Untuk mengganti nama tabel, masukkan Kode Status Namadi panel Kueri Pengaturan.

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

Menggabungkan Kueri

Sekarang setelah kita membentuk tabel Kode Status seperti yang kita inginkan, mari kita gabungkan kedua tabel ini, atau kueri, menjadi satu. Karena tabel yang kita miliki sekarang adalah hasil dari kueri yang kita terapkan ke data, tabel tersebut sering disebut sebagai kueri.

Ada dua cara utama untuk menggabungkan kueri: menggabungkan dan menambahkan.

  • Untuk satu atau beberapa kolom yang ingin Anda tambahkan ke kueri lain, Anda menggabungkan kueri.
  • Untuk satu atau beberapa baris data yang ingin Anda tambahkan ke kueri yang sudah ada, Tambahkan kueri.

Dalam hal ini, kami ingin menggabungkan kueri:

  1. Dari panel kiri Editor Power Query, pilih kueri yang ingin Anda gabungkan dengan kueri lainnya. Dalam hal ini, ini adalah Data Penghentian.

  2. Pilih Gabungkan Kueri > Gabungkan Kueri dari tab Beranda pada pita.

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

    Anda mungkin diminta untuk mengatur tingkat privasi, untuk memastikan data digabungkan tanpa menyertakan atau mentransfer data yang tidak ingin Anda transfer.

    Jendela Gabungkan muncul. Jendela tersebut akan meminta Anda untuk memilih tabel mana yang ingin Anda gabungkan ke dalam tabel yang dipilih dan kolom yang cocok digunakan untuk penggabungan.

  3. Pilih Status dari tabel Data Penghentian, lalu pilih kueri Kode Status.

    Saat Anda memilih kolom yang cocok, tombol OK diaktifkan.

    Screenshot of Power Query Editor's Merge dialog.

  4. Pilih OK.

    Editor Power Query membuat kolom baru di akhir kueri, yang berisi konten tabel (kueri) yang digabungkan dengan kueri yang sudah ada. Semua kolom dari kueri yang digabungkan diringkas ke dalam kolom, tetapi Anda dapat Memperluas tabel dan menyertakan kolom mana pun yang Anda inginkan.

  5. Untuk memperluas tabel gabungan, dan memilih kolom mana yang akan disertakan, pilih ikon perluas ( ).

    Jendela Perluas muncul.

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

  6. Dalam hal ini, kita hanya menginginkan kolom Kode Negara Bagian. Pilih kolom itu, hapus Gunakan nama kolom asli sebagai prefiks, kemudian pilih OK.

    Jika kami telah meninggalkan kotak centang yang dipilih untuk Gunakan nama kolom asli sebagai awalan, kolom gabungan akan diberi nama Kode Status.Kode Status.

    Catatan

    Jika Anda ingin menjelajahi cara membawa tabel Kode Status, Anda dapat bereksperimen sedikit. Jika Anda tidak menyukai hasilnya, cukup hapus langkah tersebut dari daftar LANGKAH YANG DITERAPKAN di panel Kueri Pengaturan, dan kueri Anda kembali ke status sebelum menerapkan langkah Perluas tersebut. Anda dapat melakukan hal ini sebanyak mungkin yang Anda inginkan sampai proses perluasan terlihat seperti yang Anda inginkan.

    Kita sekarang memiliki satu kueri (tabel) yang menggabungkan dua sumber data, yang masing-masing dibentuk untuk memenuhi kebutuhan kita. Kueri ini dapat menjadi dasar untuk koneksi data yang menarik, seperti statistik biaya perumahan, kualitas hidup, atau tingkat kejahatan dalam keadaan apa pun.

  7. Untuk menerapkan perubahan Anda dan menutup Editor Power Query, pilih Tutup & Terapkan dari tab Pita beranda.

    Model semantik yang diubah muncul di Power BI Desktop, siap digunakan untuk membuat laporan.

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

Konten terkait

Untuk informasi selengkapnya tentang Power BI Desktop dan kemampuannya, lihat sumber daya berikut: