Mengimpor dan membentuk data di Excel untuk Mac (Power Query)

Mengimpor sumber data

Anda dapat mengimpor data ke Excel menggunakan Power Query dari berbagai sumber data: Buku Kerja Excel, Teks/CSV, XML, JSON, Database SQL Server, Daftar Online SharePoint, OData, Tabel Kosong, dan Kueri Kosong.

1. Pilih Data>Dapatkan Data.

   

2. Untuk memilih sumber data yang diinginkan, pilih Dapatkan Data (Power Query).

3. Dalam kotak dialog Pilih sumber data , pilih salah satu sumber data yang tersedia.

   

4. Sambungkan ke sumber data. Untuk mempelajari selengkapnya tentang cara menyambungkan ke setiap sumber data, lihat Mengimpor data dari sumber data.

5. Pilih data yang ingin Anda impor.

6. Muat data dengan mengeklik tombol Muat .

Hasil

Data yang diimpor muncul di lembar baru.

Langkah selanjutnya

Untuk membentuk dan mengubah data menggunakan Editor Power Query, pilih Transformasikan Data. Untuk informasi selengkapnya, lihat Membentuk data dengan Editor Power Query.

Membentuk data dengan Editor Power Query

Procedure

1. Pilih Data>Dapatkan Data (Power Query).

2. Untuk membuka Editor Kueri, pilih Luncurkan Editor Power Query.

   

   Tips

   Anda juga dapat mengakses Editor Kueri dengan memilih Dapatkan Data (Power Query), memilih sumber data, lalu mengeklik Berikutnya.

3. Bentuk dan ubah data Anda menggunakan Editor Kueri seperti yang Anda lakukan di Excel untuk Windows.

   

   Untuk informasi selengkapnya, lihat Bantuan Power Query untuk Excel.

4. Setelah selesai, pilih Beranda>Tutup & Muat.

Hasil

Data yang baru diimpor muncul di lembar baru.

Me-refresh sumber data

Anda dapat merefresh sumber data berikut: File SharePoint, daftar SharePoint, folder SharePoint, OData, file teks/CSV, buku kerja Excel (.xlsx), file XML dan JSON, tabel dan rentang lokal, database Microsoft SQL Server, dan folder.

Refresh pertama kali

Saat pertama kali mencoba me-refresh sumber data berbasis file dalam kueri buku kerja, Anda mungkin perlu memperbarui jalur file.

1. Pilih Data, panah di samping Dapatkan Data, lalu Pengaturan Sumber Data. Kotak dialog Pengaturan sumber data akan muncul.
2. Pilih koneksi, lalu pilih Ubah Jalur File.
3. Dalam kotak dialog Jalur file , pilih lokasi baru, lalu pilih Dapatkan Data.
4. Pilih Tutup.

Refresh waktu berikutnya

Untuk me-refresh:

• Semua sumber data dalam buku kerja, pilih Refresh Data>Semua.
• Sumber data tertentu, klik kanan tabel kueri pada lembar, lalu pilih Segarkan.
• PivotTable, pilih sel dalam PivotTable, lalu pilih PivotTable Analisis>Data Refresh.

Masukkan dan hapus kredensial

Saat pertama kali mengakses SharePoint, SQL Server, OData, atau sumber data lain yang memerlukan izin, Anda harus memberikan kredensial yang sesuai. Anda mungkin juga ingin menghapus kredensial untuk memasukkan kredensial baru.

Masukkan kredensial

Saat me-refresh kueri untuk pertama kalinya, Anda mungkin akan diminta untuk masuk. Pilih metode autentikasi dan tentukan kredensial masuk untuk tersambung ke sumber data dan melanjutkan refresh.

Jika masuk diperlukan, kotak dialog Masukkan kredensial akan muncul.

Misalnya:

• Kredensial SharePoint:

  

• Kredensial SQL Server:

  

Menghapus kredensial

1. Pilih Pengaturan Sumber>DataDapatkan Data>.
2. Dalam kotak dialog Pengaturan Sumber Datas, pilih koneksi yang Anda inginkan.
3. Di bagian bawah, pilih Hapus Izin.
4. Konfirmasikan bahwa ini adalah hal yang ingin Anda lakukan, lalu pilih Hapus.

Menulis dan mentransfer kode Power Query VBA

Meskipun penulisan di Editor Power Query tidak tersedia di Excel untuk Mac, VBA mendukung penulisan Power Query. Mentransfer modul kode VBA dalam file dari Excel untuk Windows ke Excel untuk Mac adalah proses dua langkah. Contoh program disediakan untuk Anda di akhir bagian ini.

Langkah pertama: Excel untuk Windows

1. Di Excel Windows, kembangkan kueri menggunakan VBA. Kode VBA yang menggunakan entitas berikut dalam model objek Excel juga berfungsi di Excel untuk Mac: objek Query, objek WorkbookQuery, Properti Workbook.Query. Untuk informasi selengkapnya, lihat referensi Excel VBA.

2. Di Excel, pastikan Editor Visual Basic terbuka dengan menekan ALT+F11.

3. Klik kanan modul, lalu pilih Ekspor File. Kotak dialog Ekspor akan muncul.

4. Masukkan nama file, pastikan ekstensi filenya .bas, lalu pilih Simpan.

5. Unggah file VBA ke layanan online agar file dapat diakses dari Mac.

   Anda dapat menggunakan Microsoft OneDrive. Untuk informasi selengkapnya, lihat Menyinkronkan file dengan OneDrive di Mac OS X.

Langkah kedua: Excel untuk Mac

1. Unduh file VBA ke file lokal, file VBA yang Anda simpan di "Langkah pertama: Excel untuk Windows" dan unggah ke layanan online.
2. Di Excel untuk Mac, pilih Tools>Macro>Visual Basic Editor. Jendela Editor Visual Basic akan muncul.
3. Klik kanan objek di jendela Proyek, lalu pilih Impor File. Kotak dialog Impor File akan muncul.
4. Temukan file VBA, lalu pilih Buka.

Kode sampel

Berikut adalah beberapa kode dasar yang dapat Anda adaptasi dan gunakan. Ini adalah contoh kueri yang membuat daftar dengan nilai dari 1 hingga 100.

Sub CreateSampleList()
  ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
    "let" & vbCr & vbLf & _
      "Source = {1..100}," & vbCr & vbLf & _
      "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
      "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
    "in" & vbCr & vbLf & _
      "RenamedColumns"
  ActiveWorkbook.Worksheets.Add
  With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
    "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
    , Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [SampleList]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "SampleList"
    .Refresh BackgroundQuery:=False
  End With
End Sub

Mengimpor dan merefresh file TXT atau CSV

1. Buka buku kerja Excel.

2. Jika Anda mendapatkan peringatan keamanan tentang koneksi data eksternal yang dinonaktifkan, pilih Aktifkan Konten.

3. Jika kotak dialog Berikan Akses File muncul, pilih Pilih, lalu pilih Berikan Akses ke folder tingkat atas yang berisi file sumber data.

4. Pilih Data>dari Teks (Warisan). Kotak dialog Finder akan muncul.

5. Temukan file .txt atau .csv, lalu pilih Buka.Panduan Impor Teks akan muncul.

   Tips Periksa berulang kali panel Pratinjau data yang dipilih untuk mengonfirmasi pilihan Anda.

6. Di halaman pertama, lakukan hal berikut:
   Tipe file Untuk memilih jenis file teks, pilih Lebar dipisahkan atau Lebar tetap.

   nomor baris Di Mulai impor di baris, pilih nomor baris untuk menentukan baris pertama data yang ingin Anda impor.

   set karakter Di Asal file, pilih set karakter yang digunakan dalam file teks. Biasanya, Anda dapat membiarkan pengaturan ini pada defaultnya.

7. Di halaman kedua, lakukan hal berikut:

   Dipisahkan

   Jika Anda memilih Dipisahkan di halaman pertama, di bawah Pemisah, pilih karakter pemisah atau gunakan kotak centang Lainnya untuk memasukkan karakter yang tidak tercantum.

   Pilih Perlakukan pemisah berturut-turut sebagai satu jika data Anda berisi pemisah lebih dari satu karakter di antara bidang data atau jika data Anda berisi beberapa pemisah kustom.

   Di Penalifikasi teks, pilih karakter yang menyertakan nilai dalam file teks Anda, yang paling sering berupa karakter kutipan (").
   Lebar tetap

   Jika Anda memilih Lebar tetap di halaman pertama, ikuti instruksi untuk membuat, menghapus, atau memindahkan garis henti dalam Pratinjau kotak data yang dipilih .

8. Di halaman ketiga, lakukan hal berikut:

   Untuk setiap kolom di bawah Pratinjau data yang dipilih, pilih kolom, lalu ubah ke format kolom yang berbeda jika Anda menginginkannya. Anda dapat mengatur format tanggal lebih lanjut dan memilih Tingkat Lanjut untuk mengubah pengaturan data numerik. Anda juga dapat mengonversi data setelah mengimpornya.

   Pilih Selesai. Kotak dialog Impor Data akan muncul.

9. Pilih tempat Anda ingin data ditambahkan: baik pada lembar yang sudah ada, pada lembar baru, atau di PivotTable.

10. Pilih OK.
    Untuk memastikan koneksi berfungsi, masukkan beberapa data, lalu pilih Refresh Koneksi>.

Menyambungkan ke SQL Database (ODBC)

1. Pilih data>dari SQL Server ODBC. Kotak dialog Tersambung ke Sumber Data SQL Server ODBC akan muncul.

   

2. Masukkan server dalam kotak Nama Server , dan secara opsional, masukkan database dalam kotak Nama Database .

   Dapatkan informasi ini dari administrator database.

3. Di bawah Autentikasi, pilih metode dari daftar: Nama Pengguna/Kata Sandi, Kerberos, atau NTLM.

4. Masukkan kredensial dalam kotak Nama Pengguna dan Kata Sandi .

5. Pilih Sambungkan. Kotak dialog Navigator akan muncul.

6. Di panel kiri, navigasikan ke tabel yang Anda inginkan, lalu pilih.

7. Konfirmasikan pernyataan SQL di panel kanan. Anda dapat mengubah pernyataan SQL sesuai keinginan Anda.

8. Untuk melihat pratinjau data, pilih Jalankan.

9. Jika sudah siap, pilih Kembalikan Data. Kotak dialog Impor Data akan muncul.

   

10. Pilih tempat Anda ingin data ditambahkan: baik pada lembar yang sudah ada, pada lembar baru, atau di PivotTable.

11. Untuk mengatur properti koneksi pada tab Penggunaan dan Definisi dari kotak dialog Properti , pilih Properti. Setelah mengimpor data, Anda juga dapat memilihKoneksi Data>, lalu di kotak dialog Properti Koneksi, pilih Properti.

12. Pilih OK.

13. Untuk memastikan koneksi berfungsi, masukkan beberapa data, lalu pilih Refresh Data>Semua.

Menyambungkan ke database lain (Microsoft Query)

Jika ingin menggunakan sumber eksternal yang bukan SQL Database (misalnya, FileMaker Pro), Anda dapat menggunakan driver Konektivitas Database Terbuka (ODBC) yang diinstal di Mac. Informasi pada driver tersedia di halaman web ini. Setelah driver untuk sumber data Anda diinstal, ikuti langkah-langkah berikut:

1. Pilih data>dari database (kueri Microsoft).

2. Tambahkan sumber data untuk database Anda, lalu pilih OK.

3. Pada perintah kredensial SQL Server, masukkan metode autentikasi, nama pengguna, dan kata sandi.

   

4. Di sebelah kiri, pilih panah di samping server untuk melihat database.

5. Pilih panah di samping database yang Anda inginkan.

6. Pilih tabel yang Anda inginkan.

7. Untuk melihat pratinjau data, pilih Jalankan.

8. Jika sudah siap, pilih Kembalikan Data.

9. Dalam kotak dialog Impor Data , pilih tempat Anda ingin menemukan data: baik pada lembar yang sudah ada, pada lembar baru, atau di PivotTable.

10. Pilih OK.

11. Untuk memastikan koneksi berfungsi, masukkan beberapa data, lalu pilih Refresh Data>Semua.

Hapus kredensial masuk Anda

Jika izin tidak berfungsi, Anda tidak boleh menghapusnya terlebih dahulu, lalu masuk.

1. PilihKoneksi Data>. Kotak dialog Koneksi Buku Kerja akan muncul.

2. Pilih koneksi yang Anda inginkan dalam daftar, lalu pilih Hapus Izin.