Mengkombinasikan data dari beberapa sumber data (Power Query)

Catatan: Power Query dikenal sebagai Dapatkan & Transformasikan di Excel 2016. Informasi yang disediakan di sini berlaku untuk keduanya. Untuk mempelajari selengkapnya, lihat Dapatkan & Transformasikan di Excel 2016.

Dalam tutorial ini, Anda akan menggunakan Editor Kueri Power Query untuk mengimpor data dari file Excel lokal yang berisi informasi produk, dan dari umpan OData yang berisi informasi pesanan produk. Anda melakukan langkah-langkah transformasi dan agregasi, serta menggabungkan data dari kedua sumber tersebut untuk menghasilkan laporan Total Penjualan per Produk dan Tahun.

Untuk menjalankan tutorial ini, Anda memerlukan buku kerja Produk dan Pesanan. Dalam kotak dialog Simpan Sebagai, beri nama file Produk dan Pesanan.xlsx.

Dalam tutorial ini

Tugas 1: Mengimpor produk ke dalam buku kerja Excel

Langkah 1: Menyambungkan ke buku kerja Excel

Langkah 2: Menaikkan baris pertama ke header kolom tabel

Langkah 3: Menghapus kolom lain agar hanya menampilkan kolom yang diminati

Langkah pembuatan Power Query

Langkah 4: Mengimpor kueri produk

Tugas 2: Mengimpor data pesanan dari umpan OData

Langkah 1: Menyambungkan ke umpan OData

Langkah 2: Memperluas tabel Order_Details

Link Memperluas Tabel Order_Details

Langkah 3: Menghapus kolom lain agar hanya menampilkan kolom yang diminati

Menghapus kolom yang dipilih

Langkah 4: Menghitung total baris untuk setiap baris Order_Details

Menghitung total baris untuk setiap baris Order_Details

Langkah 5: Mentransformasi kolom tahun OrderDate

Langkah 6: Mengelompokkan baris berdasarkan ProductID dan Year

Langkah 7: Mengganti nama kueri

Hasil kueri final

Langkah pembuatan Power Query

Langkah 8: Menonaktifkan unduhan kueri ke dalam buku kerja Excel

Menonaktifkan pengunduhan kueri

Tugas 3: Mengkombinasikan kueri Products dan Total Sales

Langkah 1: Menggabungkan ProductID ke dalam kueri Total Sales

Langkah 2: Memperluas kolom gabungan

Link memperluas tabel NewColumn

Langkah pembuatan Power Query

Langkah 3: Memuat kueri Total Sales per Product ke Model Data Excel

Memuat kueri Total Sales per Product ke Model Data Excel

Kueri Total Sales per Product final

Tugas 1: Mengimpor produk ke dalam buku kerja Excel

Dalam tugas ini, Anda mengimpor produk dari file Produk dan Pesanan.xlsx ke dalam buku kerja Excel.

Langkah 1: Menyambungkan ke buku kerja Excel

  1. Membuat buku kerja Excel.

  2. Di tab pita POWER QUERY, klik Dari File > Dari Excel.

  3. Dalam kotak dialog telusuri Excel, telusuri atau ketikkan jalur Products and Orders.xlsx untuk mengimpor atau menautkan ke file.

  4. Di panel Navigator, klik ganda lembar kerja Products atau klik Products dan klik Edit Kueri. Saat Anda mengedit kueri, atau menyambungkan ke sumber data baru, jendela Editor Kueri muncul.

    Catatan: Untuk video yang sangat cepat tentang cara menampilkan Editor Kueri, lihat bagian akhir artikel ini.

Langkah 2: Menaikkan baris pertama ke header kolom tabel

Di kisi Pratinjau Kueri, baris pertama tabel tidak berisi nama kolom tabel. Untuk menaikkan baris pertama ke header kolom tabel:

  1. Klik ikon tabel ( Ikon tabel ) di sudut kiri atas pratinjau data.

  2. Klik Gunakan Baris Pertama sebagai Header.

Menaikkan baris pertama ke header kolom tabel

Langkah 3: Menghapus kolom lain agar hanya menampilkan kolom yang diminati

Dalam langkah ini Anda menghapus semua kolom kecuali ProductID, ProductName, CategoryID, dan QuantityPerUnit.

  1. Di kisi Pratinjau Kueri, pilih kolom ProductID, ProductName, CategoryID, dan QuantityPerUnit (gunakan Ctrl+Klik atau Shift+Klik).

  2. Di pita Editor Kueri, klik Hapus Kolom > Hapus Kolom Lain atau klik kanan di header kolom, dan klik Hapus Kolom Lain.

    Menyembunyikan kolom lain

Langkah pembuatan Power Query

Saat Anda melakukan aktivitas kueri di Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, di daftar LANGKAH yang DITERAPKAN. Setiap langkah kueri memiliki rumus Power Query yang berkaitan, juga dikenal sebagai bahasa "M". Untuk informasi selengkapnya tentang bahasa rumus Power Query, lihat Mempelajari tentang rumus Power Query.

Tugas

Langkah kueri

Rumus

Menyambungkan ke buku kerja Excel

Sumber

Source{[Name="Products"]}[Data]

Menaikkan baris pertama ke header kolom tabel

FirstRowAsHeader

Tabel.PromoteHeaders

(Products)

Menghapus kolom lain agar hanya menampilkan kolom yang diminati

RemovedOtherColumns

Table.SelectColumns

(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Langkah 4: Mengimpor kueri produk

Dalam langkah ini, Anda mengimpor kueri Produk ke dalam buku kerja Excel Anda.

  1. Di pita Editor Kueri, klik Terapkan & Tutup. Hasilnya muncul dalam lembar kerja Excel baru.

Atas Halaman

Tugas 2: Mengimpor data pesanan dari umpan OData

Dalam tugas ini, Anda mengimpor data ke dalam buku kerja Excel dari sampel umpan OData Northwind di http://services.odata.org/Northwind/Northwind.svc.

Langkah 1: Menyambungkan ke umpan OData

  1. Di tab pita POWER QUERY, klik Dari Sumber Lain > Dari Umpan OData.

  2. Dalam kotak dialog Umpan OData, masukkan URL untuk umpan OData Northwind.

  3. Klik OK.

  4. Di panel Navigator, klik ganda tabel Orders atau klik Orders dan klik Edit.

Catatan: Saat Anda mengarahkan mouse Anda ke atas sebuah tabel, Anda akan melihat pratinjau tabel terbang keluar.

Arahkan mouse ke atas Sumber Data

Langkah 2: Memperluas tabel Order_Details

Dalam langkah ini, Anda memperluas tabel Order_Details yang terkait dengan tabel Orders, untuk menggabungkan kolom ProductID, UnitPrice, dan Quantity dari Order_Details ke dalam tabel Orders. Operasi Perluas tersebut mengkombinasikan kolom dari tabel terkait ke dalam subjek tabel. Saat kueri berjalan, baris dari tabel terkait (Order_Details) digabungkan ke dalam baris dari tabel subjek (Orders).

Di Power Query, kolom yang berisi link ke tabel terkait memiliki link Entri atau link Tabel. Link Entri menavigasi ke satu rekaman terkait, dan mewakilihubungan satu ke satu dengan subjek tabel.Link Tabel menavigasi ke tabel terkait, dan mewakili satu-hingga-beberapa hubungan dengan subjek tabel. Link menyatakan properti navigasi di sumber data dalam model relasional. Untuk umpan OData, properti navigasi mewakili entitas dengan kaitan kunci asing. Dalam database, seperti SQL Server, properti navigasi menyatakan hubungan kunci asing dalam database.

Link Memperluas Tabel Order_Details

Setelah Anda memperluas tabel Order_Details, tiga kolom baru dan baris tambahan ditambahkan ke tabel Orders, satu untuk setiap baris dalam tabel ditumpuk atau terkait.

  1. Di panel Pratinjau Kueri, gulir ke kolom Order_Details.

  2. Di kolom Order_Details, klik ikon perluas ( Perluas ).

  3. Di menu turun bawah Perluas :

    1. Klik (Pilih Semua Kolom) untuk mengosongkan semua kolom.

    2. Klik ProductID, UnitPrice, dan Quantity.

    3. Klik OK.

      Link Memperluas Tabel Order_Details

      Catatan: Di Power Query, Anda bisa memperluas tabel yang tertaut ke kolom, dan juga kemampuan untuk melakukan operasi agregat pada kolom dari tabel yang ditautkan sebelum memperluas data dalam subjek tabel. Untuk informasi selengkapnya tentang cara melakukan operasi agregat, lihat Melakukan agregat data dari sebuah kolom.

Langkah 3: Menghapus kolom lain agar hanya menampilkan kolom yang diminati

Dalam langkah ini Anda menghapus semua kolom kecuali kolom OrderDate, ProductID, UnitPrice, dan Quantity. Di tugas sebelumnya, Anda menggunakan Hapus Kolom Lain. Untuk tugas ini, Anda menghapus kolom yang dipilih.

Menghapus kolom yang dipilih

  1. Di panel Pratinjau Kueri, pilih semua kolom:

    1. Klik kolom pertama (OrderID).

    2. Shift+Klik kolom terakhir (Pengirim).

    3. Ctrl+Click kolom OrderDate, Order_Details.ProductID, Order_Details.UnitPrice, dan Order_Details.Quantity.

  2. Klik kanan pada header kolom yang dipilih, dan klik Hapus Kolom.

Langkah 4: Menghitung total baris untuk setiap baris Order_Details

Dalam langkah ini, Anda membuat Kolom Kustom untuk menghitung total baris untuk setiap baris Order_Details.

Menghitung total baris untuk setiap baris Order_Details

  1. Di panel Pratinjau Kueri, klik ikon tabel ( Ikon tabel ) di sudut kiri atas pratinjau.

  2. Klik Sisipkan Kolom > Kustom.

  3. Dalam kotak dialog Sisipkan Kolom Kustom, dalam kotak teks Rumus Kolom Kustom, masukkan [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. Dalam kotak teks Nama kolom baru, masukkan Total Baris.

  5. Klik OK.

Menghitung total baris untuk setiap baris Order_Details

Langkah 5: Mentransformasi kolom tahun OrderDate

Dalam langkah ini, Anda mengubah kolom OrderDate untuk menyajikan tahun tanggal pesanan.

  1. Di kisi Pratinjau, klik kanan kolom OrderDate, dan klik Transformasi > Year.

  2. Ganti nama kolom OrderDate menjadi Year:

    1. Klik Ganda kolom OrderDate, dan masukkan Year atau

    2. Klik Kanan kolom OrderDate, klik Ganti nama, dan masukkan Year.

Langkah 6: Mengelompokkan baris berdasarkan ProductID dan Year

  1. Di kisi Pratinjau Kueri, pilih Year dan Order_Details.ProductID.

  2. Klik kanan salah satu header, dan klik Kelompokkan Menurut.

  3. Dalam kotak dialog Kelompokkan Menurut:

    1. Dalam kotak teks Nama kolom baru, masukkan Total Sales.

    2. Di menu turun bawah Operasi, pilih Sum.

    3. Di menu turun bawah Kolom, pilih Total Baris.

  4. Klik OK.

    Kotak Dialog Kelompokkan Berdasarkan untuk Operasi Agregat

Langkah 7: Mengganti nama kueri

Sebelum Anda mengimpor data penjualan ke Excel, beri nama kueri Total Sales:

  1. Di panel Pengaturan Kueri, di kotak teks Nama masukkan Total Sales.

Hasil kueri final

Setelah Anda melakukan setiap langkah, Anda akan memiliki kueri Total Sales melalui umpan OData Northwind.

Total Penjualan

Langkah pembuatan Power Query

Saat Anda melakukan aktivitas kueri di Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, di daftar LANGKAH yang DITERAPKAN. Setiap langkah kueri memiliki rumus Power Query yang berkaitan, juga dikenal sebagai bahasa "M". Untuk informasi selengkapnya tentang bahasa rumus Power Query, lihat Mempelajari tentang rumus Power Query.

Tugas

Langkah kueri

Rumus

Menyambungkan ke umpan OData

Sumber

Source{[Name="Orders"]}[Data]

Memperluas tabel Order_Details

Perluas Order_Details

Table.ExpandTableColumn

(Orders, "Order_Details", {"ProductID", "UnitPrice", "Quantity"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Quantity"})

Menghapus kolom lain agar hanya menampilkan kolom yang diminati

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Customer", "Employee", "Shipper"})

Menghitung total baris untuk setiap baris Order_Details

InsertedColumns

Tabel.AddColumn

(RemovedColumns, "Custom", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Mengubah kolom OrderDate untuk merender tahun

RenamedColumns

Table.RenameColumns

(InsertedCustom,{{"Custom", "Line Total"}})

TransformedColumn

Table.TransformColumns

(RenamedColumns,{{"OrderDate", Date.Year}})

RenamedColumns1

Table.RenameColumns

(TransformedColumn,{{"OrderDate", "Year"}})

Mengelompokkan baris menurut ProductID dan Year

GroupedRows

Table.Group
(RenamedColumns1, {"Year", "Order_Details.ProductID"}, {{"Total Sales", each List.Sum([Line Total]), ketikkan angka}})

Langkah 8: Menonaktifkan unduhan kueri ke dalam buku kerja Excel

Karena kueri Total Sales tidak mewakili laporan final Total Sales per Product and Year, Anda menonaktifkan pengunduhan kueri ke dalam buku kerja Excel. Saat opsi Muat ke Lembar KerjaDinonaktifkan di panel Pengaturan kueri, hasil data kueri ini tidak diunduh, tapi kueri masih bisa dikombinasikan dengan kueri lain untuk menyusun hasil yang diharapkan. Anda mempelajari cara mengkombinasikan kueri ini dengan kueri Product dalam tugas berikutnya.

Menonaktifkan unduhan kueri

  1. Di panel Pengaturan Kueri, kosongkan Muat ke lembar kerja.

  2. Di pita Editor Kueri, klik Terapkan & Tutup. Di panel Kueri Buku Kerja, kueri Total Sales menampilkan Memuat dinonaktifkan.

    Menonaktifkan unduhan kueri

Atas Halaman

Tugas 3: Mengkombinasikan kueri Products dan Total Sales

Power Query memungkinkan Anda mengkombinasikan beberapa kueri, dengan menggabungkan atau menambahkan kueri. Operasi Gabungkan yang dilakukan pada setiap kueri Power Query dengan bentuk tabular, terlepas dari sumber data dari mana data berasal. Untuk informasi selengkapnya tentang menggabungkan sumber data, lihat Mengkombinasikan beberapa kueri.

Dalam tugas ini, Anda mengkombinasikan kueri Product dan Total Sales dengan menggunakan langkah kueri Gabungkan dan Perluas.

Langkah 1: Menggabungkan ProductID ke dalam kueri Total Sales

  1. Dalam buku kerja Excel, navigasi ke kueri Product pada Sheet2.

  2. Di tab pita QUERY, klik kueri Gabungkan.

  3. Dalam kotak dialog Gabungkan , pilih Product sebagai tabel utama, dan pilih Total Sales sebagai kueri kedua atau terkait untuk digabungkan. Total Sales akan menjadi kolom baru yang dapat diperluas.

  4. Untuk mencocokkan Total Sales untuk Products menurut ProductID, pilih kolom ProductID dari tabel Products , dan kolom Order_Details.ProductID dari tabel Total Sales.

  5. Dalam kotak dialog Tingkat Privasi:

    1. Pilih Organisasi untuk tingkat isolasi privasi Anda untuk kedua sumber data.

    2. Klik Simpan.

  6. Klik OK.

    Catatan Keamanan : Tingkat Privasi mencegah pengguna tanpa sengaja menggabungkan data dari beberapa sumber data, yang mungkin bersifat privat atau organisasi. Bergantung pada kueri, pengguna bisa tanpa sengaja mengirim data dari sumber data privat ke sumber data lain yang mungkin berbahaya. Power Query menganalisis setiap sumber data dan menggolongkannya ke dalam tingkat privasi yang ditentukan: Publik, Organisasi, dan Privat. Untuk informasi selengkapnya tentang Tingkat Privasi, lihat Tingkat Privasi.

    Kotak dialog Gabungkan

Setelah Anda mengklik OK, operasi Gabungkan membuat kueri. Hasil kueri berisi semua kolom dari tabel utama (Product), dan kolom tunggal berisi link navigasi ke tabel terkait (Total Sales). Operasi Perluas menambahkan kolom baru ke dalam tabel utama atau subjek tabel dari tabel terkait.

Gabungkan Final

Langkah 2: Memperluas kolom yang digabungkan

Dalam langkah ini, Anda memperluas kolom yang digabungkan dengan nama NewColumn untuk membuat dua kolom baru di kueri Products : Year dan Total Sales.

Link memperluas tabel NewColumn

  1. Di kisi Pratinjau Kueri, klik NewColumn ikon perluas ( Perluas ).

  2. Di menu turun bawah Perluas :

    1. Klik (Pilih Semua Kolom) untuk mengosongkan semua kolom.

    2. Klik Tahun dan Total Penjualan.

    3. Klik OK.

  3. Ganti nama kedua kolom ini menjadi Year dan Total Sales.

  4. Urutkan Turun menurut Total Sales untuk mengetahui produk mana dan tahun berapa produk tersebut mendapatkan volume penjualan tertinggi.

  5. Ganti Nama kueri menjadi Total Sales per Product.

Memperluas tautan tabel

Langkah pembuatan Power Query

Saat Anda melakukan aktivitas kueri Gabungkan di Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, di daftar LANGKAH yang DITERAPKAN. Setiap langkah kueri memiliki rumus Power Query yang sesuai, juga dikenal sebagai bahasa "M". Untuk informasi selengkapnya tentang bahasa rumus Power Query, lihat Mempelajari tentang rumus Power Query.

Tugas

Langkah kueri

Rumus

Menggabungkan ProductID ke dalam kueri Total Sales

Sumber (sumber data untuk operasi Penggabungan)

Table.NestedJoin

(Products,{"ProductID"},#"Total Sales",{"Order_Details.ProductID"},"NewColumn")

Memperluas kolom gabungan

ExpandNewColumn

Table.ExpandTableColumn

(Source, "NewColumn", {"Year", "Total Sales"}, {"NewColumn.Year", "NewColumn.Total Sales"})

RenamedColumns

Table.RenameColumns

(#"Expand NewColumn",{{"NewColumn.Year", "Year"}, {"NewColumn.Total Sales", "Total Sales"}})

SortedRows

Table.Sort

(RenamedColumns,{{"Total Sales", Order.Descending}})

Langkah 3: Memuat kueri Total Sales per Product ke Model Data Excel

Dalam langkah ini, Anda menonaktifkan opsi Muat ke Lembar Kerja dan memuat kueri ke dalam Model Data Excel, untuk menyusun laporan yang tersambung ke hasil kueri. Selain untuk memuat hasil kueri ke dalam lembar kerja Excel, Power Query memungkinkan Anda untuk memuat hasil kueri ke dalam Model Data Excel. Setelah Anda memuat data ke dalam Model Data Excel, Anda bisa menggunakan Power Pivot dan Power View untuk analisis data lebih lanjut.

Memuat kueri Total Sales per Product ke Model Data Excel

  1. Di panel Pengaturan Kueri, kosongkan Muat ke lembar kerja dan centang Muat ke model data.

  2. Untuk memuat kueri ke dalam Model Data Excel, klik Terapkan & Tutup.

Memuat Model Data Excel

Kueri Total Sales per Product final

Setelah Anda melakukan setiap langkah, Anda akan memiliki kueri Total Sales per Product yang mengkombinasikan data dari file Products and Orders.xlsx dan umpan OData Northwind. Kueri ini dapat diterapkan ke model Power Pivot. Selain itu, perubahan pada kueri di Power Query mengubah dan merefresh tabel yang dihasilkan dalam model Power Pivot.

Atas Halaman

Catatan: Editor Kueri hanya muncul saat Anda memuat, mengedit, atau membuat kueri baru menggunakan Power Query. Video berikut ini memperlihatkan jendela Editor Kueri muncul setelah pengeditan kueri dari sebuah buku kerja Excel. Untuk menampilkan Editor Kueri tanpa memuat atau mengedit kueri buku kerja yang sudah ada, dari bagian Dapatkan Data Eksternal di tab pita Power Query, pilih Dari Sumber Lain > Kueri Kosong. Video berikut ini memperlihatkan salah satu cara untuk menampilkan Editor Kueri.

Cara untuk melihat Editor Kueri di Excel

Kembangkan keterampilan Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×