Tutorial Microsoft Power Query Mengenai Cara Membetulkan Isu Lebar Lajur Tetap Campuran

Mudassir: Untuk hari ini, kami mempunyai masalah yang sangat menarik untuk diselesaikan. Masalah dengan fail ini ialah ia dibatasi oleh lajur dan saya tidak tahu cara menyelesaikannya menggunakan Microsoft Power Query. Anda boleh menonton video penuh tutorial ini di bahagian bawah blog ini.

Pertama, tidak mudah bagi saya untuk memadamkan lajur secara dinamik. Kedua, dalam laporan ini, kami mempunyai satu jadual dengan lebar lajur yang berbeza, dan kemudian jadual lain dengan lebar lajur yang berbeza.

Jadi jika saya menggunakan satu pembatas secara dinamik di bahagian atas, saya tidak akan dapat mendapatkan data dengan kemas. Saya mahu mendapatkan semua data ini daripada jadual kedua dan nombor produk saya daripada jadual pertama. Saya juga mahu nombor kerja dalam setiap baris semua jadual.

Saya cuba menyelesaikannya sendiri, tetapi kerana ini ada kaitan dengan pertanyaan kuasa saya memerlukan bantuan daripada Melissa. Saya fikir ia akan mengambil masa sekurang-kurangnya dua hari, tetapi dia berjaya mencari penyelesaian dengan segera.

Melissa akan menunjukkan kepada kita bagaimana dia menyelesaikan masalah rumit ini. Saya fikir kebanyakan orang akan menghadapi masalah seperti ini dan mencari cara bagaimana untuk menyelesaikannya.

Melissa: Petua pertama ialah jika anda melihat fail dengan panjang tetap, anda boleh pergi ke tab Lihat dan menghidupkan pilihan Monospaced itu.

Kita dapat melihat bahawa ia adalah fon panjang tetap. Kita juga boleh melihat pengepala, jadual awal dan subjadual. Ini adalah bahagian yang kami minati dan ingin kami ekstrak.

Selain itu, sila pastikan bar formula anda didayakan. Adalah satu perkara yang baik untuk memaparkannya pada skrin anda kerana kami akan menggunakannya dengan kerap untuk membuat sedikit pengubahsuaian pada input.

Saya telah mencipta parameter untuk lokasi fail tempat saya menyimpan fail CSV. Saya membawanya sebagai fail pementasan dan menyalinkannya dalam parameter lokasi fail saya. Kemudian saya membuat rujukan dan saya akan bekerja dari rujukan itu. Jadi itulah yang kita lihat sekarang dalam pertanyaan kuasa Microsoft.

Isi kandungan

• Menambah Lajur Indeks
• Mencipta Senarai Penampan Dalam Microsoft Power Query
• Memisahkan Pengepala Daripada Baris
• Mengalih Keluar Nol Dan Kosong Dalam Microsoft Power Query
• Membahagi Bahagian
• Memangkas Rentetan Teks Dalam Microsoft Power Query
• Kesimpulan

Menambah Lajur Indeks

Biasanya, apabila saya mula bekerja pada fail seperti ini, saya perlu mengetahui keperluan pelanggan. Saya bertanya apa yang pelanggan perlukan, dan apa yang perlu dicari.

Dalam kes ini, kami mahu nombor item dan nombor tugas daripada pengepala, dan kemudian kami mahu semua butiran yang dimiliki oleh pengepala khusus itu.

Kami memerlukan kunci untuk menyatukan semula perkara itu. Tetapi jika tiada kunci hadir, maka tujuan saya ialah menambah lajur indeks . Saya akan klik pada ikon jadual mini, pilih Tambah Lajur Indeks , kemudian tambah Daripada 0 .

Saya akan menggunakan logik kemudian dengan senarai, yang mempunyai indeks berasaskan 0. Mempunyai indeks anda bermula dari sifar sebenarnya bermakna anda boleh merujuk baris yang sama. Jika tidak, anda perlu menolak 1 untuk mencapai kedudukan berasaskan 0 itu.

Kemudian, kita perlu mencari kedudukan di mana pengepala kita berada, yang boleh kita lakukan dengan mudah kerana pengepala tersebut diulang secara berterusan sepanjang keseluruhan fail.

Untuk memulakan, mari salin nilai ini:

Tambahkan pertanyaan kosong baharu, tampalkannya dan panggil ini HeaderID .

Saya akan melakukan proses yang sama untuk subjadual. Saya akan menyalin rentetan teks itu, mencipta pertanyaan kosong lain dan menampal nilai itu. Ini akan menjadi rentetan yang akan kami gunakan semasa mencari baris terperinci.

Jika proses ini entah bagaimana mengubah pengepala untuk mana-mana jadual ini, apa yang saya perlu lakukan ialah menukar salah satu rentetan teks dan fail akan berfungsi semula.

Saya tidak perlu menyelami kod M untuk mencari rentetan yang kami cari. Kita hanya boleh menggunakan ini sebagai parameter.

Mari dayakan beban untuk dua pertanyaan ini.

Mencipta Senarai Penampan Dalam Microsoft Power Query

Perkara pertama yang saya akan lakukan ialah menukar Column1 menjadi senarai dengan merujuk dan memuatkannya ke dalam memori sekali. Dengan cara ini, saya tidak perlu membuat panggilan berulang ke fail.

Saya akan membuka editor lanjutan dan meletakkannya sepenuhnya di bahagian atas. Apabila anda menggunakan antara muka pengguna untuk membina kod anda, ia akan merujuk kepada langkah sebelumnya.

Apabila anda meletakkan langkah penimbal di tempat lain dalam kod anda dan anda ingin membuat pengubahsuaian, kemudiannya, langkah ini akan membantu anda membuat perubahan pada langkah yang anda buat secara manual.

Saya akan memanggil BufferList ini dan rujukan Column1. Untuk memuatkannya ke dalam memori, saya akan menambah langkah List.Buffer .

Inilah pembolehubah saya sepanjang jalan di bahagian atas. Saya boleh merujuknya berulang kali.

Perkara pertama yang saya ingin tentukan ialah tempat pengepala saya bermula kerana saya memerlukan kunci untuk menyimpan bahagian pengepala ini dan mendapatkan satu nilai untuk semua baris tersebut. Untuk melakukan ini, saya akan menambah lajur tersuai dan memanggilnya Pengepala .

Saya akan menulis bahawa jika Column1 adalah sama dengan ID Pengepala kami, maka saya mahu nombor indeks saya menjadi batal.

Akibatnya, ia menemui teks dan mengembalikan 5 dan 23.

Saya memerlukan nilai itu merentas semua baris, jadi saya perlu mengisinya. Anda hanya boleh klik kanan untuk mengisi, tetapi anda juga boleh menggunakan sintaks yang sangat mudah dan menambahnya dalam bar formula.

Dalam kes ini, saya menambah Table.FillDown dan dalam rentetan teks, saya menunjukkan lajur yang ingin kita isi (Pengepala).

Sekarang, kami telah mengisinya untuk semua baris. Kami mempunyai kunci untuk semua bahagian pengepala dan semua bahagian baris kerana kesemuanya berkongsi nilai ini.

Memisahkan Pengepala Daripada Baris

Langkah seterusnya ialah memisahkan tajuk daripada baris. Saya akan menambah satu lagi lajur tersuai dan memanggilnya Temp . Kali ini, kita akan melakukan sesuatu yang lebih terperinci dan memanfaatkan BufferList yang saya buat sebelum ini.

Kami akan menggunakan beberapa fungsi senarai untuk melihat setiap kedudukan dan mencari sama ada terdapat padanan dengan indeks.

Saya akan mulakan dengan pernyataan if dan menggunakan List.Contains untuk mencari kedudukan tertentu dalam BufferList dan merujuk pertanyaan HeaderID .

Kami ingin mencarinya merentasi keseluruhan panjang fail, dan kemudian mengembalikan kedudukan item dalam senarai. Jika ia sepadan dengan indeks, kami mempunyai padanan untuk baris khusus itu.

Kemudian saya ingin mengembalikan nilai untuk mengenal pasti pengepala. Dalam kes ini, saya hanya memulangkan H. Saya akan menyalin sintaks supaya saya tidak perlu menulisnya sekali lagi.

Kami juga perlu mengenal pasti bahagian baris. Jika senarai tidak mengandungi HeaderID , tetapi DetailID , maka kami berada di bahagian baris.

Jika Column1 ialah rentetan teks kosong, maka saya mahu ia kekal kosong . Jika itu tidak berlaku, maka saya mahu ia menjadi batal .

Pembangkang ini mendapat baris pengepala dan mengembalikan H, dan kemudian ia menemui baris terperinci dan mengembalikan R. Kemudian ia mengembalikan 0s untuk semua item yang dikongsi dalam bahagian baris itu.

Kosong atau nol ini penting kerana ia membolehkan anda mengisi. Isian tidak akan bergerak merentasi sel kosong tersebut supaya kami boleh menghapuskannya kemudian.

Kami akan melakukan ini dalam bar formula dan menggunakan Table.FillDown sekali lagi. Ia mahukan senarai dengan nama lajur, iaitu lajur Temp kami .

Sekarang kita mempunyai nilai H dan R diulang di seluruh lajur ini, yang bermaksud kita sebenarnya boleh memisahkan pengepala daripada bahagian terperinci.

Anda juga boleh mengisinya dari antara muka pengguna jika anda tidak mahu menulis kod tersebut. Anda hanya boleh klik kanan dan pilih Isi , kemudian Bawah .

Mengalih Keluar Nol Dan Kosong Dalam Microsoft Power Query

Sekarang kita mempunyai hak ini, kita boleh menghapuskan perkara yang kita tidak perlukan. Semua yang batal atau mengandungi kosong ialah baris yang kita tidak perlukan dan perlu dialih keluar. Kita boleh menghapuskannya dengan menapis.

Membahagi Bahagian

Sebaik sahaja kami mengalih keluar kosong dan nol itu, kami tinggal dengan semua yang kami perlukan. Pada ketika ini, kita hanya boleh membahagi bahagian. Kita boleh menumpukan pada baris pengepala dan memilihnya kerana ia mempunyai jarak yang berasingan daripada semua baris butiran (yang mempunyai jarak yang berasingan juga).

Saya akan menambah langkah baharu dalam bar formula yang membolehkan saya membuat penapis lain pada lajur yang sama. Dalam kes ini, saya hanya akan menyimpan semua bahagian pengepala.

Sekarang, saya mempunyai semua baris pengepala itu di sini.

Saya boleh memilih Column1 , pergi ke bar formula, pilih Split column , kemudian split mengikut kedudukan.

Kemudian dapatkan pertanyaan kuasa sendiri memikirkan yang ini. Ia akan mencadangkan beberapa jawatan. Klik OK untuk menerima jawatan tersebut.

Satu-satunya perkara yang kami minati daripada pengepala ialah item dan nombor kerja .

Di dalam bar formula di sini, saya boleh menamakan semula mereka dengan Item dan Job # . Ini akan menyelamatkan saya daripada langkah lajur nama semula yang lain.

Selepas langkah ini, apa yang perlu saya lakukan ialah memilih Item , pilih Job # , dan sudah tentu, pilih kekunci pengepala kami . Kemudian saya akan mengalih keluar semua lajur lain kerana saya tidak lagi memerlukannya.

Ini akan menjadi hasilnya. Kami masih perlu membersihkan nilai dan mengalih keluar item teks dan sempang. Apa yang kita mahukan ialah nilai-nilai di antaranya.

Jadi kami membukanya dan menyahpilih sempang dan item.

Sekarang, semua tajuk telah selesai.

Kita perlu melakukan proses yang sama untuk DetailID juga. Saya perlu menamakan semula langkah tersebut supaya lebih mudah bagi saya untuk kembali kepadanya sedikit kemudian.

Kami akan kembali ke pertanyaan awal yang kami mulakan. Kami bermula dengan Baris Ditapis dalam anak tetingkap Applied Steps.

Saya akan menyalin ini dan menambahkannya pada penapis saya. Kali ini, saya tidak memilih H tetapi saya memilih R.

Kemudian saya akan memilih Lajur1, pergi ke lajur Split, bahagikan mengikut kedudukan, kemudian minta kuasa pertanyaan memikirkannya.

Inilah yang dicadangkan oleh pertanyaan kuasa. Mari cuba ini.

Ini sebenarnya kelihatan agak bagus. Malah jumlah baris terbahagi dengan sempurna. Sudah tentu, terdapat banyak ruang kerana kami mempunyai lekukan itu.

Memangkas Rentetan Teks Dalam Microsoft Power Query

Saya akan memilih lajur pertama itu, kemudian tekan Bawah + Shift untuk memilih sehingga Lajur 1.10. Pergi ke Transform , pilih Format , kemudian Trim . Pemangkasan hanya akan mengeluarkan lebihan ruang di hadapan atau di hujung rentetan, bukan di antaranya.

Seterusnya, kita hanya boleh mempromosikan pengepala, jadi saya tidak perlu menaip semua pengepala atau tajuk untuk lajur ini. Dalam langkah berpecah, saya menamakan semula dua lajur. Sekarang, sudah tentu, dengan 10 lajur, itu agak mengganggu.

Kita juga perlu membuang nilai-nilai yang berlebihan itu. Oleh kerana kita mempunyai jumlah, saya perlu menggunakan salah satu daripada tiga lajur terakhir ini kerana ia adalah satu-satunya baris yang mempunyai nilai tambahan di antaranya. Kemudian kami akan menyahpilih tempat kosong, sempang dan teks tersebut.

Kemudian saya akan mengalih keluar lajur yang tidak diperlukan supaya yang tinggal hanyalah jadual dengan hanya pengepala dan butiran sahaja. Kami memerlukan kunci untuk menyatukan bahagian tersebut semula.

Untuk itu, kita boleh menggunakan cantuman diri supaya kita boleh menggabungkan jadual dengan dirinya sendiri untuk membawa maklumat itu kembali bersama. Pada tab Laman Utama , pilih Gabung , kemudian pilih Lajur 5 dan pertanyaan yang sama.

Daripada AllDetails, saya mahukan AllHeaders sebagai jadual awal saya yang ingin saya gabungkan.

Ini telah membawa balik semua maklumat daripada jadual pengepala dengan satu baris untuk setiap item dan setiap nombor kerja.

Kami menggunakan kunci untuk bergabung dengan baris terperinci. Jika saya menekan ke tepi dalam ruang putih di sini, kita akan melihat pratonton semua baris yang dimiliki oleh Pengepala 5.

Kami akan mengalih keluar lajur akhir di sini dan kemudian kami selesai membetulkan lebar lajur tetap campuran dalam pertanyaan kuasa Microsoft.

Kesimpulan

Dalam tutorial ini, kami menghasilkan cara untuk menyelesaikan isu lebar lajur tetap bercampur menggunakan Microsoft Power Query. Jika anda menikmati kandungan yang diliputi dalam tutorial khusus ini, sila jangan lupa untuk melanggan saluran TV LuckyTemplates.

Kami mempunyai sejumlah besar kandungan yang keluar sepanjang masa daripada saya sendiri dan pelbagai pencipta kandungan, yang kesemuanya berdedikasi untuk menambah baik cara anda menggunakan LuckyTemplates dan Power Platform.

Melissa