Excel menyalin model data ke buku kerja lain

Saya telah menyiapkan kueri yang menggabungkan data aktual dan perkiraan. Ini bagus karena saya kemudian dapat menggunakan Power Pivot untuk menyusun laporan kecil yang bagus yang memungkinkan pengguna melihat perkiraan dan data aktual secara berdampingan. Meskipun ini berfungsi dengan baik di buku kerja yang berisi data sumber, ketika saya mencoba menyalin laporan ke buku kerja lain (untuk konsumsi publik), saya mendapatkan kesalahan

The first error was around referencing the source data.  When I went into the Advanced Editor I noticed that the source was still referencing Excel.CurrentWorkbook().  However, the new workbook doesn't contain the source data.  I managed to get around this by using Excel.Workbook(Web.Contents()).  Then I got the error, Expression.Error: The column 'Column1' of the table wasn't found.

Saya kemudian menambahkan {[Name="Forecast"]} untuk merujuk rentang bernama di buku kerja dengan harapan ini akan menyelesaikan masalah, namun sekarang saya memiliki pesan kesalahan berikut, Expression. Kesalahan. Kami tidak dapat mengonversi nilai tipe Rekam ke tipe Tabel

Pengalaman kode M saya masih agak terbatas, saya dapat melakukan sedikit pengeditan, namun saya benar-benar di luar kemampuan saya dalam hal ini

Idealnya saya tidak ingin data sumber disimpan di lebih dari 1 buku kerja. Saya berharap untuk menjaga koneksi dan hanya dapat melakukan "Segarkan Semua"

Saya akan sangat menghargai panduan apa pun dan saya minta maaf jika topik ini sudah dibahas (saya baru di forum ini)

Terima kasih

Maureen

Excel menyalin model data ke buku kerja lain

Treasi Mynda

Posting. 4364

Excel menyalin model data ke buku kerja lain

24 Oktober 2019 - 8. 18 malam


2

Hai Maureen,

Cara kerja Power Query dan Power Pivot adalah dengan menyimpan data di buku kerja yang berisi koneksi dan karenanya laporan. Data sumber tidak harus ada di lembar kerja, namun dapat disimpan hanya di model Power Pivot

Kami dapat menyambungkan hampir semua sumber data di Power Query, tetapi model data PowerPivot tidak disertakan dalam daftar ekstensif sumber di luar kotak

Tetapi dengan bantuan DAX Studio yang luar biasa kita dapat melakukannya (walaupun menurut saya masih merepotkan dan rumit) setidaknya secara lokal – dari buku kerja yang sama di Excel atau dari Power BI Desktop

Yang Anda perlukan hanyalah membuka buku kerja Excel, menjalankan add-in DAX Studio, dan menyambungkannya ke buku kerja ini. Kemudian Anda bisa menyambungkan ke model PowerPivot ke kubus SQL Server Analysis Services

Tetapi ini adalah fitur yang tidak berdokumen dan sangat terbatas yang tidak didukung oleh Microsoft, yang hanya dapat digunakan dengan risiko Anda sendiri

Langkah 1. Siap…

Pertama, kita membutuhkan Sumbernya. Dengan kata lain, buku kerja Excel dengan beberapa data, dimuat ke model data PowerPivot. Jika Anda bisa melihat tabel di jendela PowerPivot, itu bagus

Saya memiliki buku kerja dengan tabel contoh ini

Excel menyalin model data ke buku kerja lain
Tabel sumber dimuat ke model data

 

yang membentuk model data sederhana ini

Excel menyalin model data ke buku kerja lain
Model data dengan hubungan, kolom terhitung, dan ukuran

Di tabel 'Penjualan' saya telah membuat kolom terhitung untuk menghitung total kumulatif untuk produk berdasarkan tanggal

Excel menyalin model data ke buku kerja lain
Hanya menghitung kolom

Dan ukuran sederhana

Bawaan

1

Jumlah dari Penjualan . = SUM(Penjualan[Value])

Sumber tabel dalam model PowerPivot adalah tabel dari buku kerja ini, tapi bisa juga dari Power Query

Kedua, kita perlu menginstal add-in DAX Studio untuk Excel. Jika Anda bekerja dengan Power BI atau PowerPivot dan tidak menginstal DAX Studio, saya punya kabar buruk untuk Anda… Jika demikian, atau jika Anda belum menginstal add-in untuk Excel, buka https. //daxstudio. org/, klik tombol hijau besar untuk mengunduh rilis terbaru dan menginstalnya. Selama penginstalan, jangan lupa untuk menginstal tidak hanya aplikasi saja, tetapi juga add-in Excel. Itu perlu

Langkah 2. Stabil…

Untuk menyambungkan Power Query ke model data buku kerja Anda, buka buku kerja Anda. ). Untuk beberapa alasan saya bersikeras bahwa hanya buku kerja ini yang dibuka. Ini dan hanya ini. Jika beberapa buku kerja dibuka secara bersamaan, mungkin ada kekacauan dan masalah

Jalankan add-in DAX Studio dari grup pita “Add-in” di Excel. Setelah DAX Studio dimulai, Anda akan melihat jendela koneksi, di mana Anda harus memilih opsi pertama

Excel menyalin model data ke buku kerja lain
Hubungkan DAX Studio ke file Excel

Klik "Hubungkan". Setelah koneksi dibuat, jangan tutup DAX Studio, itu harus berjalan. Anda dapat meminimalkannya jika Anda mau

Sekarang semuanya siap terhubung

Langkah 3. Pergi

Sekarang buka Power Query dan sambungkan ke «database SQL Server Analysis Services (import)»

Kita perlu mengetahui alamat server untuk itu. Jika koneksi ini ke beberapa server atau Power BI Desktop, kami kemudian dapat melihat alamatnya di sudut kanan bawah jendela DAX Studio

Excel menyalin model data ke buku kerja lain
Membuka alamat port file PBIX di DAX Studio

Tetapi saat terhubung ke file Excel, kami hanya melihat ini

Excel menyalin model data ke buku kerja lain
Tidak ada server. alamat port untuk file Excel. 🙁

Tidak ada nama server, tidak ada alamat, tidak ada nomor port

Sekarang saya mengungkapkan rahasia utamanya

Anda harus menggunakan http://localhost:9000/xmla sebagai nama/alamat server. Itu saja

Tentu saja, Anda juga dapat menggunakan nama database ini (perlu menuliskannya jika Anda ingin menggunakan kueri MDX atau DAX khusus terkait). “Microsoft_SQLServer_AnalysisServices_”

Excel menyalin model data ke buku kerja lain
Opsi koneksi

Bagaimana saya mengungkapkan alamat ini terlalu TL;DR bahkan untuk saya 😊. Perlu Anda ketahui bahwa alamat server dan nama database ini selalu sama, setidaknya pada saat saya menulis postingan ini, jadi Anda tidak perlu mencari alamat server setiap saat.

Saat Power Query bertanya tentang kredensial sumber data, cukup gunakan otorisasi Windows Anda saat ini (maaf jika Anda tidak dapat membaca tangkapan layar dalam bahasa Rusia, lihat saja opsi mana yang saya pilih)

Excel menyalin model data ke buku kerja lain
Gunakan otentikasi Windows Anda saat ini untuk terhubung

Bendungan

Excel menyalin model data ke buku kerja lain
Power Query tersambung ke model data PowerPivot ke kubus

Sekarang Anda melihat modelnya, semua kolom terhitung dan ukurannya sebagai kubus OLAP. Anda dapat membuat tabel apa pun yang Anda butuhkan darinya

Jika Anda terbiasa dengan kubus MD dan perilakunya, maka Anda baik-baik saja 😊 Jika tidak, Anda perlu waktu untuk menyesuaikan diri dengan logika kubus dan menggunakannya

Baca model PowerPivot dari Power BI

Jika karena alasan tertentu Anda tidak ingin mengimpor model data Excel di Power BI, Anda dapat menggunakan trik ini untuk terhubung ke PowerPivot di Power BI Desktop. Tetapi sekali lagi, hanya dalam mode impor, tidak ada koneksi langsung

Excel menyalin model data ke buku kerja lain
Power BI dapat tersambung ke model PowerPivot hanya dalam mode impor

Semua langkahnya SAMA

Tujuan

Bagi saya, trik ini adalah salah satu dari daftar panjang “hanya karena saya bisa”. Sejujurnya, saya tidak melihat aplikasi yang berguna untuk koneksi ini hari ini. Itu terlalu rumit, terbatas pada penyegaran manual, dan tidak dapat digunakan di buku kerja lain. Tetapi jika Anda memiliki kebutuhan mendesak untuk mengekstrak beberapa data dari model data Excel ke PQ, Anda dapat menggunakan pendekatan ini dengan hati-hati. Misalnya, Anda mungkin perlu melakukan beberapa operasi mashup data di Power Query, tetapi beberapa di antaranya terlalu rumit di Power Query dan memiliki kinerja yang sangat buruk di sana (biasanya tentang mashup yang memerlukan banyak pemindaian dari tabel yang sama) . Anda dapat mengekspor hasil langkah tengah dalam model data dan melakukan penghitungan yang diperlukan di PowerPivot, lalu membacanya lagi di Power Query

Atau Anda dapat menggunakannya untuk melakukan kueri MDX atau DAX apa pun yang sesuai atas data Anda

Excel menyalin model data ke buku kerja lain
Kueri DAX dijalankan melalui model PowerPivot

Saya pikir mungkin ada kebutuhan lain yang dapat dipenuhi dengan trik ini dan belum saya penuhi. Jika Anda memiliki beberapa dalam pikiran jangan ragu untuk berbagi

Perhatian dan masalah yang diketahui

  1. Ini tidak berdokumen
  2. Ini tidak didukung
  3. Ini bisa berubah kapan saja, tidak ada jaminan
  4. Saat menghubungkan dari Power BI Desktop, pastikan hanya satu buku kerja yang dibuka – setidaknya, hanya satu buku kerja dengan model data di dalamnya
  5. Tidak, saya tidak tahu cara membaca model PowerPivot di buku kerja lain. Semua usaha saya gagal;
  6. Untuk mendapatkan urutan penyegaran yang benar dari kueri dan tabel Anda, Anda perlu melakukannya secara manual (atau melalui makro VBA). Mengklik tombol “Segarkan Semua” di Excel tidak menjamin urutan penyegaran, jadi tidak semua perubahan Anda dapat tercermin dengan benar. Atau tekan "Segarkan Semua" dua kali

Ada banyak hal menarik lainnya dan perangkap dengan koneksi ini, tapi saya tidak ingin menghabiskan terlalu banyak waktu sekarang. Setidaknya tidak sampai saya melihat alasan yang bagus atau kasus penggunaan untuk itu

Semoga beruntung

Ikuti aku.
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain

Bagikan ini.
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain
Excel menyalin model data ke buku kerja lain

Bagikan ini

Maxim Zelensky 30 Juni, 2020

Power BI / Power Query /

Air Terjun untuk Air Terjun (Waterfall) di Power BI

Особенность загрузки текстовых столбцов di Power BI dan PowerPivot, atau Как найти дубликаты там, где нет

Bisakah Anda menyalin gaya sel khusus dari satu buku kerja ke buku kerja lainnya?

Anda dapat menyalin gaya sel dari buku kerja itu ke buku kerja lain . Buka buku kerja yang berisi gaya sel yang ingin Anda salin. Buka buku kerja tempat Anda ingin menyalin gaya. di samping kotak gaya sel yang berisi pratinjau gaya.

Bagaimana cara menarik data secara otomatis dari satu buku kerja Excel ke buku kerja Excel lainnya?

Gunakan Tautan Salin dan Tempel untuk mentransfer data secara otomatis dari satu lembar kerja Excel ke lembar kerja Excel lainnya .
Buka dua spreadsheet yang berisi kumpulan data sederhana yang sama
Di lembar 1, pilih sel dan ketik Ctrl + C / Cmd + C untuk menyalinnya
Di lembar 2, klik kanan pada sel yang setara dan buka Tempel > Tautan

Bagaimana cara menyalin spreadsheet Excel ke lembar lain dengan format yang sama?

Cukup tahan tombol Ctrl, lalu klik dan seret tab lembar. Saat Anda melepaskan mouse, Excel akan membuat salinan persis dari lembar tersebut