TEXTJOIN di Power Query - Tips Excel

CONCATENATEX di Power Query. Fungsi TEXTJOIN baru itu luar biasa. Bisakah Anda melakukan hal yang sama dengan Power Query? Iya. Sekarang kamu bisa.

Menonton video

• Penampil mengunduh data dari sistem di mana setiap item dipisahkan oleh Alt + Enter
• Bill: Mengapa Anda melakukan ini? Penampil: Ini adalah cara saya mewarisi data. Saya ingin tetap seperti itu.
• Tagihan: Apa yang ingin Anda lakukan dengan 40% nilai yang tidak ada dalam tabel? Penonton: Tidak ada jawaban
• Tagihan: Ada cara yang rumit untuk menyelesaikan ini jika Anda memiliki alat Power Query terbaru.
• Sebaliknya, Makro VBA untuk mengatasinya - makro harus berfungsi sepenuhnya kembali ke Excel 2007
• Alih-alih melakukan VLOOKUP, lakukan serangkaian Temukan & Ganti dengan VBA

Transkrip Video

Pelajari Excel dari, Podcast Episode 2151.

Aku benar-benar tidak tahu harus menyebutnya apa. Jika saya mencoba menarik orang yang menggunakan DAX, saya akan mengatakan ConcatenateX di Power Query, atau hanya orang yang menggunakan Excel biasa tapi Office 365, saya akan mengatakan TEXTJOIN di Power Query, atau, sejujurnya, itu serangkaian langkah yang sangat kompleks di Power Query untuk mengaktifkan solusi yang sangat gila di Excel.

Hei. Selamat datang kembali di netcast. Saya Bill Jelen. Nah, kemarin di episode 2150, saya jelaskan masalahnya. Seseorang mengirimkan file ini di mana sistem mereka mengunduh item yang dipesan dengan umpan baris di antara mereka. Dengan kata lain, ALT + ENTER, dan lihat, WRAP TEXT diaktifkan, dan mereka ingin melakukan VLOOKUP ke dalam LOOKUPTABLE ini untuk masing-masing item tersebut. Saya seperti, apa? Mengapa kau melakukan ini? Tapi saya membahasnya kemarin. Mari kita coba dan cari tahu bagaimana melakukan ini.

Sebenarnya saya mengatakan, Power Query akan menjadi cara terbaik untuk melakukan ini, tetapi saya bingung tentang cara melakukan bagian terakhir. Saya berkata, apakah boleh jika setiap item berakhir di barisnya sendiri? Tidak, mereka harus kembali ke urutan aslinya. Saya seperti, itu mengerikan, tapi, di feed Twitter saya minggu lalu, Tim Rodman, 27 September: "Akhirnya membaca buku ini," - Saya menduga itu PowerPivot Alchemy - "dan sudah mendapatkan keinginan ConcatenateX-nya. ” Saya menjadi orang yang cerdas ketika melakukan ini, meminta PERHAPS ROMANX, tetapi saya mungkin benar-benar menginginkan ConcatenateX, jadi Tim memberi tahu saya bahwa sekarang saya dapat melakukannya di Power BI.

Jadi, saya menemui teman-teman saya, Rob Collie di Power Pivot Pro dan Miguel Escobar, dan, Anda tahu, mereka berdua adalah penulis buku-buku hebat. Saya memiliki kedua buku ini, tetapi fitur ini terlalu baru, tidak di kedua buku. Saya berkata, hei, apakah kalian tahu bagaimana melakukan ini? Dan Miguel memenangkan hadiah tersebut karena Miguel bangun pagi-pagi sekali atau larut malam - saya tidak yakin yang mana - dan mengirimkan kodenya.

Baiklah, jadi, inilah rencana di Power Query dan yang ini sangat rumit. Saya tidak pernah menulis rencana di Power Query. Aku hanya pergi melakukan semuanya. Saya akan mulai dengan data asli, tambahkan kolom INDEX sehingga kita dapat menyimpan item dari pesanan bersama-sama, SPLIT COLUMN to ROWS menggunakan LINEFEED. Ini adalah kedua atau ketiga kalinya di podcast saya menggunakan fitur baru ini. Betapa kerennya itu. Saya memiliki kolom INDEX kedua sehingga kami dapat mengurutkan item ke dalam urutan aslinya, lalu SAVE AS A CONNECTION.

Kemudian, kita akan masuk ke tabel LOOKUP, menjadikannya tabel, kueri dari tabel, SIMPAN SEBAGAI KONEKSI - itu akan menjadi bagian termudah di sana - lalu gabungkan kueri ini dan kueri ini berdasarkan item nomor, semua item dari tabel kiri, ini adalah tabel kiri, cocok dari kanan, ganti null dengan nomor item. Kami masih belum tahu apa yang ingin kami lakukan ketika sesuatu tidak ditemukan karena suatu alasan. Saya telah menanyakan pertanyaan ini, tetapi orang yang mengirim file tidak menjawab, jadi saya akan menggantinya dengan nomor item. Mudah-mudahan hal yang benar untuk dilakukan adalah menambahkan lebih banyak item ke LOOKUPTABLE sehingga tidak ada yang tidak ditemukan, tapi di sinilah kita, dan kemudian kita akan mengurutkan menurut INDEX1 dan INDEX2, jadi dengan begitu,semuanya kembali ke urutan yang benar dan kemudian ini adalah bagian yang saya tidak tahu bagaimana melakukannya.

Kami akan mengelompokkan menurut INDEX1 melakukan hal yang sama dengan TEXTJOIN atau ConcatenateX dengan karakter 10 sebagai pemisah, sebagai agregator, dan, tentu saja, ini adalah bagian yang sulit tetapi bagian yang benar-benar baru di sini serangkaian langkah ini. Jadi, jika Anda memahami apa yang dilakukan TEXTJOIN atau dapat membuat konsep tentang apa yang akan dilakukan ConcatenateX, pada dasarnya kami melakukannya menggunakan langkah semacam ini. Jadi, baiklah. Jadi, mari kita coba.

Jadi, kita akan mulai dari sini. Berikut data asli kami, memiliki judul. Jadi, saya akan FORMAT SEBAGAI TABEL, CONTROL + T, TABEL SAYA MEMILIKI KEPALA, ya, lalu kita akan menggunakan Power Query. Sekarang, saya menggunakan Excel 2016 Office 365, jadi ada di sini, di bagian kiri tab DATA. Jika Anda hanya menggunakan Excel 2016 langsung, bukan Office 365, itu di tengah - DAPATKAN & TRANSFORMASI. Jika Anda menggunakan Excel 2010 atau 2013, itu akan menjadi tabnya sendiri di sini yang disebut Power Query, dan jika Anda tidak memiliki tab itu, Anda harus mengunduh tab itu. Jika Anda menggunakan Mac atau Android atau salah satu versi Excel palsu lainnya, maaf, tidak ada Power Query untuk Anda. Dapatkan versi Windows dari Excel dan cobalah ini.

Baiklah, jadi, kita akan melakukan Power Query DARI TABEL, baiklah, dan hal pertama yang akan saya lakukan adalah saya akan MENAMBAHKAN KOLOM INDEKS dan saya akan mulai DARI 1. Baiklah Jadi, ini pada dasarnya adalah urutan 1, urutan 2, urutan 3, urutan 4. Kemudian kita akan memilih kolom ini dan, pada tab TRANSFORM, kita akan ke SPLIT COLUMN, BY DELIMITER, dan mereka bisa mendeteksi bahwa LineFeed adalah pembatas. Saya suka Power Query yang mendeteksi ini. Sekarang, mengapa Excel, teks ke kolom, ya, teks ke kolom tidak mengetahui apa pembatasnya? Dan setiap kejadian kita akan SPLIT KE BARIS, dan MENGGUNAKAN KARAKTER KHUSUS. Baiklah, jadi semuanya bagus.

Sekarang perhatikan apa yang terjadi di sini. Kami memiliki 999 baris tetapi sekarang kami memiliki lebih dari itu. Jadi, setiap item di nomor pesanan itu sekarang menjadi barisnya sendiri. Sekarang, orang yang menanyakan pertanyaan ini tidak ingin itu menjadi barisnya sendiri tetapi kita harus membuatnya menjadi barisnya sendiri sehingga kita dapat melakukan penggabungan. Saya akan menambahkan kolom INDEX baru di sini. TAMBAHKAN KOLOM, KOLOM INDEKS, DARI 1, dan jadi kami memiliki… ini pada dasarnya adalah nomor pesanan dan kemudian ini adalah urutan dalam urutan karena saya telah menentukan bahwa, nanti, ini akan berada di urutan lain. Saya tidak tahu mereka beralih ke urutan mana, tetapi di sinilah kita.

Baiklah, jadi, HOME, bukan tombol CLOSE & LOAD tapi drop-down CLOSE & LOAD, dan CLOSE & LOAD TO. Saya tidak tahu mengapa butuh 10 detik bagi mereka untuk menampilkan kotak dialog ini untuk pertama kalinya. Kami HANYA akan MENCIPTAKAN KONEKSI. Klik OK. Cantik. Jadi itulah TABLE1, TABLE1.

Sekarang, kita akan pergi ke LOOKUPTABLE kami. LOOKUPTABLE akan mudah diproses. Kami akan memformat ini sebagai tabel. CONTROL + T. Klik OK. DATA, atau POWER QUERY jika Anda menggunakan versi lama, DARI TABEL. Ini akan disebut TABLE2. Sebut saja LOOKUPTABLE. Sempurna. TUTUP & BEBAN, TUTUP & BEBAN KE, HANYA BUAT KONEKSI.

Baik. Sekarang, kita memiliki dua bit di sini dan saya ingin menggabungkan keduanya. Jadi, kami hanya akan pergi ke tempat baru dan kemudian DATA, DAPATKAN DATA, GABUNGKAN KUERI, kami akan melakukan GABUNGAN, dan tabel di sebelah kiri akan menjadi TABLE1 - itulah data asli kami - - dan kami akan menggunakan nomor ITEM ini dan kami akan mengawinkannya dengan nomor LOOKUPTABLE dan ITEM itu. Ini benar-benar non-intuitif di sana Anda harus mengklik ITEM dalam kedua kasus untuk menentukan apa kuncinya, dan OUTER join, SEMUA DARI PERTAMA, PENCOCOKAN DARI KEDUA, dan, lihat, ada 40% dari ini yang hilang dari TERLIHAT. Ini semua adalah data palsu tetapi data asli telah 40% hilang dari LOOKUPTABLE juga. Benar-benar membuat frustrasi. Baik. Jadi, inilah nomor ITEM kami, 2 bidang INDEX kami, dan kemudian LOOKUPTABLE kami di sini. SAYA'm akan BUKA itu dan minta DESKRIPSI. Baiklah, Anda lihat kami memiliki banyak null di sini.

Alright, so, we're going to do a conditional column. Conditional column’s going to say look at this column. If it's = to null, then bring this value over, otherwise, use the value that's in that column. So here, under ADD COLUMN, we’ll do CONDITIONAL COLUMN -- nice little UI that'll walk us through this -- if the LOOKUPTABLEDESCRIPTION EQUALS NULL, then we want to use a COLUMN here of ITEMS, otherwise, we want to use the COLUMN called LOOKUPDESCRIPTION, alright. Click OK, and there we are. There's our CUSTOM column with either the new value from the LOOKUPTABLE or the original value if it's not found. At this point, we can right click and say that we want to REMOVE this column. It was a temporary column, it was a helper column. Now that we have what we need, we don't need that column anymore, and actually, at this point, I don't need this column anymore either. So, I can right click and REMOVE that column. Alright. Now, we have our data here. I want to sort it by the original INDEX. So, SORT ASCENDING. That gets our data into the right sequence, and now that it's sorted, I can actually right click and REMOVE that column.

Alright. Now we’re at the point where, for every item, each order number -- so, this is order number 1, let's say -- I want to have these 4 items separated by a LineFeed character. Now, what I was hoping to be able to do was to come here to TRASNFORM. Instead, if we wanted to GROUP BY and that there'd be some magic here in the GROUP BY, I would say I'm going to concatenate or textjoin all those things, but it doesn't work, alright?

So, here's the set of steps that are new to me that allow this to happen. First thing we want to do is we're going to create a brand new column. That column is just going to be called a TABLECOLUMN and we're going to take ALL ROWS and click OK. Okay. So, when we look inside at this table, we see that we have 2 columns -- one called INDEX and one called CUSTOM -- and we have to remember that name there, alright, and this table unfortunately does not work with STRUCTURED COLUMN. See, EXTRACT VALUES is grayed out. So, this doesn't work with a table. It has to work with a list. I have to convert this table to a list, and this is the part I couldn't figure out and the part that Miguel filled in for me.

So, I'm going to create a CUSTOM COLUMN here and I'm going to call it a LISTCOLUMN and we're going to use a function called TABLE.COLUMN and the table is the thing called TABLECOLUMN, and then which column in there is the thing called CUSTOM. Click OK. Alright, and now these are, instead of a table, it is a list. We're home-free now. TRANSFORM, STRUCTURED COLUMN. I'm going to EXTRACT VALUES. I'm going to create a CUSTOM delimiter USING SPECIAL CHARACTERS, INSERT SPECIAL CHARACTER, LINE FEED, and click OK, and it gives me what I'm looking for. So here’s my original order number. The TABLE, we don’t need anymore, right click and REMOVE that, and we now have our original data using the LOOKUPTABLE where we need it, alright? So, I can right-click and REMOVE this, alright, and then finally, HOME, just straight CLOSE & LOAD, which brings it back into a table in Excel. (=Table.Column((TableColumn),“Custom”))

Alright, but it doesn't look like it worked, does it? That's because, by default, this table does not have WRAP TEXT turned on. So, HOME, WRAP TEXT, and we now have our new data doing the equivalent of a VLOOKUP for each item in the list, and when an item is not found, the original item number is still there, so someone can go piece that back together.

Now, the beautiful thing with Power Query is that while it took us some time to get this set up the first time, the next time we download this list, we just copy it here, and we can even edit something. So, let’s change one. So, MANGO, 4954, we’ll take that 7036 and change it to 4954. Alright, so, now the underlying data has changed, all we have to do is come here to this and click the REFRESH which will refresh all of these items, and we get here to SHEET11 and that second item has changed to a MANGO, alright? It’ll take you some time to set this up once but, once you get it set up, it's just a simple matter of refreshing the data and Power Query will go through all those steps.

Sekarang, hei, ini adalah titik di mana saya biasanya meminta Anda untuk membeli buku saya tetapi, hari ini, mari kita meminta Anda untuk membeli buku Miguel. Miguel Escobar dan Ken Puls menulis buku yang sangat bagus ini tentang M Is For (DATA) MONKEY - buku terbaik yang ada di Power Query. Coba lihat itu.

Baiklah, tutup: hari ini adalah episode yang sangat panjang; kami memiliki penampil, mengunduh data dari sistem di mana setiap item dipisahkan oleh ALT + ENTER dan kami mencoba melakukan VLOOKUP untuk masing-masing item; membuat solusi hari ini menggunakan Power Query termasuk alat ekstrak kolom terstruktur sebagai; tetapi itu hanya berfungsi pada daftar, bukan tabel, jadi saya harus menggunakan fungsi TABLE.COLUMN untuk mengonversi tabel menjadi daftar.

Baiklah. Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.

Unduh berkas

Unduh file contoh di sini: Podcast2151.xlsm