Menggabungkan data daripada berbilang sumber data (Power Query)

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Nota: Power Query dikenali sebagai Dapatkan & transformasi dalam Excel 2016. Maklumat yang disediakan di sini digunakan pada kedua-duanya. Untuk mengetahui lebih lanjut, lihat Dapatkan & transformasi dalam Excel 2016.

Dalam tutorial ini, anda akan menggunakan Editor pertanyaan Power Query untuk mengimport data dari fail Excel setempat yang mengandungi maklumat produk dan daripada OData suapan yang mengandungi maklumat pesanan produk. Anda melaksanakan langkah transformasi dan pengagregatan dan menggabungkan data daripada kedua-dua sumber untuk menghasilkan laporan Jumlah jualan setiap produk dan tahun .

Untuk melaksanakan tutorial ini, anda perlukan buku kerja Products and Orders. Dalam kotak dialog Simpan Sebagai, namakan fail Products and Orders.xlsx.

Dalam tutorial ini

Tugas 1: Mengimport produk ke dalam buku kerja Excel

Langkah 1: Sambung ke buku kerja Excel

Langkah 2: Naikkan baris pertama kepada pengepala lajur

Langkah 3: Alih keluar lajur lain untuk memaparkan lajur yang diinginkan sahaja

Langkah Power Query dicipta

Langkah 4: Import pertanyaan produk

Tugas 2: Mengimport data pesanan daripada suapan OData

Langkah 1: Sambung ke suapan OData

Langkah 2: Kembangkan jadual Order_Details

Mengembangkan pautan Jadual Order_Details

Langkah 3: Alih keluar lajur lain untuk memaparkan lajur yang diinginkan sahaja

Mengalih keluar lajur terpilih

Langkah 4: Kira jumlah baris bagi setiap baris Order_Details

Mengira jumlah baris bagi setiap baris Order_Details

Langkah 5: Tukar lajur tahun OrderDate

Langkah 6: Kumpulkan baris mengikut ProductID dan Year

Langkah 7: Namakan semula pertanyaan

Hasil pertanyaan muktamad

Langkah Power Query dicipta

Langkah 8: Nyahdayakan muat turun pertanyaan ke dalam buku kerja Excel

Menyahdayakan muat turun pertanyaan

Tugas 3: Menggabungkan pertanyaan Produk dan Jumlah Jualan

Langkah 1: Cantum ProductID ke dalam pertanyaan Total Sales

Langkah 2: Kembangkan lajur cantuman

Mengembangkan pautan jadual NewColumn

Langkah Power Query dicipta

Langkah 3: Muat pertanyaan Total Sales per Product ke dalam Model Data Excel

Memuatkan pertanyaan Total Sales per Product ke dalam Model Data Excel

Pertanyaan Total Sales per Product yang muktamad

Tugas 1: Mengimport produk ke dalam buku kerja Excel

Dalam tugas ini, anda mengimport produk daripada fail Products and Orders.xlsx ke dalam buku kerja Excel.

Langkah 1: Sambung ke buku kerja Excel

  1. Cipta buku kerja Excel.

  2. Dalam tab reben POWER QUERY, klik Daripada Fail > Daripada Excel.

  3. Dalam kotak dialog semak lalu Excel, semak lalu atau taipkan laluan Produk dan Pesanan.xlsx mengimport atau memaut ke fail.

  4. Dalam anak tetingkap Navigator, dwiklik lembaran kerja Produk atau klik Produk dan klik Edit Pertanyaan. Apabila anda mengedit pertanyaan atau bersambung dengan sumber data baru, tetingkap Editor Pertanyaan akan muncul.

    Nota: Untuk video ringkas tentang cara memaparkan Editor Pertanyaan, lihat penghujung artikel ini.

Langkah 2: Naikkan baris pertama kepada pengepala lajur

Dalam grid Pratonton Pertanyaan, baris pertama jadual tidak mengandungi nama lajur jadual. Untuk menaikkan baris pertama kepada pengepala lajur:

  1. Klik ikon jadual ( ikon Jadual ) di penjuru atas kiri pratonton data.

  2. Klik Gunakan Baris Pertama kepada Pengepala.

Naikkan baris pertama kepada pengepala lajur

Langkah 3: Alih keluar lajur lain untuk memaparkan lajur yang diinginkan sahaja

Dalam langkah ini anda mengalih keluar semua lajur kecuali ProductID, ProductName, CategoryID dan QuantityPerUnit.

  1. Dalam grid Pratonton Pertanyaan, pilih lajur ProductID, ProductName, CategoryID dan QuantityPerUnit (gunakan Ctrl+Klik atau Shift+Klik).

  2. Dalam reben Editor Pertanyaan, klik Alih Keluar Lajur > Alih Keluar Lajur Lain atau klik kanan pada pengepala lajur dan klik Alih Keluar Lajur Lain.

    Menyembunyikan lajur lain

Langkah Power Query dicipta

Apabila anda menjalankan aktiviti pertanyaan dalam Power Query, langkah pertanyaan dicipta dan disenaraikan dalam anak tetingkap Seting Pertanyaan, dalam senarai LANGKAH YANG DIGUNAKAN. Setiap langkah pertanyaan mempunyai formula Power Query sepadan yang juga dikenali sebagai bahasa "M". Untuk maklumat lanjut tentang bahasa formula Power Query, lihat Mengetahui formula Power Query.

Tugas

Langkah pertanyaan

Formula

Sambung ke buku kerja Excel

Sumber

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

Naikkan baris pertama kepada pengepala lajur

FirstRowAsHeader

Table.PromoteHeaders

(Products)

Alih keluar lajur lain untuk memaparkan lajur yang diinginkan sahaja

RemovedOtherColumns

Table.SelectColumns

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

Langkah 4: Import pertanyaan produk

Dalam langkah ini, anda mengimport pertanyaan Products ke dalam buku kerja Excel anda.

  1. Dalam reben Editor Pertanyaan, klik Guna & Tutup. Hasil muncul dalam lembaran kerja Excel yang baru.

Halaman Atas

Tugas 2: Mengimport data pesanan daripada suapan OData

Dalam tugas ini, anda mengimport data ke dalam buku kerja Excel anda daripada sampel suapan OData Northwind di http://services.odata.org/Northwind/Northwind.svc.

Langkah 1: Sambung ke suapan OData

  1. Dalam tab reben POWER QUERY, klik Daripada Sumber Lain > Daripada Suapan OData.

  2. Dalam kotak dialog Suapan OData Baru, masukkan URL untuk suapan Northwind OData.

  3. Klik OK.

  4. Dalam anak tetingkap Navigator, dwiklik jadual Pesanan atau klik Pesanan dan klik Edit.

Nota: Apabila anda meletakkan penuding tetikus anda pada jadual, anda akan melihat pratonton jadual melayang keluar.

Letakkan penuding pada Sumber Data

Langkah 2: Kembangkan jadual Order_Details

Dalam langkah ini, anda mengembangkan jadual Order_Details yang berkaitan dengan jadual Orders untuk menggabungkan lajur ProductID, UnitPrice dan Quantity daripada Order_Details ke dalam jadual Orders. Operasi Kembangkan akan menggabungkan lajur daripada jadual berkaitan ke dalam jadual subjek. Apabila pertanyaan berjalan, baris daripada jadual berkaitan (Order_Details) dicantumkan dengan baris daripada jadual subjek (Orders).

Dalam Power Query, lajur yang mengandungi pautan ke jadual berkaitan mempunyai pautan Entri atau pautan Jadual. Pautan Entri menavigasi ke satu rekod yang berkaitan dan mewakili perhubungan satu ke satu dengan jadual subjek.Pautan Jadual menavigasi ke jadual yang berkaitan dan mewakili perhubungan satu ke banyak dengan jadual subjek. Pautan mewakili sifat navigasi dalam sumber data dalam model hubungan. Bagi suapan OData, sifat navigasi mewakili entiti dengan perkaitan kunci asing. Dalam pangkalan data, seperti SQL Server, sifat navigasi mewakili perhubungan kunci asing dalam pangkalan data.

Mengembangkan pautan Jadual Order_Details

Selepas anda mengembangkan jadual Order_Details, tiga lajur yang baru dan baris tambahan akan ditambahkan pada jadual Orders, satu untuk setiap baris dalam jadual tersarang atau jadual berkaitan.

  1. Dalam anak tetingkap Pratonton Pertanyaan, skrol ke lajur Order_Details.

  2. Dalam lajur Order_Details, klik ikon kembangkan ( Kembangkan ).

  3. Dalam juntai bawah Kembangkan:

    1. Klik (Pilih Semua Lajur) untuk mengosongkan semua lajur.

    2. Klik ProductID, UnitPrice dan Kuantiti.

    3. Klik OK.

      Mengembangkan pautan Jadual Order_Details

      Nota: Dalam Power Query, anda boleh mengembangkan jadual yang dipautkan dari lajur dan juga keupayaan untuk melaksanakan operasi agregat pada lajur jadual terpaut sebelum mengembangkan data dalam jadual subjek. Untuk maklumat lanjut tentang cara melaksanakan operasi agregat, lihat Mengumpul data daripada lajur.

Langkah 3: Alih keluar lajur lain untuk memaparkan lajur yang diinginkan sahaja

Dalam langkah ini, anda mengalih keluar semua lajur kecuali lajur OrderDate, ProductID, UnitPrice dan Quantity. Dalam tugas sebelumnya, anda menggunakan Alih Keluar Lajur Lain. Untuk tugas ini, anda mengalih keluar lajur yang dipilih.

Mengalih keluar lajur terpilih

  1. Dalam anak tetingkap Pratonton Pertanyaan, pilih semua lajur:

    1. Klik lajur pertama (OrderID).

    2. Shift+Klik lajur terakhir (Shipper).

    3. Ctrl+Klik lajur OrderDate, Order_Details.ProductID, Order_Details.UnitPrice dan Order_Details.Quantity.

  2. Klik kanan pada pengepala lajur yang dipilih dan klik Alih Keluar Lajur.

Langkah 4: Kira jumlah baris bagi setiap baris Order_Details

Dalam langkah ini, anda mencipta satu Lajur Tersuai untuk mengira jumlah baris bagi setiap baris Order_Details.

Mengira jumlah baris bagi setiap baris Order_Details

  1. Dalam anak tetingkap Pratonton Pertanyaan, klik ikon jadual ( ikon Jadual ) di penjuru atas kiri pratonton.

  2. Klik Selitkan Lajur > Tersuai.

  3. Dalam kotak dialog Selitkan Lajur Tersuai, dalam kotak teks Formula Lajur Tersuai, masukkan [Order_Details.UnitPrice] * [Order_Details.Quantity].

  4. Dalam kotak teks Nama lajur baru, masukkan Jumlah Baris.

  5. Klik OK.

Mengira jumlah baris bagi setiap baris Order_Details

Langkah 5: Tukar lajur tahun OrderDate

Dalam langkah ini, anda mengubah lajur OrderDate untuk memaparkan tahun tarikh pesanan.

  1. Dalam grid Pratonton, klik kanan pada lajur OrderDate dan klik Transformasi > Year.

  2. Namakan semula lajur OrderDate kepada Year:

    1. Dwiklik lajur OrderDate dan masukkan Year atau

    2. Klik kanan pada lajur OrderDate, klik Namakan Semula dan masukkan Year.

Langkah 6: Kumpulkan baris mengikut ProductID dan Year

  1. Dalam grid Pratonton Pertanyaan, pilih Year dan Order_Details.ProductID.

  2. Klik kanan pada salah satu pengepala, dan klik Kumpul Mengikut.

  3. Dalam kotak dialog Kumpulan Mengikut:

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

    2. Dalam juntai bawah Operasi, pilih Jumlah.

    3. Dalam juntai bawah Lajur, pilih Line Total.

  4. Klik OK.

    Kotak Dialog Kumpul Mengikut untuk Operasi Agregat

Langkah 7: Namakan semula pertanyaan

Sebelum anda mengimport data jualan ke dalam Excel, namakan pertanyaan Total Sales:

  1. Dalam anak tetingkap Seting Pertanyaan, dalam kotak teks Nama, masukkan Total Sales.

Hasil pertanyaan muktamad

Selepas anda menjalankan setiap langkah, anda akan mempunyai pertanyaan Total Sales pada suapan Northwind OData.

Jumlah Jualan

Langkah Power Query dicipta

Apabila anda menjalankan aktiviti pertanyaan dalam Power Query, langkah pertanyaan dicipta dan disenaraikan dalam anak tetingkap Seting Pertanyaan, dalam senarai LANGKAH YANG DIGUNAKAN. Setiap langkah pertanyaan mempunyai formula Power Query sepadan yang juga dikenali sebagai bahasa "M". Untuk maklumat lanjut tentang bahasa formula Power Query, lihat Mengetahui formula Power Query.

Tugas

Langkah pertanyaan

Formula

Menyambung ke suapan OData

Sumber

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

Kembangkan jadual Order_Details

Expand Order_Details

Table.ExpandTableColumn

(Pesanan, "Order_Details", {"ProductID", "UnitPrice", "Kuantiti"}, {"Order_Details.ProductID", "Order_Details.UnitPrice", "Order_Details.Kuantiti"})

Alih keluar lajur lain untuk memaparkan lajur yang diinginkan sahaja

RemovedColumns

Table.RemoveColumns

(#"Expand Order_Details",{"OrderID", "CustomerID", "EmployeeID", "RequiredDate", "ShippedDate", "ShipVia", "Tambang", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "Pelanggan", "Pekerja", "Pengangkut"})

Mengira jumlah baris bagi setiap baris Order_Details

InsertedColumns

Table.AddColumn

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

Ubah lajur OrderDate untuk memaparkan tahun

RenamedColumns

Table.RenameColumns

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

TransformedColumn

Table.TransformColumns

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

RenamedColumns1

Table.RenameColumns

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

Kumpulkan baris mengikut ProductID dan Tahun

GroupedRows

Table.Group
(RenamedColumns1, {"Tahun", "Order_Details.ProductID"}, {{"Jumlah Jualan", each List.Sum([Jumlah Baris]), nombor jenis}})

Langkah 8: Nyahdayakan muat turun pertanyaan ke dalam buku kerja Excel

Memandangkan pertanyaan Total Sales tidak mewakili laporan Total Sales per Product and Year yang muktamad, anda menyahdayakan muat turun pertanyaan ke dalam buku kerja Excel. Apabila opsyen Muatkan pada Lembaran KerjaMatikan dalam anak tetingkap Seting pertanyaan, hasil data pertanyaan ini tidak dimuat turun tetapi pertanyaan masih boleh digabungkan dengan pertanyaan lain untuk membina hasil yang diinginkan. Anda mengetahui cara untuk menggabungkan pertanyaan ini dengan pertanyaan Products dalam tugas seterusnya.

Menyahdayakan muat turun pertanyaan

  1. Dalam anak tetingkap Seting Pertanyaan, kosongkan tanda Muatkan pada lembaran kerja.

  2. Dalam reben Editor Pertanyaan, klik Guna & Tutup. Dalam anak tetingkap Pertanyaan Buku Kerja, pertanyaan Total Sales memaparkan Muat dinyahdayakan.

    Menyahdayakan muat turun pertanyaan

Halaman Atas

Tugas 3: Menggabungkan pertanyaan Produk dan Jumlah Jualan

Power Query membolehkan anda untuk menggabungkan berbilang pertanyaan, dengan mencantumkan atau menambahkannya. Operasi Cantum dilakukan pada sebarang pertanyaan Power Query dengan bentuk berjadual, bebas daripada sumber data yang mengasaskan data tersebut. Untuk maklumat lanjut tentang menggabungkan sumber data, lihat Menggabungkan berbilang pertanyaan.

Dalam tugas ini, anda menggabungkan pertanyaan Products dan Total Sales menggunakan satu langkah pertanyaan Cantum dan Kembangkan.

Langkah 1: Cantum ProductID ke dalam pertanyaan Total Sales

  1. Dalam buku kerja Excel, navigasi ke pertanyaan Products pada Helaian2.

  2. Dalam tab reben PERTANYAAN, klik Cantum.

  3. Dalam kotak dialog Cantum, pilih Products sebagai jadual utama dan pilih Total Sales sebagai pertanyaan kedua atau pertanyaan berkaitan untuk cantuman. Total Sales akan menjadi lajur baru yang boleh dikembangkan.

  4. Untuk memadankan Total Sales dengan Products mengikut ProductID, pilih lajur ProductID daripada jadual Products dan lajur Order_Details.ProductID daripada jadual Total Sales.

  5. Dalam kotak dialog Aras Privasi:

    1. Pilih Organisasi untuk aras pengasingan privasi anda bagi kedua-dua sumber data.

    2. Klik Simpan.

  6. Klik OK.

    Nota Keselamatan : Aras Privasi menghalang pengguna daripada menggabungkan data daripada berbilang sumber data secara tidak sengaja, yang mungkin peribadi atau organisasi. Bergantung pada pertanyaan, pengguna mungkin menghantar data daripada sumber data peribadi kepada sumber data lain secara tidak sengaja dan hal sedemikian mungkin berbahaya. Power Query menganalisis setiap sumber data dan mengelaskannya ke dalam aras privasi yang ditentukan: Awam, Organisasi dan Persendirian. Untuk maklumat lanjut tentang Aras Privasi, lihat Aras Privasi.

    Kotak dialog Cantum

Selepas anda klik OK, operasi Cantum akan mencipta pertanyaan. Hasil pertanyaan mengandungi semua lajur daripada jadual utama (Products), dan lajur tunggal mengandungi pautan navigasi ke jadual berkaitan (Total Sales). Operasi Kembangkan akan menambahkan lajur baru pada jadual utama atau jadual subjek daripada jadual berkaitan.

Cantuman Muktamad

Langkah 2: Kembangkan lajur cantuman

Dalam langkah ini, anda mengembangkan lajur cantuman dengan nama NewColumn untuk mencipta dua lajur baru dalam pertanyaan Products: Year dan Total Sales.

Mengembangkan pautan jadual NewColumn

  1. Dalam grid Pratonton Pertanyaan, klik ikon kembangkan NewColumn ( Kembangkan ).

  2. Dalam juntai bawah Kembangkan:

    1. Klik (Pilih Semua Lajur) untuk mengosongkan semua lajur.

    2. Klik Year dan Total Sales.

    3. Klik OK.

  3. Namakan Semula dua lajur ini kepada Year dan Total Sales.

  4. Isih Menurun mengikut Total Sales untuk menentukan jenis produk dan tahun yang produk tersebut mencapai volum jualan yang tertinggi.

  5. Namakan semula pertanyaan kepada Total Sales per Product.

Kembangkan pautan jadual

Langkah Power Query dicipta

Apabila anda melaksanakan aktiviti pertanyaan Cantum dalam Power Query, langkah pertanyaan dicipta dan disenaraikan dalam anak tetingkap Seting Pertanyaan, dalam senarai LANGKAH YANG DIGUNAKAN. Setiap langkah pertanyaan mempunyai formula Power Query sepadan yang juga dikenali sebagai bahasa "M". Untuk maklumat lanjut tentang bahasa formula Power Query, lihat Mengetahui formula Power Query.

Tugas

Langkah pertanyaan

Formula

Cantum ProductID kepada pertanyaan Total Sales

Source (sumber data untuk operasi Cantum)

Table.NestedJoin

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

Kembangkan lajur cantuman

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: Muat pertanyaan Total Sales per Product ke dalam Model Data Excel

Dalam langkah ini, anda menyahdayakan opsyen Muatkan pada Lembaran Kerja dan memuatkan pertanyaan ke dalam Model Data Excel untuk membina laporan yang disambungkan ke hasil pertanyaan. Di samping memuatkan hasil pertanyaan ke dalam lembaran kerja Excel, Power Query membolehkan anda memuatkan hasil pertanyaan ke dalam satu Model Data Excel. Selepas anda memuatkan data ke dalam Model Data Excel, anda boleh menggunakan Power Pivot dan Power View untuk analisis data lanjutan.

Memuatkan pertanyaan Total Sales per Product ke dalam Model Data Excel

  1. Dalam anak tetingkap Seting Pertanyaan, kosongkan tanda Muatkan pada lembaran kerja dan tandakan Muatkan pada model data.

  2. Untuk memuatkan pertanyaan ke dalam Model Data Excel, klik Gunakan & Tutup.

Memuatkan Model Data Excel

Pertanyaan Total Sales per Product yang muktamad

Selepas anda menjalankan setiap langkah, anda akan mempunyai pertanyaan Total Sales per Product yang menggabungkan data daripada Products and Orders.fail xlsx dan suapan Northwind OData. Pertanyaan ini boleh digunakan pada model Power Pivot. Selain itu, perubahan kepada pertanyaan dalam Power Query akan mengubah suai dan menyegar semula jadual yang terhasil dalam model Power Pivot.

Halaman Atas

Nota: Editor Pertanyaan hanya muncul apabila anda memuatkan, mengedit atau mencipta pertanyaan baru menggunakan Power Query. Video berikut menunjukkan tetingkap Editor Pertanyaan muncul selepas mengedit pertanyaan dari buku kerja Excel. Untuk melihat Editor Pertanyaan tanpa memuatkan atau mengedit pertanyaan buku kerja sedia ada, dari seksyen Dapatkan Data Luaran dalam tab reben Power Query, pilih Daripada Sumber Lain > Pertanyaan Kosong. Video berikut menunjukkan cara untuk memaparkan Editor Pertanyaan.

Cara untuk melihat Editor Pertanyaan dalam Excel

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×