Pelajari cara menggabungkan beberapa sumber data (Power Query)

Dalam tugas ini, Anda mengimpor produk dari file Produk dan Orders.xlsx (diunduh dan diganti namanya di atas) ke 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 > DataDari File > Dari Buku Kerja.

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

4. Di panel Navigator , klik ganda tabel Produk . Power Editor Kueri muncul.

Langkah 2: Periksa Langkah-langkah Kueri

Secara default, Power Query secara otomatis menambahkan beberapa langkah sebagai kenyamanan bagi Anda. Periksa setiap langkah di bawah Langkah yang Diterapkan di panel Pengaturan Kueri untuk mempelajari selengkapnya.

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

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

3. Klik kanan langkah Tipe yang Diubah, lalu pilih Edit Pengaturan. Langkah ini dibuat oleh Power Query yang menyimpulkan tipe data dari 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 > Hapus Kolom Lain.

   

Langkah 4: Memuat kueri produk

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

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

Ringkasan: Power Query langkah yang dibuat di Tugas 1

Saat Anda melakukan aktivitas kueri dalam Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, dalam 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 di Excel.

Tugas	Langkah kueri	Rumus
Mengimpor buku kerja Excel	Sumber	= Excel.Workbook(File.Contents("C:\Products and Orders.xlsx"), null, true)
Pilih tabel Produk	Navigasi	= Source{[Item="Products",Kind="Table"]}[Data]
Power Query mendeteksi tipe data kolom secara otomatis	Tipe yang Diubah	= Table.TransformColumnTypes(Products_Table,{{"ProductID", Int64.Type}, {"ProductName", type text}, {"SupplierID", Int64.Type}, {"CategoryID", Int64.Type}, {"QuantityPerUnit", ketik text}, {"UnitPrice", ketik 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 buku kerja Excel dari sampel umpan OData Northwind di http://services.odata.org/Northwind/Northwind.svc,memperluas tabel Order_Details, menghapus kolom, menghitung total baris, mengubah OrderDate, mengelompokkan baris menurut IdProduk dan Tahun, mengganti nama kueri, dan menonaktifkan unduhan kueri ke buku kerja Excel.

Langkah 1: Menyambungkan 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 Pesanan .

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 dalam sel. Ini disebut kolom terstruktur. Rekaman menunjukkan satu rekaman terkait dan mewakilihubungan 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 mewakili hubungan dalam sumber data yang memiliki model relasi. Misalnya, kolom terstruktur menunjukkan entitas dengan asosiasi kunci asing dalam umpan OData atau hubungan kunci asing dalam database SQL Server.

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

2. Di kolom Order_Details , pilih ikon 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.

      

      Catatan: Di Power Query, Anda bisa memperluas tabel yang ditautkan dari kolom dan mengagregasi kolom tabel yang ditautkan sebelum memperluas data dalam tabel subjek. 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 PratinjauData, pilih kolom berikut ini: 

   1. Pilih kolom pertama, IDPesanan.

   2. Shift+Klik kolom terakhir, Pengirman.

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

2. Klik kanan header kolom yang dipilih, lalu 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 () 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.

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 , lalu pilih Transformasi> Tahun.

2. Ganti nama kolom OrderDate menjadi Year:

   1. Klik Ganda kolom OrderDate, dan masukkan Year atau

   2. Right-Click pada kolom OrderDate , pilih Ganti Nama, lalu masukkan Tahun.

Langkah 6: Mengelompokkan baris berdasarkan ProductID dan Year

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

2. Right-Click salah satu header, lalu 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.

   

Langkah 7: Mengganti nama kueri

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

• Di panel Pengaturan Kueri , dalam 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.

Ringkasan: langkah-langkah Power Query dibuat di Tugas 2 

Saat Anda melakukan aktivitas kueri dalam Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, dalam 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])
Mengubah ke nama yang lebih bermakna, Lne Total	Kolom yang Diganti Namanya	= 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, OrderDate, dan Tahun yang lebih bermakna	Mengganti nama Kolom 1	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]), type number}})

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 menggabungkan kueri Produk dan Total Penjualan menggunakan kueri Gabungkan dan operasi Perluas , lalu muat kueri Total Sales per Product ke dalam Model Data Excel.

Langkah 1: Menggabungkan ProductID ke dalam kueri Total Sales

1. Dalam buku kerja Excel, navigasikan ke kueri Produk pada tab lembar kerja Produk .

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

3. Dalam kotak dialog Gabungkan , pilih Produk sebagai tabel utama, lalu 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.

   

Hasil

Operasi Gabungkan membuat kueri. Hasil kueri berisi semua kolom dari tabel utama (Produk), 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.

Langkah 2: Memperluas kolom gabungan

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

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

2. Dalam daftar menurun 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 di tahun mana produk mendapatkan volume penjualan tertinggi, pilih Urutkan Turun menurut Total Penjualan.

5. Ganti Nama kueri menjadi Total Sales per Product.

Hasil

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

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

1. Pilih Beranda > 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 ke model Power Pivot. Selain itu, perubahan pada kueri mengubah dan merefresh tabel yang dihasilkan dalam Model Data.

Ringkasan: Power Query langkah yang dibuat di Tugas 3

Saat Anda melakukan aktivitas kueri Gabungkan di Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, dalam 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 Namanya	= 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}})