Excel

Artikel
02/26/2024

Ringkasan

Item	Deskripsi
Status Rilis	Ketersediaan Umum
Produk	Excel Power BI (Model semantik) Power BI (Aliran Data) Fabric (Dataflow Gen2) Power Apps (Aliran Data) Dynamics 365 Customer Insights Analysis Services
Jenis Autentikasi Didukung	Anonim (online) Dasar (online) Akun organisasi (online)
Dokumentasi Referensi Fungsi	Excel.Workbook Excel.CurrentWorkbook

Catatan

Beberapa kemampuan mungkin ada dalam satu produk tetapi tidak yang lain karena jadwal penyebaran dan kemampuan khusus host.

Prasyarat

Untuk menyambungkan ke buku kerja warisan (seperti .xls atau .xlsb), penyedia Access Database Engine OLEDB (atau ACE) diperlukan. Untuk menginstal penyedia ini, buka halaman unduhan dan instal versi yang relevan (32 bit atau 64 bit). Jika Anda belum menginstalnya, Anda akan melihat kesalahan berikut saat menyambungkan ke buku kerja warisan:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

ACE tidak dapat diinstal di lingkungan layanan cloud. Jadi, jika Anda melihat kesalahan ini di host cloud (seperti Power Query Online), Anda harus menggunakan gateway yang menginstal ACE untuk menyambungkan ke file Excel warisan.

Kapabilitas yang Didukung

Impor

Koneksi ke buku kerja Excel dari Power Query Desktop

Untuk membuat koneksi dari Power Query Desktop:

Pilih Buku Kerja Excel dalam mendapatkan pengalaman data. Pengalaman mendapatkan data di Power Query Desktop bervariasi di antara aplikasi. Untuk informasi selengkapnya tentang Power Query Desktop mendapatkan pengalaman data untuk aplikasi Anda, buka Tempat mendapatkan data.
Telusuri dan pilih buku kerja Excel yang ingin Anda muat. Lalu pilih Buka.

Jika buku kerja Excel online, gunakan konektor Web untuk menyambungkan ke buku kerja.
Di Navigator, pilih informasi buku kerja yang Anda inginkan, lalu pilih Muat untuk memuat data atau Mengubah Data untuk terus mengubah data dalam Editor Power Query.

Koneksi ke buku kerja Excel dari Power Query Online

Untuk membuat koneksi dari Power Query Online:

Pilih opsi buku kerja Excel dalam mendapatkan pengalaman data. Aplikasi yang berbeda memiliki berbagai cara untuk masuk ke Power Query Online mendapatkan pengalaman data. Untuk informasi selengkapnya tentang cara masuk ke Power Query Online mendapatkan pengalaman data dari aplikasi Anda, buka Tempat mendapatkan data.
Dalam kotak dialog Excel yang muncul, berikan jalur ke buku kerja Excel.
Jika perlu, pilih gateway data lokal untuk mengakses buku kerja Excel.
Jika ini pertama kalinya Anda mengakses buku kerja Excel ini, pilih jenis autentikasi dan masuk ke akun Anda (jika diperlukan).
Di Navigator, pilih informasi buku kerja yang Anda inginkan, lalu Ubah Data untuk terus mengubah data dalam Editor Power Query.

Tabel yang disarankan

Jika Anda menyambungkan ke Buku Kerja Excel yang tidak secara khusus berisi satu tabel, navigator Power Query akan mencoba membuat daftar tabel yang disarankan yang bisa Anda pilih. Misalnya, pertimbangkan contoh buku kerja berikut yang berisi data dari A1 ke C5, lebih banyak data dari D8 ke E10, dan banyak lagi dari C13 ke F16.

Cuplikan layar buku kerja Excel dengan tiga set data.

Saat Anda menyambungkan ke data di Power Query, navigator Power Query membuat dua daftar. Daftar pertama berisi seluruh lembar buku kerja, dan daftar kedua berisi tiga tabel yang disarankan.

Jika Anda memilih seluruh lembar di navigator, buku kerja ditampilkan seperti yang muncul di Excel, dengan semua sel kosong diisi dengan null.

Jika Anda memilih salah satu tabel yang disarankan, setiap tabel individual yang dapat ditentukan Power Query dari tata letak buku kerja ditampilkan di navigator. Misalnya, jika Anda memilih Tabel 3, data yang awalnya muncul di sel C13 hingga F16 ditampilkan.

Catatan

Jika lembar cukup berubah, tabel mungkin tidak di-refresh dengan benar. Anda mungkin dapat memperbaiki refresh dengan mengimpor data lagi dan memilih tabel baru yang disarankan.

Pemecahan Masalah

Presisi numerik (atau "Mengapa angka saya berubah?")

Saat mengimpor data Excel, Anda mungkin melihat bahwa nilai angka tertentu tampaknya sedikit berubah saat diimpor ke Power Query. Misalnya, jika Anda memilih sel yang berisi 0,049 di Excel, angka ini ditampilkan di bilah rumus sebagai 0,049. Tetapi jika Anda mengimpor sel yang sama ke Power Query dan memilihnya, detail pratinjau menampilkannya sebagai 0,04900000000000000002 (meskipun dalam tabel pratinjau diformat sebagai 0,049). Apa yang terjadi di sini?

Jawabannya agak rumit, dan ada hubungannya dengan bagaimana Excel menyimpan angka menggunakan sesuatu yang disebut notasi floating-point biner. Intinya adalah ada angka tertentu yang tidak dapat diwakili Excel dengan presisi 100%. Jika Anda membuka file .xlsx dan melihat nilai aktual yang disimpan, Anda akan melihat bahwa dalam file .xlsx, 0,049 sebenarnya disimpan sebagai 0,049000000000000002. Ini adalah nilai yang dibaca Power Query dari .xlsx, dan dengan demikian nilai yang muncul saat Anda memilih sel di Power Query. (Untuk informasi selengkapnya tentang presisi numerik di Power Query, buka bagian "Angka desimal" dan "Angka desimal tetap" dari Jenis data di Power Query.)

Koneksi ke buku kerja Excel online

Jika Anda ingin menyambungkan ke dokumen Excel yang dihosting di Sharepoint, Anda bisa melakukannya melalui konektor Web di Power BI Desktop, Excel, dan Aliran Data, dan juga dengan konektor Excel di Aliran Data. Untuk mendapatkan tautan ke file:

Buka dokumen di Excel Desktop.
Buka menu File, pilih tab Info, lalu pilih Salin Jalur.
Salin alamat ke bidang Jalur File atau URL , dan hapus ?web=1 dari akhir alamat.

Konektor ACE warisan

Power Query membaca buku kerja warisan (seperti .xls atau .xlsb) menggunakan penyedia OLEDB Access Database Engine (atau ACE). Karena itu, Anda mungkin menemukan perilaku tak terduga saat mengimpor buku kerja warisan yang tidak terjadi saat mengimpor buku kerja OpenXML (seperti .xlsx). Berikut ini adalah beberapa contoh umum.

Pemformatan nilai tak terduga

Karena ACE, nilai dari buku kerja Excel warisan mungkin diimpor dengan presisi atau keakuratan yang lebih sedikit dari yang Anda harapkan. Misalnya, bayangkan file Excel Anda berisi angka 1024.231, yang telah Anda format untuk ditampilkan sebagai "1,024.23". Saat diimpor ke Power Query, nilai ini dinyatakan sebagai nilai teks "1.024,23" alih-alih sebagai nomor keakuratan penuh yang mendasar (1024.231). Ini karena, dalam hal ini, ACE tidak menampilkan nilai yang mendasar ke Power Query, tetapi hanya nilai seperti yang ditampilkan di Excel.

Nilai null yang tidak terduga

Saat ACE memuat lembar, ACE melihat delapan baris pertama untuk menentukan jenis data kolom. Jika delapan baris pertama tidak mewakili baris yang lebih baru, ACE mungkin menerapkan jenis yang salah ke kolom tersebut dan mengembalikan null untuk nilai apa pun yang tidak cocok dengan jenisnya. Misalnya, jika kolom berisi angka dalam delapan baris pertama (seperti 1000, 1001, dan sebagainya) tetapi memiliki data non-numerik di baris yang lebih baru (seperti "100Y" dan "100Z"), ACE menyimpulkan bahwa kolom berisi angka, dan nilai non-numerik dikembalikan sebagai null.

Pemformatan nilai yang tidak konsisten

Dalam beberapa kasus, ACE mengembalikan hasil yang sama sekali berbeda di seluruh refresh. Menggunakan contoh yang dijelaskan di bagian pemformatan, Anda mungkin tiba-tiba melihat nilai 1024.231 alih-alih "1.024,23". Perbedaan ini dapat disebabkan oleh buku kerja warisan terbuka di Excel saat mengimpornya ke Power Query. Untuk mengatasi masalah ini, tutup buku kerja.

Data Excel yang hilang atau tidak lengkap

Terkadang Power Query gagal mengekstrak semua data dari Lembar Kerja Excel. Kegagalan ini sering disebabkan oleh Lembar Kerja yang memiliki dimensi yang salah (misalnya, memiliki dimensi A1:C200 saat data aktual menempati lebih dari tiga kolom atau 200 baris).

Cara mendiagnosis dimensi yang salah

Untuk menampilkan dimensi Lembar Kerja:

Ganti nama file xlsx dengan ekstensi .zip.
Buka file di File Explorer.
Navigasikan ke xl\lembar kerja.
Salin file xml untuk lembar bermasalah (misalnya, Sheet1.xml) dari file zip ke lokasi lain.
Periksa beberapa baris pertama file. Jika file cukup kecil, buka di editor teks. Jika file terlalu besar untuk dibuka di editor teks, jalankan perintah berikut dari Prompt Perintah: lebih banyak Sheet1.xml.
Cari <dimension .../> tag (misalnya, <dimension ref="A1:C200" />).

Jika file Anda memiliki atribut dimensi yang menunjuk ke satu sel (seperti <dimension ref="A1" />), Power Query menggunakan atribut ini untuk menemukan baris awal dan kolom data pada lembar.

Namun, jika file Anda memiliki atribut dimensi yang menunjuk ke beberapa sel (seperti <dimension ref="A1:AJ45000"/>), Power Query menggunakan rentang ini untuk menemukan baris dan kolom awal serta baris dan kolom akhir. Jika rentang ini tidak berisi semua data pada lembar, beberapa data tidak akan dimuat.

Cara memperbaiki dimensi yang salah

Anda dapat memperbaiki masalah yang disebabkan oleh dimensi yang salah dengan melakukan salah satu tindakan berikut:

Buka dan simpan ulang dokumen di Excel. Tindakan ini akan menimpa dimensi yang salah yang disimpan dalam file dengan nilai yang benar.
Pastikan alat yang menghasilkan file Excel diperbaiki untuk menghasilkan dimensi dengan benar.
Perbarui kueri M Anda untuk mengabaikan dimensi yang salah. Pada rilis Power Query Desember 2020, Excel.Workbook sekarang mendukung InferSheetDimensions opsi. Jika benar, opsi ini akan menyebabkan fungsi mengabaikan dimensi yang disimpan dalam Buku Kerja dan sebaliknya menentukannya dengan memeriksa data.

Berikut adalah contoh cara memberikan opsi ini:

Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Pemuatan data Excel yang lambat juga dapat disebabkan oleh dimensi yang salah. Namun, dalam hal ini, kelambatan disebabkan oleh dimensi yang jauh lebih besar dari yang seharusnya, daripada terlalu kecil. Dimensi yang terlalu besar akan menyebabkan Power Query membaca jumlah data yang jauh lebih besar dari Buku Kerja daripada yang sebenarnya diperlukan.

Untuk memperbaiki masalah ini, Anda bisa merujuk ke Temukan dan reset sel terakhir pada lembar kerja untuk instruksi terperinci.

Saat mengambil data dari Excel di komputer Anda atau dari SharePoint, pertimbangkan volume data yang terlibat, serta kompleksitas buku kerja.

Anda akan melihat penurunan performa saat mengambil file yang sangat besar dari SharePoint. Namun, ini hanya satu bagian dari masalah. Jika Anda memiliki logika bisnis yang signifikan dalam file Excel yang diambil dari SharePoint, logika bisnis ini mungkin harus dijalankan saat Anda me-refresh data Anda, yang dapat menyebabkan perhitungan yang rumit. Pertimbangkan untuk menggabungkan dan menghitung data sebelumnya, atau memindahkan lebih banyak logika bisnis dari lapisan Excel dan ke lapisan Power Query.

Kesalahan saat menggunakan konektor Excel untuk mengimpor file CSV

Meskipun file CSV dapat dibuka di Excel, file tersebut bukan file Excel. Gunakan konektor Teks/CSV sebagai gantinya.

Kesalahan saat mengimpor buku kerja "Buka Lembar Bentang XML" yang ketat

Anda mungkin melihat kesalahan berikut saat mengimpor buku kerja yang disimpan dalam format "Buka Lembar Bentang XML" Excel:

DataFormat.Error: The specified package is invalid. The main part is missing.

Kesalahan ini terjadi ketika driver ACE tidak diinstal pada komputer host. Buku kerja yang disimpan dalam format "Buka Lembar Bentang XML" yang ketat hanya dapat dibaca oleh ACE. Namun, karena buku kerja tersebut menggunakan ekstensi file yang sama dengan buku kerja Open XML reguler (.xlsx), kami tidak dapat menggunakan ekstensi untuk menampilkan pesan kesalahan biasa the Access Database Engine OLEDB provider may be required to read this type of file .

Untuk mengatasi kesalahan, instal driver ACE. Jika kesalahan terjadi di layanan awan, Anda harus menggunakan gateway yang berjalan di komputer yang menginstal driver ACE.

Kesalahan "File berisi data yang rusak"

Anda mungkin melihat kesalahan berikut saat mengimpor buku kerja Excel tertentu.

DataFormat.Error: File contains corrupted data.

Biasanya kesalahan ini menunjukkan ada masalah dengan format file.

Namun, terkadang kesalahan ini dapat terjadi ketika file tampaknya merupakan file Open XML (seperti .xlsx), tetapi driver ACE sebenarnya diperlukan untuk memproses file. Buka bagian konektor ACE Warisan untuk informasi selengkapnya tentang cara memproses file yang memerlukan driver ACE.

Masalah dan batasan yang diketahui

Power Query Online tidak dapat mengakses file Excel terenkripsi. Karena file Excel berlabel tipe sensitivitas selain "Publik" atau "Non-Bisnis" dienkripsi, file tersebut tidak dapat diakses melalui Power Query Online.
Power Query Online tidak mendukung file Excel yang dilindungi kata sandi.

Bagikan melalui