Melampirkan Beberapa Helaian Dalam Excel Kepada LuckyTemplates

Tutorial ini akan membincangkan tentang cara mengimport dan membuka fail Excel dengan berbilang helaian kepada satu jadual LuckyTemplates.

Fail Excel data ini akan digunakan untuk tutorial ini.

Fail mengandungi 60 helaian data dengan susun atur yang sama.

Objektif tutorial ini adalah untuk memuatkan semua helaian Excel ke LuckyTemplates dan mendapatkan semula maklumat Bon sebagai satu jadual. Anda perlu mendapatkan ticker Keselamatan dan menambahnya dalam lajur yang berasingan. Kemudian, tambahkan kesemuanya bersama-sama dalam satu jadual yang memanfaatkan UI untuk menjana kod yang anda perlukan.

Isi kandungan

• Mengimport Helaian Excel Untuk Menguasai BI
• Mengeluarkan Ticker Keselamatan Daripada Excel Kepada LuckyTemplates
• Mengalih keluar Baris Kosong
• Menduakan Pertanyaan
• Menetapkan Jenis Data Kepada Lajur
• Menambah Lajur Ticker Keselamatan Daripada Excel Kepada LuckyTemplates
• Mencipta Lajur Tersuai Untuk Pertanyaan Bon
• Kesimpulan

Mengimport Helaian Excel Untuk Menguasai BI

Perkara pertama yang perlu dilakukan ialah membuka Editor Pertanyaan Kuasa dan buat parameter untuk menyimpan lokasi fail menggunakan pilihan Urus Parameter .

Di dalam kotak Urus Parameter, klik Baharu dan masukkan Lokasi Fail sebagai nama parameter. Seterusnya, tetapkan Jenis Data kepada Teks dan Nilai Dicadangkan kepada Sebarang Nilai . Salin laluan fail fail Excel dan tampalkannya dalam medan Nilai Semasa.

Selepas menekan OK, klik Sumber Baharu dan pilih Buku Kerja Excel .

Seterusnya, pilih dan buka Fail Excel. Setelah selesai, skrin Navigator akan dibuka dan menyenaraikan semua helaian dalam fail Excel.

Pilih helaian pertama dan tekan OK. Selepas itu, anda akan melihat jadual di dalam Power Query Editor.

Perkara seterusnya yang perlu dilakukan ialah menukar laluan fail berkod keras ke dalam parameter fail. Buka tetingkap Advanced Editor dan tukar laluan fail dalam kod Sumber kepada FileLocation .

Adalah lebih baik untuk mempunyai data anda dalam jadual Excel kerana sempadan atau julat data ditentukan sebelum anda membawanya ke Power Query . Memandangkan fail mengandungi helaian dan tiada jadual, ia berisiko membawa masuk lajur kosong dan baris kosong jadi anda perlu menyusunnya.

Mengeluarkan Ticker Keselamatan Daripada Excel Kepada LuckyTemplates

Mengetahui bahawa susun atur helaian adalah tetap membantu dalam membina penyelesaian, terutamanya jika anda ingin memaksimumkan dan menggunakan antara muka pengguna untuk menjana kod. Sebagai contoh, satu keperluan ialah menambah lajur yang mengandungi ticker Keselamatan. Jika anda melihat data, anda boleh melihat ticker.

Antara muka pengguna boleh membantu anda mengekstrak nilai ticker. Klik kanan pada sel dan pilih Tambah Sebagai Pertanyaan Baharu .

Dalam bar formula, anda akan melihat bahawa nama jadual diikuti dengan nombor baris berasaskan sifar antara kurungan kerinting. Tanda kurung kerinting dipanggil pengendali indeks kedudukan . Anda juga akan melihat nama medan yang disertakan dengan kurungan segi empat sama yang dipanggil operator akses medan .

Dengan kod tersebut, anda kini boleh mengekstrak nilainya. Kembali ke jadual Bond dan dapatkan data Bon. Mula-mula, keluarkan 8 lapan baris teratas. Klik Alih Keluar Baris dan pilih Alih Keluar Baris Teratas .

Seterusnya, masukkan 8 dalam medan Number Of Rows dan tekan OK.

Kemudian, klik Gunakan Baris Pertama Sebagai Pengepala untuk menetapkan pengepala.

Setelah selesai, jadual Bond kini akan kelihatan seperti ini.

Tepat di bawah pengepala, anda akan melihat garis nipis yang mewakili kualiti lajur. Dari situ, anda dapat melihat bahawa terdapat banyak ruang kosong dalam lajur. Ini bermakna fail itu membawa banyak baris kosong.

Mengalih keluar Baris Kosong

Untuk mengalih keluar baris kosong, klik Alih Keluar Baris dan pilih Alih Keluar Baris Kosong .

Transformasi itu menjana sintaks ini:

Record.FieldValues ​​mendapat semua nilai daripada baris semasa dalam jadual sebagai senarai. List.RemoveMatchingItems mengalih keluar semua nilai dalam senarai pertama yang mempunyai padanan dalam senarai kedua. Senarai kedua hanya mengandungi rentetan teks kosong atau null. Ini ialah nilai yang akan dikecualikan daripada senarai pertama.

Jika semua rentetan teks kosong dan null telah dialih keluar daripada senarai dengan nilai medan rekod, senarai itu hendaklah kosong dan List.IsEmpty akan menilai kepada Benar. Kemudian, Table.SelectRows akan mengekalkan Trues.

Anda tidak sepatutnya berakhir dengan jadual dengan hanya baris kosong. Itulah sebabnya kata kunci not ditambahkan sebelum List.IsEmpty . Itu kemudian mengembalikan jadual yang mengandungi baris bukan kosong.

Selain daripada baris kosong, anda juga perlu mengalih keluar lajur kosong. Tetapi sebelum itu, lihat apa yang dijana Power Query apabila anda mengalih keluar lajur. Pilih lajur keempat dan klik Alih keluar lajur .

Selepas melakukan transformasi, anda akan melihat sintaks ini dalam bar formula.

Ia memanggil fungsi Table.RemoveColumns kemudian merujuk dan melepasi langkah sebelumnya dalam anak tetingkap Applied Steps sebagai hujah pertama. Transformasi seterusnya melepasi senarai yang mengandungi nama lajur lajur yang anda mahu alih keluar.

Menduakan Pertanyaan

Sekarang, salin pertanyaan dan pilih langkah Promosikan Pengepala dalam anak tetingkap Langkah Gunaan. Seterusnya, klik kanan pada langkah itu dan pilih Padam Hingga Tamat .

Ingat bahawa anda boleh menggunakan operator indeks kedudukan untuk menghantar nombor baris berasaskan sifar dalam set kurungan kerinting. Jadi simpan hanya baris pertama dengan memasukkan 0 dalam dua kurungan kerinting dalam bar formula.

Setelah selesai, jadual Bond akan kelihatan seperti ini.

Dari sana, gunakan semula sebahagian daripada logik yang dicipta oleh langkah Baris Kosong yang Dialih Keluar untuk menukar rekod menjadi senarai dan untuk mengalih keluar nol. Kembali ke pertanyaan Bon dan pilih langkah Alih Keluar Baris Kosong. Seterusnya, salin kod M ini.

Kembali ke pertanyaan pendua dan tampal kod dalam bar formula. Kemudian, susun beberapa rentetan untuk menjadikan formula kelihatan seperti ini.

Anda kini telah membuat senarai dengan nama lajur yang anda mahu simpan. Namakan semula Nama Lajur pertanyaan .

Kemudian, kembali ke pertanyaan Bon. Memandangkan anda telah membuat pertanyaan yang mengandungi semua lajur yang anda ingin simpan, anda perlu menyenaraikan semua lajur yang anda mahu kecualikan dalam fungsi Table.RemoveColumns .

Tukar {Column4} dengan sintaks yang sama yang telah disalin daripada langkah Baris Kosong Dialih Keluar. Tukar juga {“”, null} kepada ColumnNames .

Kemudian, anda perlu lulus senarai dengan nama lajur sebenar daripada jadual Bond. Tukar Record.FieldValues(_) kepada Table.ColumnNames() . Masukkan #”Baris Kosong Dialih Keluar” di dalam kurungan untuk menghantar hujah rujukan jadual.

Menetapkan Jenis Data Kepada Lajur

Perkara seterusnya yang perlu dilakukan ialah menetapkan jenis data yang sesuai kepada lajur. Untuk lajur Tarikh, klik ikon di sebelah pengepala dan pilih Tarikh.

Untuk lajur PX_LAST dan YLD_YTM_MID, pilih jenis data Nombor Perpuluhan.

Dengan tiga pertanyaan ini, anda telah mencipta semua blok binaan yang boleh anda gunakan untuk mereka bentuk penyelesaian yang menangani semua helaian dalam fail Excel . Untuk itu, anda perlu menduplikasi pertanyaan Bon dan memadam semua langkah kecuali langkah Sumber dalam anak tetingkap Langkah Gunaan.

Menambah Lajur Ticker Keselamatan Daripada Excel Kepada LuckyTemplates

Dalam langkah Sumber, anda boleh melihat semua data dalam fail Excel. Daripada membuat pertanyaan berasingan untuk setiap helaian, anda boleh menggunakan pertanyaan Bon dan mengubah jadual bersarang dalam lajur Data.

Mula-mula, tambahkan ticker Keselamatan. Jika anda mengklik ruang putih di dalam sel mana-mana jadual, anda akan melihat pratonton kandungan jadual itu.

Anda perlu mencipta logik menggunakan pernyataan untuk mendapatkan ticker Keselamatan. Pergi ke pertanyaan Column2 dan salin alamat ticker Security dari bar formula.

Seterusnya, kembali ke pertanyaan Bon dan tambah lajur tersuai.

Namakan lajur Security Ticker dan tulis kod M berikut.

Formula tersebut mempunyai pernyataan IF yang mengatakan bahawa jika perkataan Security ditemui dalam Column1, ia akan memberikan nilai sel daripada Column2 yang bersebelahan dengannya. Jika tidak, batal akan diberikan.

Selepas menekan OK, lajur baharu dengan ticker Keselamatan akan ditambahkan pada jadual.

Klik butang lungsur turun lajur Security Ticker dan nyahpilih null untuk mengalih keluar semua null dalam lajur.

Selepas itu, anda akan ditinggalkan dengan semua maklumat Bon dari semua helaian. Apa yang anda perlu lakukan ialah mengulangi transformasi yang dilakukan yang dilakukan dalam pertanyaan Bond(2) dan gunakannya pada jadual bersarang dalam lajur Data.

Mencipta Lajur Tersuai Untuk Pertanyaan Bon

Pergi ke pertanyaan Bond(2), buka tetingkap Advanced Editor dan salin kod berikut:

Seterusnya, kembali ke pertanyaan Bon dan buat lajur tersuai lain. Memandangkan anda perlu menggunakan berbilang transformasi merentas berbilang langkah, anda perlu menggunakan pernyataan let . Jadi, masukkan biarkan dan tampal kod daripada Editor Lanjutan.

Kemudian, tukar Bond_Sheet kepada Data untuk mengubah jadual dalam lajur Data.

Selepas menekan OK, lajur baharu akan ditambah dalam jadual.

Jika anda mengklik ruang putih di dalam mana-mana sel lajur itu, anda akan melihat data daripada pertanyaan Bond(2).

Apa yang anda perlukan adalah dalam dua lajur terakhir pertanyaan Bon. Jadi, pilih kedua-dua lajur Security Ticker dan Custom dan klik Alih Keluar Lajur Lain daripada reben Alih Lajur.

Kembangkan lajur Tersuai dan nyahtanda kotak Gunakan Nama Asal Sebagai Awalan.

Setelah selesai, sahkan jadual. Di bawah jadual, pilih pilihan Pemprofilan Lajur Berdasarkan Seluruh Set Data .

Selepas transformasi itu, ralat akan berlaku dalam lajur Data.

Jadi periksa ralat dengan mengklik lajur Data dan memilih Keep Errors dalam reben Keep Rows.

Jika anda mengklik nilai Ralat di dalam lajur Data, anda boleh membaca mesej ralat di bawah:

Untuk menyelesaikan isu, alih keluar langkah Kept Errors dahulu. Klik lajur Data dan pilih Alih Keluar Ralat daripada reben Alih Keluar Baris.

Kemudian, tetapkan pilihan pemprofilan lajur kembali ke 1000 baris teratas. Dan itu sahaja!

Kesimpulan

Penyelesaian dipacu antara muka pengguna ini boleh membantu anda menambahkan berbilang helaian daripada fail Excel ke LuckyTemplates. Daripada membuat 60 pertanyaan berasingan dan melakukan semua transformasi berulang kali, penyelesaian ini boleh mencipta satu pertanyaan yang melaksanakan semua transformasi. Gunakan dan maksimumkan penyelesaian ini untuk membina laporan data yang hebat .

Melissa