LuckyTemplates Power Query: Mengalih keluar Baris Pendua

Dalam blog ini, saya akan menunjukkan kepada anda cara untuk mengalih keluar baris pendua dalam LuckyTemplates Power Query berdasarkan syarat. Anda boleh menonton video penuh tutorial ini di bahagian bawah blog ini.

Contoh ini adalah daripada soalan yang dibangkitkan di . Ahli mempunyai data transaksi dan mahu mengalih keluar baris yang membatalkan satu sama lain.

Terdapat dua jenis transaksi yang berbeza, satu untuk masuk dan satu untuk keluar. Dia juga menambah kiraan pergerakan. Dan berdasarkan itu, dia merumuskan tiga senario.

Senario satu adalah berdasarkan dua baris. Jika mereka membatalkan satu sama lain, kedua-dua baris hendaklah dialih keluar. Jika itu tidak berlaku, maka kedua-dua baris hendaklah disimpan.

Senario dua dan tiga adalah berdasarkan gabungan baris, sebahagiannya membatalkan satu sama lain. Baris tersebut hendaklah dialih keluar pada asas masuk dahulu supaya hanya transaksi masuk terkini disimpan.

Pada asasnya, kami mengulangi logik yang sama berulang kali. Kita seharusnya dapat mereka bentuk pendekatan tunggal untuk menyesuaikan semua senario ini.

Beberapa perkara yang akan kami lakukan ialah menambah lajur dengan kuantiti mutlak dan kumpulan dengan "tambah lajur baki kuantiti" dan jadual bersarang menggunakan pilihan "semua baris". Kami juga akan mengubah jadual bersarang tersebut, mengisih jenis pergerakan dan tarikh penyiaran, mencipta jumlah berjalan dan hanya menyimpan baris berdasarkan syarat.

Saya telah menilai ini pada tahap pertengahan kerana kebanyakan transformasi yang boleh kita lakukan di sini adalah dengan menggunakan antara muka pengguna. Dengan itu, mari kita pergi ke LuckyTemplates Power Query.

Isi kandungan

• Menambah Lajur Dengan Kuantiti Mutlak Dalam Pertanyaan Kuasa
• Kumpulan Mengikut Indeks ID & Kuantiti Mutlak
• Mengubah Jadual Bersarang Dalam LuckyTemplates Power Query
  • Menyusun Jenis Pergerakan & Tarikh Penyiaran
  • Mencipta Jumlah Berjalan
  • Menapis Baris Berdasarkan Keadaan
• Kesimpulan

Menambah Lajur Dengan Kuantiti Mutlak Dalam Pertanyaan Kuasa

Kami akan mulakan dengan menambah lajur dengan kuantiti mutlak. Pilih lajur Kuantiti , kemudian pada reben atau tab Tambah Lajur , pergi ke Saintifik , kemudian klik pada Nilai Mutlak .

Saya akan menamakan semula lajur ini dalam bar formula.

Kumpulan Mengikut Indeks ID & Kuantiti Mutlak

Kini kita boleh mengumpulkan mengikut Indeks ID dan Kuantiti Mutlak. Pilih Indeks ID , kemudian tahan anjakan atau kawalan untuk berbilang pilih, dan kemudian klik pada Abs qty (kuantiti mutlak).

Anda akan menemui Kumpulan mengikut pada tab Transform.

Tetapi, anda juga boleh menemuinya pada tab Laman Utama.

Klik padanya dan sekarang, kami akan menambah lajur baharu. Ia bukan "kiraan", tetapi ia akan menjadi "jumlah" kuantiti. Jadi, mari kita namakan semula kepada Qty (kuantiti) Baki . Ia akan menjadi jumlah lajur kuantiti. Kemudian, kami akan menambah pengagregatan lain, tetapi ia akan menjadi pengagregatan yang istimewa, jadi kami akan memilih Semua Baris . Mari kita beri nama lajur ini juga ( AllRows ). Kemudian, tekan OK.

Jika kita mengklik dalam ruang putih dalam jadual, kita melihat pratonton jadual bersarang di bawah. Kami melihat bahawa kuantiti untuk jadual bersarang ini membatalkan satu sama lain dan baki kuantiti adalah sifar.

Jika kita melihat jadual bersarang seterusnya (jadi klik ke sebelah dalam ruang putih baris seterusnya), kita melihat bahawa lajur kuantiti tidak membatalkan satu sama lain dan baki kuantiti lebih besar daripada sifar.

Mari tambahkan lajur tersuai supaya kita boleh menggunakannya untuk transformasi jadual bersarang kita nanti. Jadi, pilih Tambah Lajur Tersuai .

Mari namakan semula lajur itu kepada Transformasi dan kami akan menambah sifar (0) sebagai ruang letak. Kemudian, klik OK.

Mengubah Jadual Bersarang Dalam LuckyTemplates Power Query

Kami kini bersedia untuk mencipta logik untuk mengubah jadual bersarang tersebut. Tetapi kami tidak mahu menulis sekumpulan mereka, dan kami tidak perlu.

Kami boleh menggunakan antara muka pengguna untuk sebahagian besar. Jika kita membina logik untuk transformasi tersebut dalam pertanyaan yang berasingan, senario paling rumit yang kita ada ialah senario dengan sama ada tiga atau empat baris.

Sekarang, jika saya mengklik pada sebelah kanan dalam baris terakhir, saya melihat bahawa jadual ini mempunyai empat baris. Jadi, kita boleh menggunakan ini untuk membina transformasi kita.

Klik kanan pada sebelah dalam ruang putih dan pilih Tambah sebagai Pertanyaan Baharu .

Kami melihat bahawa jadual bersarang kini telah berkembang dan ditambah sebagai pertanyaan baharu dan kami boleh menggunakannya untuk membina logik yang kami perlukan.

Menyusun Jenis Pergerakan & Tarikh Penyiaran

Sekarang, perkara pertama yang perlu kita lakukan ialah mengisih pada Jenis Pergerakan . Jom pilih turun . Dengan cara ini, urus niaga keluar akan sentiasa berada di atas.

Seterusnya, kami akan mengisih Tarikh Penyiaran sebagai menaik , memastikan bahawa jika kami meninggalkan baris, ia akan sentiasa dilakukan mengikut prinsip FIFO (pertama masuk dahulu keluar).

Mencipta Jumlah Berjalan

Untuk mengenal pasti baris yang akan kami alih keluar, saya akan menambah jumlah berjalan untuk melumpuhkan dan kami boleh menggunakan List.FirstN untuk tugasan itu. Fungsi ini mencipta senarai berdasarkan senarai lain, di mana item teratas disimpan berdasarkan nombor tertentu atau pada kriteria.

Mari kembali kepada pertanyaan kami. Kami memerlukan dua perkara untuk List.FirstN. Pertama, kami memerlukan senarai itu, dan senarai itu ialah lajur Kuantiti kami . Jika saya klik kanan pada pengepala lajur Kuantiti, kami mendapat pilihan untuk Tambah sebagai NewQuery .

Dan di sini, kita melihat kod yang kita perlukan untuk menjana senarai itu. Dalam langkah yang digunakan, anda boleh melihat bahawa ia menunjuk ke langkah terakhir dan kemudian mengenal pasti lajur dalam kurungan tersebut.

Bahagian kedua yang kita perlukan untuk List.FirstN ialah nombor untuk mengenal pasti bilangan nombor yang perlu disimpan daripada senarai itu. Kita boleh menggunakan indeks untuk itu, jadi saya akan pergi menambah lajur indeks daripada satu (1) untuk baris ini.

Jadi untuk nombor 1, ia akan mengekalkan nombor pada baris pertama dalam lajur Kuantiti, dan seterusnya. Dan kini kita boleh merumuskannya.

Mari tambahkan lajur tersuai dan panggil ia Running Total . Kami menggunakan List.FirstN di sini dan kami dapat melihat bahawa parameter pertama ialah senarai itu, lajur Kuantiti. Kita perlu menunjuk ke langkah terakhir, dan langkah terakhir itu ialah Indeks Ditambah .

Kami mengenal pasti lajur yang kami mahu, dan itu lajur Kuantiti kami. Kiraan kami adalah dalam lajur Indeks.

Kini ia mengembalikan senarai. Jika kita klik pada bahagian tepi dalam ruang putih, kita boleh melihat kandungan senarai itu. Untuk rekod pertama, ia hanya mengekalkan baris teratas daripada senarai itu. Untuk rekod kedua, ia mengekalkan dua baris teratas.

Sekarang apa yang perlu kita lakukan ialah meringkaskan kuantiti ini dan kita boleh menggunakan Lists.Sum untuk melakukannya. Jadi, saya akan menambahnya dalam bar formula. Dan saya juga akan meletakkan Type .

Menapis Baris Berdasarkan Keadaan

Dengan jumlah berjalan ini, kami boleh mengenal pasti baris yang ingin kami simpan. Kami hanya mahu mengekalkan baris yang lebih besar daripada sifar, jadi mari tambahkan keadaan penapis.

Kini kami boleh mengalih keluar lajur pembantu kami. Pilih Indeks dan Jumlah Berjalan, kemudian klik Alih Keluar Lajur.

Dalam Editor Lanjutan, kami kini boleh menyalin kod yang telah kami buat. Saya akan membuka Editor Lanjutan , dan kita dapat melihat bahawa ini adalah langkah berpecah kita. Jadi, kita boleh memilih dan menyalin semua di bawah itu.

Sekarang mari kita kembali ke pertanyaan asal kita. Sekali lagi, buka Editor Lanjutan. Dan bukannya pemegang tempat, mari beralih ke baris baharu. Saya akan menggunakan ungkapan " biarkan " kerana ia boleh menangkap nilai daripada pengiraan perantaraan dalam pembolehubah. Jadi, "biar" dan kemudian pergi ke baris baharu dan tampalkan transformasi saya. Saya juga akan mengisytiharkan Type .

Kod yang diserlahkan di atas menunjukkan jadual bersarang kami yang diperluaskan, yang diletakkan dalam lajur Semua Baris. Jadi, saya akan menunjukkannya, dengan menggantikan kod yang diserlahkan ini kepada AllRows .

Jadi lajur transformasi kami kini mempunyai jadual bersarang. Kami membina pertanyaan kami berdasarkan baris terakhir, bukan? Dan itu mengandungi empat baris apabila kami mula dan hanya mengandungi dua. Kami juga mempunyai sifar kuantiti yang seimbang, bukan? Dan kini ia mempunyai meja kosong.

Kami boleh mengalih keluarnya dengan menapis pada lajur Baki Kuantiti. Kami tidak mahu baki kuantiti sama dengan sifar.

Dan kemudian, saya akan memilih lajur transformasi saya dan mengalih keluar lajur lain. Saya kini boleh mengembangkan jadual bersarang transformasi saya. Jangan gunakan nama lajur asal sebagai awalan dan tekan okay.

Pada tab Transform, pilih Jenis Data , dan kita sudah selesai. Dan begitulah cara anda membersihkan data transaksi.

Nyahpivot Dan Asas Pangsi Dalam LuckyTemplates – Editor Pertanyaan Semak
Petua Amalan Saya Apabila Menggunakan Editor Lanjutan LuckyTemplates
Transformasi Lanjutan Dalam LuckyTemplates

Kesimpulan

Dalam tutorial ini, saya menunjukkan kepada anda cara membersihkan data transaksi, khususnya mengalih keluar baris pendua. Ini ialah teknik hebat yang boleh anda gunakan apabila menggunakan LuckyTemplates Power Query.

Saya harap anda menikmati yang ini. Tonton tutorial video penuh di bawah untuk butiran lanjut. Semak pautan juga di bawah untuk lebih banyak kandungan berkaitan sekitar Editor LuckyTemplates Power Query.

Cheers!

Melissa