Dalam tutorial ini, Anda dapat 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 untuk menghasilkan laporan "Total Penjualan per Produk dan Tahun".
Untuk melakukan tutorial ini, Anda memerlukan buku kerja Produk . Dalam kotak dialog Simpan Sebagai, beri nama file Produk dan Pesanan.xlsx.
Tugas 1: Mengimpor produk ke dalam buku kerja Excel
Dalam tugas ini, Anda mengimpor produk dari file Produk dan Orders.xlsx (diunduh dan diganti namanya di atas) ke buku kerja Excel, mempromosikan baris menjadi header kolom, menghapus beberapa kolom, dan memuat kueri ke lembar kerja.
Langkah 1: Sambungkan ke buku kerja Excel
- Membuat buku kerja Excel.
- Pilih Data>Dapatkan Data>dari File>dari Buku Kerja.
- Dalam kotak dialog Impor Data , telusuri dan temukan file Products.xlsx yang Anda unduh, lalu pilih Buka.
- Di panel Navigator , klik dua kali tabel Produk . Editor Power Query akan muncul.
Langkah 2: Periksa langkah-langkah kueri
Secara default, Power Query secara otomatis menambahkan beberapa langkah untuk memudahkan Anda. Periksa setiap langkah di bawah Langkah Terapan di panel Pengaturan Kueri untuk mempelajari selengkapnya.
- Klik kanan langkah Sumber , lalu pilih Edit Pengaturan. Langkah ini dibuat saat Anda mengimpor buku kerja.
- Klik kanan langkah Navigasi , lalu pilih Edit Pengaturan. Langkah ini dibuat ketika Anda memilih tabel dari kotak dialog Navigasi .
- Klik kanan langkah Tipe yang Diubah , lalu 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: Hapus kolom lain untuk hanya menampilkan kolom yang diminati
Dalam langkah ini Anda menghapus semua kolom kecuali ProductID, ProductName, CategoryID, dan QuantityPerUnit.
- Di Pratinjau Data, pilih kolom ProductID,ProductName, CategoryID, dan QuantityPerUnit (gunakan Ctrl+Klik atau Shift+Klik).
- Pilih Hapus kolom Hapus>kolom lain.
Langkah 4: Muat kueri produk
Pada langkah ini, Anda memuat kueri Produk ke lembar kerja Excel.
- Pilih Beranda>,Tutup, & Muat. Kueri muncul di lembar kerja Excel baru.
Ringkasan: Langkah-langkah Power Query yang dibuat di Tugas 1
Saat Anda melakukan aktivitas kueri di Power Query, langkah-langkah kueri dibuat dan tercantum di panel Pengaturan Kueri, dalam daftar Langkah 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 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 | Menghapus kolom lain | = Table.SelectColumns(FirstRowAsHeader,{"ProductID", "ProductName", "CategoryID", "QuantityPerUnit"}) |
Tugas 2: Mengimpor data pesanan dari umpan OData
Dalam tugas ini, Anda mengimpor data ke buku kerja Excel dari sampel umpan Northwind OData di http://services.odata.org/Northwind/Northwind.svc, memperluas tabel Order_Details, menghapus kolom, menghitung total baris, mengubah OrderDate, mengelompokkan baris berdasarkan ProductID dan Tahun, mengganti nama kueri, dan menonaktifkan pengunduhan kueri ke buku kerja Excel.
Langkah 1: Sambungkan ke umpan OData
- Pilih data>Dapatkan data>dari sumber> laindari umpan OData.
- Dalam kotak dialog Umpan OData, masukkan URL untuk umpan OData Northwind.
- Pilih OK.
- Di panel Navigator , klik ganda tabel Pesanan .
Langkah 2: Perluas 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 menjadi baris dengan tabel utama (Pesanan).
Dalam Power Query, kolom yang berisi tabel terkait memiliki nilai Rekaman atau Tabel dalam sel. Ini disebut kolom terstruktur. Rekaman menunjukkan satu rekaman terkait dan mewakili hubungan satu lawan satu dengan data atau tabel utama saat ini. Tabel menunjukkan tabel terkait dan mewakili hubungan satu ke banyak dengan tabel saat ini atau primer. Kolom terstruktur mewakili hubungan dalam sumber data yang memiliki model relasional. Misalnya, kolom terstruktur menunjukkan entitas dengan asosiasi kunci asing dalam umpan OData atau hubungan kunci asing dalam database SQL Server.
Setelah memperluas tabel Order_Details , tiga kolom baru dan baris tambahan ditambahkan ke tabel Pesanan , satu untuk setiap baris dalam tabel bertumpuk atau terkait.
Di Pratinjau Data, gulir secara horizontal ke kolom Order_Details .
Di kolom Order_Details, pilih ikon perluas (
Di menu turun bawah Perluas :
Pilih (Pilih Semua kolom) untuk menghapus semua kolom.
Pilih ID Produk, Harga Satuan, dan Kuantitas.
Pilih OK.
Catatan
Di Power Query, Anda dapat memperluas tabel yang ditautkan dari kolom dan menggabungkan kolom tabel tertaut sebelum memperluas data dalam tabel subjek. Untuk informasi selengkapnya tentang cara melakukan operasi agregat, lihat Melakukan agregat data dari sebuah kolom.
Langkah 3: Hapus kolom lain untuk hanya menampilkan kolom yang diminati
Dalam langkah ini Anda menghapus semua kolom kecuali kolom OrderDate, ProductID, UnitPrice, dan Quantity.
Di Pratinjau Data, pilih kolom berikut:
- Pilih kolom pertama, OrderID.
- Shift+Klik kolom terakhir, Pengiriman.
- Ctrl+Click kolom OrderDate, Order_Details.ProductID, Order_Details.UnitPrice, dan Order_Details.Quantity.
Klik kanan header kolom yang dipilih, lalu pilih Hapus Kolom Lain.
Langkah 4: Hitung total baris untuk setiap baris Order_Details
Dalam langkah ini, Anda membuat Kolom Kustom untuk menghitung total baris untuk setiap baris Order_Details.
- Di Pratinjau Data, pilih ikon tabel (
) di sudut kiri atas pratinjau. - Klik Tambahkan kolom kustom.
- Dalam kotak dialog Kolom Kustom , dalam kotak Rumus kolom kustom , masukkan [Order_Details.UnitPrice] * [Order_Details.Quantity].
- Di kotak Nama kolom baru , masukkan Total Baris.
- Pilih OK.
Langkah 5: Ubah kolom tahun OrderDate
Dalam langkah ini, Anda mengubah kolom OrderDate untuk menyajikan tahun tanggal pesanan.
Di Pratinjau Data, klik kanan kolom OrderDate, lalu pilih Ubah Tahun>.
Ganti nama kolom OrderDate menjadi Year:
- Klik Ganda kolom OrderDate, dan masukkan Year atau
- Right-Click pada kolom OrderDate , pilih Ganti Nama, lalu masukkan Tahun.
Langkah 6: Kelompokkan baris berdasarkan ProductID dan Tahun
Di Pratinjau Data, pilih Tahun dan Order_Details.ProductID.
Right-Click salah satu header, lalu pilih Kelompokkan Menurut.
Dalam kotak dialog Kelompokkan Menurut:
- Dalam kotak teks Nama kolom baru, masukkan Total Sales.
- Di menu turun bawah Operasi, pilih Sum.
- Di menu turun bawah Kolom, pilih Total Baris.
Pilih OK.
Sebelum mengimpor data penjualan ke Excel, ganti nama kueri:
- Di panel Pengaturan Kueri , dalam kotak Nama , masukkan Total Penjualan.
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 yang dibuat di Tugas 2
Saat Anda melakukan aktivitas kueri di Power Query, langkah-langkah kueri dibuat dan tercantum di panel Pengaturan Kueri, dalam daftar Langkah 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="pesanan"]}[data] |
| Memperluas tabel Order_Details | Perluas Order_Details | = Table.ExpandTableColumn(Pesanan, "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 | Menambahkan kustom |
= Table.AddColumn(RemovedColumns, "Custom", masing-masing [Order_Details.UnitPrice] * [Order_Details.Quantity]) = Table.AddColumn(#"Diperluas Order_Details", "Total Baris", masing-masing [Order_Details.UnitPrice] * [Order_Details.Quantity]) |
| Ubah 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(#"Baris yang dikelompokkan",{{"Tahun", Date.Year, Int64.Type}}) |
| Ubah menjadi Nama yang lebih bermakna, Tanggal Urutan, dan Tahun |
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}}) |
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 menggabungkan kueri Produk dan Total Penjualan menggunakan kueri Gabungan dan operasi Expand, lalu muat kueri Total Penjualan per Produk ke dalam Model Data Excel.
Langkah 1: Gabungkan ProductID ke dalam kueri Total Sales
Di buku kerja Excel, navigasikan ke kueri Produk pada tab lembar kerja Produk .
Pilih sel dalam kueri, lalu pilih Gabungan Kueri>.
Dalam kotak dialog Gabungkan , pilih Produk sebagai tabel utama, lalu pilih Total Penjualan sebagai kueri sekunder atau terkait untuk digabungkan. Total Penjualan akan menjadi kolom terstruktur baru dengan ikon perluas.
Untuk mencocokkan Total Sales untuk Products menurut ProductID, pilih kolom ProductID dari tabel Products , dan kolom Order_Details.ProductID dari tabel Total Sales.
Dalam kotak dialog Tingkat Privasi:
- Pilih Organisasi untuk tingkat isolasi privasi Anda untuk kedua sumber data.
- Pilih Simpan.
Pilih OK.
Catatan
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 Penjualan). Pilih ikon Luaskan untuk menambahkan kolom baru ke tabel utama dari tabel sekunder atau terkait.
Langkah 2: Perluas kolom gabungan
Pada langkah ini, Anda memperluas kolom gabungan dengan nama NewColumn untuk membuat dua kolom baru dalam kueri Produk : Tahun dan Total Penjualan.
Di Pratinjau Data, pilih ikon Luaskan (
) di samping NewColumn.Dalam daftar menurun Luaskan :
- Pilih (Pilih Semua kolom) untuk menghapus semua kolom.
- Pilih Tahun dan Total Penjualan.
- Pilih OK.
Ganti nama kedua kolom ini menjadi Year dan Total Sales.
Untuk mengetahui produk dan tahun mana produk tersebut mendapat volume penjualan tertinggi, pilih Urutkan Menurun menurut Total Penjualan.
Ganti Nama kueri menjadi Total Sales per Product.
Hasil
Langkah 3: Muat kueri Total Penjualan per Produk ke dalam Model Data Excel
Pada langkah ini, Anda memuat kueri ke dalam Model Data Excel, untuk membuat laporan yang terhubung ke hasil kueri. Setelah memuat data ke dalam Model Data Excel, Anda dapat menggunakan Power Pivot untuk analisis data lebih lanjut.
- Pilih Beranda>,Tutup, & Muat.
- Dalam kotak dialog Impor Data , pastikan Anda memilih Tambahkan data ini ke Model Data. Untuk informasi selengkapnya tentang penggunaan kotak dialog ini, pilih tanda tanya (?).
Hasil
Anda memiliki kueri Total Penjualan per Produk yang menggabungkan data dari file Products.xlsx dan umpan Northwind OData. Kueri ini diterapkan ke model Power Pivot. Selain itu, perubahan pada kueri akan mengubah dan merefresh tabel yang dihasilkan dalam Model Data.
Ringkasan: Langkah-langkah Power Query yang dibuat di Tugas 3
Saat Anda melakukan aktivitas kueri gabungan di Power Query, langkah-langkah kueri dibuat dan dicantumkan di panel Pengaturan Kueri, dalam daftar Langkah 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 | Penjualan Total yang 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(#"Kolom yang Diganti namanya",{{"Total Sales", Order.Ascending}}) |