Pelajari cara menggabungkan beberapa sumber data (Power Query)

Dalam tutorial ini, Anda bisa 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. Dalam kotak dialog Simpan Sebagai, beri nama file Produk dan Pesanan.xlsx.

Dalam tugas ini, Anda mengimpor produk dari file Products and Orders.xlsx (diunduh dan diganti nama di atas) ke dalam buku kerja Excel, mempromosikan baris ke header kolom, menghapus beberapa kolom, dan memuat kueri ke lembar kerja.

Langkah 1: Menyambungkan ke buku kerja Excel

  1. Membuat buku kerja Excel.

  2. Pilih Data > Dapatkan Data > Dari File > Buku Kerja.

  3. Dalam kotak dialog Impor Data, telusuri dan temukan file Products.xlsx yang telah diunduh, lalu pilih Buka.

  4. Di panel Navigator, klik ganda tabel Products. Editor Power Query muncul.

Langkah 2: Periksa Langkah-langkah Kueri

Secara default, Power Query otomatis menambahkan beberapa langkah demi langkah sesuai kenyamanan Anda. Periksa setiap langkah di bawah Langkah yang Diterapkan dalam panel Pengaturan kueri untuk mempelajari selengkapnya.

  1. Klik kanan langkah Sumber, dan pilih Edit Pengaturan. Langkah ini dibuat saat Anda mengimpor buku kerja.

  2. Klik kanan langkah Navigasi, dan pilih Edit Pengaturan. Langkah ini dibuat saat Anda memilih tabel dari kotak dialog Navigasi.

  3. Klik kanan langkah Tipe yang Diubah, dan pilih Edit Pengaturan. Langkah ini dibuat oleh Power Query yang menyimpulkan tipe data setiap kolom. Pilih panah bawah di sebelah kanan bilah rumus untuk melihat rumus lengkap.

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 Pratinjau Data, pilih kolom ProductID, ProductName, CategoryID, dan QuantityPerUnit (gunakan Ctrl+Klik atau Shift+Klik).

  2. Pilih Hapus Kolom > Menghapus Kolom Lain.

    Menyembunyikan kolom lain

Langkah 4: Muat kueri produk

Dalam langkah ini, Anda memuat kueri Products ke dalam lembar Excel kerja.

  • Pilih Beranda > Tutup & Muat. Kueri muncul di lembar kerja Excel kerja baru.

Ringkasan: Langkah-langkah Power Query dibuat di Tugas 1

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 sesuai, juga dikenal sebagai bahasa "M". Untuk informasi selengkapnya tentang rumus Power Query, lihat Membuat rumus Power Query Excel.

Tugas

Langkah kueri

Rumus

Mengimpor buku Excel kerja

Sumber

= Excel. Buku kerja(File.Contents("C:\Products and Orders.xlsx"), null, true)

Pilih tabel Produk

Navigasi

= Source{[Item="Products",Kind="Table"]}[Data]

Power Query secara otomatis mendeteksi tipe data kolom

Tipe yang Diubah

= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", type text}, {"UnitPrice", type number}, {"UnitsInStock", Int64.Type}, {"UnitsOnOrder", Int64.Type}, {"ReorderLevel", Int64.Type}, {"Discontinued", type logical}})

Menghapus kolom lain agar hanya menampilkan kolom yang diminati

Kolom lain yang dihapus

= Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"})

Dalam tugas ini, Anda mengimpor data ke dalam buku kerja Excel Anda dari sampel umpan OData Northwind di http://services.odata.org/Northwind/Northwind.svc,memperluas tabel Order_Details, menghapus kolom, menghitung total baris, mentransformasi OrderDate, mengelompokkan baris menurut ProductID dan Year, mengganti nama kueri, dan menonaktifkan unduhan kueri ke buku kerja Excel.

Langkah 1: Koneksi ke Umpan OData

  1. Pilih Data > Dapatkan Data > Dari Sumber Lain > Dari Umpan OData.

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

  3. Pilih OK.

  4. Di panel Navigator, klik ganda tabel Orders.

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 dengan tabel utama (Pesanan).

Di Power Query, kolom yang berisi tabel terkait memiliki nilai Rekaman atau Tabel di sel tersebut. Ini disebut kolom terstruktur. Rekaman menunjukkan satu rekaman terkait dan mewakili hubungan satu ke satu dengan data saat ini atau tabel utama. Tabel menunjukkan tabel terkait dan mewakili hubungan satu ke banyak dengan tabel saat ini atau utama. Kolom terstruktur menyatakan hubungan di sumber data yang memiliki model relasional. Misalnya, kolom terstruktur menunjukkan sebuah entitas dengan kunci asing yang diasingkan dalam umpan OData atau hubungan kunci asing dalam database SQL Server lain.

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 Pratinjau Data,gulir secara horizontal ke Order_Details kolom.

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

  3. Di menu turun bawah Perluas :

    1. Pilih (Pilih Semua Kolom) untuk menghapus semua kolom.

    2. Pilih ProductID, UnitPrice, dan Quantity.

    3. Pilih OK.

      Link Memperluas Tabel Order_Details

      Catatan: Di Power Query, Anda bisa memperluas tabel yang ditautkan dari kolom dan menggabungkan 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

  1. Di Pratinjau Data,pilih kolom berikut ini: 

    1. Pilih 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 pilih Hapus Kolom Lain.

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.

  1. Di Pratinjau Data,pilih ikon tabel ( Ikon tabel ) di sudut kiri atas pratinjau.

  2. Klik Tambahkan Kolom Kustom.

  3. Dalam kotak dialog Kolom Kustom, dalam kotak rumus kolom Kustom, masukkan [Order_Details.UnitPrice] * [Order_Details.Quantity].

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

  5. Pilih 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 Pratinjau Data,klik kanan kolom OrderDate, dan pilih Transformasi > Year.

  2. Ganti nama kolom OrderDate menjadi Year:

    1. Klik Ganda kolom OrderDate, dan masukkan Year atau

    2. Right-Click kolom OrderDate, pilih Ganti Nama, dan masukkan Year.

Langkah 6: Mengelompokkan baris berdasarkan ProductID dan Year

  1. Di Pratinjau Data, pilih Tahun dan Order_Details.ProductID.

  2. Right-Click salah satu header, dan pilih 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. Pilih OK.

    Kotak Dialog Kelompokkan Menurut untuk Operasi Agregat

Langkah 7: Mengganti nama kueri

Sebelum Anda mengimpor data penjualan ke dalam Excel, ganti nama kueri:

  • Di panel Pengaturan, di kotak Nama masukkan Total Sales.

Hasil: Kueri akhir untuk Tugas 2

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

Total Penjualan

Ringkasan: Langkah-langkah Power Query dibuat di Tugas 2 

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 sesuai, juga dikenal sebagai bahasa "M". Untuk informasi selengkapnya tentang rumus Power Query, lihat Mempelajari tentang rumus Power Query.

Tugas

Langkah kueri

Rumus

Menyambungkan ke umpan OData

Sumber

= OData.Feed("http://services.odata.org/Northwind/Northwind.svc", null, [Implementation="2.0"])

Pilih tabel

Navigasi

= 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

Kustom yang Ditambahkan

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

= Table.AddColumn(#"Expanded Order_Details", "Line Total", each [Order_Details.UnitPrice] * [Order_Details.Quantity])

Berubah menjadi nama yang lebih bermakna, Jumlah Total

Kolom yang Diganti Nama

= Table.RenameColumns(InsertedCustom,{{"Custom", "Line Total"}})

Mengubah kolom OrderDate untuk merender tahun

Tahun yang Diekstrak

= Table.TransformColumns(#"Grouped Rows",{{"Year", Date.Year, Int64.Type}})

Ubah menjadi 

nama yang lebih bermakna, OrderDate dan Year

Kolom 1 yang Diganti Nama

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}})

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 Products dan Total Sales dengan menggunakan operasi Gabungkan kueri dan Perluas, lalu muat kueri Total Sales per Product ke model Data Excel.

Langkah 1: Menggabungkan ProductID ke dalam kueri Total Sales

  1. Dalam bagian Excel kerja, navigasikan ke kueri Products pada tab lembar kerja Products.

  2. Pilih sebuah sel di dalam kueri, lalu pilih Kueri> Gabungkan.

  3. Dalam kotak dialog Gabungkan, pilih Products sebagai tabel utama, dan pilih Total Sales sebagai kueri sekunder atau terkait untuk digabungkan. Total Sales akan menjadi kolom terstruktur baru dengan ikon perluas.

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

  6. Pilih 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 tingkat privasi yang ditentukan: Publik, Organisasi, dan Pribadi. Untuk informasi selengkapnya tentang Tingkat Privasi, lihat Mengatur Tingkat Privasi.

    Kotak dialog Gabungkan

Hasil

Operasi Gabungkan membuat kueri. Hasil kueri berisi semua kolom dari tabel utama (Products), dan satu kolom Terstruktur Tabel ke tabel terkait (Total Sales). Pilih ikon Perluas untuk menambahkan kolom baru ke tabel utama dari tabel sekunder atau terkait.

Gabungkan Final

Langkah 2: Memperluas kolom yang digabungkan

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

  1. Di Pratinjau Data, pilih Perluas ikon ( Perluas ) di samping NewColumn.

  2. Di daftar turun bawah Perluas:

    1. Pilih (Pilih Semua Kolom) untuk menghapus semua kolom.

    2. Pilih Tahun dan Total Penjualan.

    3. Pilih OK.

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

  4. Untuk mengetahui produk mana dan tahun produk tersebut mendapatkan volume penjualan tertinggi, pilih Urutkan Turun menurutTotal Penjualan.

  5. Ganti Nama kueri menjadi Total Sales per Product.

Hasil

Link perluas tabel

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

Dalam langkah ini, Anda memuat kueri ke dalam Excel Model Data,untuk menyusun laporan yang tersambung ke hasil kueri. Setelah Anda memuat data ke dalam model Excel Data,Anda bisa menggunakan Power Pivot untuk analisis data lebih lanjut.

  1. Pilih Pengaturan> Tutup & Muat.

  2. Dalam kotak dialog Impor Data, pastikan Anda memilih Tambahkan data ini ke Model Data. Untuk informasi selengkapnya tentang menggunakan kotak dialog ini, pilih tanda tanya (?).

Hasil

Anda memiliki kueri Total Sales per Product yang menggabungkan data dari file Products.xlsx dan umpan OData Northwind. Kueri ini diterapkan pada model Power Pivot. Selain itu, perubahan pada kueri mengubah dan merefresh tabel yang dihasilkan dalam Model Data.

Ringkasan: Langkah-langkah Power Query dibuat di Tugas 3

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 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"}, "Total Sales", JoinKind.LeftOuter)

Memperluas kolom gabungan

Total Penjualan diperluas

= Table.ExpandTableColumn(Source, "Total Sales", {"Year", "Total Sales"}, {"Total Sales.Year", "Total Sales.Total Sales"})

Mengganti nama dua kolom

Kolom yang Diganti Nama

= Table.RenameColumns(#"Expanded Total Sales",{{"Total Sales.Year", "Year"}, {"Total Sales.Total Sales", "Total Sales"}})

Mengurutkan total Penjualan dalam urutan naik

Baris yang Diurutkan

= Table.Sort(#"Renamed Columns",{{"Total Sales", Order.Ascending}})

Lihat Juga

Power Query untuk Excel Bantuan

Perlu bantuan lainnya?

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

Apakah informasi ini bermanfaat?

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

×