Power Query'de CONCATENATEX. Yeni METİNBİRLEŞTİR işlevi harika. Power Query ile aynı şeyi yapabilir misiniz? Evet. Şimdi yapabilirsin.
Video izle
- Bir izleyici, her öğenin Alt + Enter ile ayrıldığı bir sistemden veri indirir
- Bill: Bunu neden yapıyorsun? Görüntüleyici: Verileri böyle miras alırım. Bu şekilde kalmasını istiyorum.
- Fatura: Tabloda olmayan değerlerin% 40'ı ile ne yapmak istiyorsunuz? Görüntüleyen: Cevap yok
- Bill: En son Power Query araçlarına sahipseniz, bunu çözmenin karmaşık bir yolu vardır.
- Bunun yerine, çözmek için bir VBA Makrosu - makro Excel 2007'ye kadar çalışmalıdır.
- DÜŞEYARA yapmak yerine, VBA ile bir dizi Bul ve Değiştir yapın
Video Metni
Podcast Bölüm 2151'den Excel öğrenin.
Buna ne isim vereceğimi gerçekten bilmiyorum. DAX kullanan kişileri çekmeye çalışıyorsam, Power Query'de ConcatenateX veya yalnızca Office 365 dışında normal Excel kullanan kişileri, Power Query'de METİNBİRLEŞTİR derim veya tamamen dürüst olmak gerekirse Excel'de süper çılgın bir çözümü etkinleştirmek için Power Query'de süper karmaşık adımlar kümesi.
Hey. İnternet yayınına tekrar hoş geldiniz. Ben Bill Jelen. Dün 2150. bölümde sorunu anlattım. Birisi, aralarında satır beslemeli bir sipariş olan öğeleri kendi sistemlerinin indirdiği bu dosyaya gönderdi. Başka bir deyişle, ALT + ENTER ve bakın, WRAP TEXT açık ve bu öğelerin her biri için bu LOOKUPTABLE'a bir DÜŞEYARA yapmak istiyorlar. Ben ne gibiyim? Bunu neden yapıyorsun? Ama bunu dün ele aldım. Bunu nasıl yapacağımızı bulmaya çalışalım.
Aslında Power Query'nin bunu yapmanın en iyi yolu olacağını söyledim ama son kısmı nasıl yapacağım konusunda şaşkına döndüm. Her bir öğe kendi satırında biterse sorun olur mu dedim? Hayır, bu orijinal sıraya geri dönmeleri gerekiyor. Sanırım, bu korkunç, ama geçen hafta, Tim Rodman, 27 Eylül'de Twitter beslememde: "Sonunda bu kitabı okuyorum," - sanırım PowerPivot Simyası - "ve onun ConcatenateX dileğini çoktan aldı. " Bunu yaptığım zaman akıllı davranıyordum, PERHAPS ROMANX istedim, ama muhtemelen ConcatenateX'i istedim ve bu yüzden Tim bana bunu artık Power BI'da yapabileceğime dair bir uyarı verdi.
Ben de arkadaşlarım, Power Pivot Pro'dan Rob Collie ve Miguel Escobar'la çıktım ve biliyorsunuz, ikisi de harika kitapların yazarı. Bu kitapların ikisine de sahibim ama bu özellik çok yeni, her iki kitapta da değil. Hey, bunu nasıl yapacağınızı biliyor musunuz dedim? Miguel ödülü kazandı çünkü Miguel bu sabah erken saatlerde veya dün gece geç saatlerde kalktı - hangisi olduğundan emin değilim - ve kodu gönderdi.
Pekala, işte Power Query'deki plan ve bu çok karmaşık. Power Query'de asla bir plan yazmam. Ben sadece her şeyi yapıyorum. Orijinal verilerle başlayacağım, bir INDEX sütunu ekleyeceğim, böylece bir siparişteki öğeleri bir arada tutabiliriz, bir LINEFEED kullanarak SÜTUNU SATIRLARA AYIRIN. Bu, podcast'te bu yeni özelliği kullandım ikinci veya üçüncü kez. Ne kadar serin. İkinci bir INDEX sütunum vardı, böylece öğeleri orijinal sıraya göre sıralayabilir ve ardından BAĞLANTI OLARAK KAYDEDEBİLİRİZ.
Ardından, ARA tablosuna geleceğiz, bir tablo yapacağız, tablodan sorgu, BAĞLANTI OLARAK KAYDET - buradaki en kolay kısım olacaktı - ve sonra bu sorguyu ve bu sorguyu öğeye göre birleştireceğiz. sayı, soldaki tablodaki tüm öğeler, bu soldaki tablodur, sağdan eşleşen, boşları öğe numarasıyla değiştirin. Herhangi bir nedenle bir şey bulunamadığında, yapmak istediğimiz şey konusunda hala yayındayız. Bu soruyu sordum, ancak dosyayı gönderen kişi cevap vermiyor, bu yüzden onu madde numarasıyla değiştireceğim. Umarım yapılacak doğru şey, LOOKUPTABLE'a daha fazla öğe eklemektir, böylece hiç bulunmayan yoktur, ancak buradayız ve sonra INDEX1 ve INDEX2'ye göre sıralayacağız, böylece bu şekilde,işler doğru sıraya geri döndü ve sonra bu, nasıl yapacağımı çözemediğim kısımdı.
INDEX1'e göre gruplandıracağız, TEXTJOIN veya ConcatenateX'in eşdeğerini ayırıcı olarak 10 karakteriyle, toplayıcı olarak yapacağız ve elbette, işin zor kısmı bu ama burada gerçekten yeni olan kısım bu bu adımlar kümesi. Dolayısıyla, TEXTJOIN'in ne yaptığını anlarsanız veya ConcatenateX'in ne yapacağını kavramsallaştırırsanız, esasen bunu bu tür bir adımı kullanarak yapıyoruz. Peki, tamam. Öyleyse bir deneyelim.
O halde buradan başlayacağız. İşte orijinal verilerimiz, bir başlığı var. Öyleyse, TABLO OLARAK BİÇİMLENDİRECEĞİM, CONTROL + T, TABLAMIN BAŞLIKLARI VAR, evet, ve sonra Power Query kullanacağız. Şimdi, Excel 2016 Office 365'teyim, bu yüzden burada, VERİ sekmesinin sol tarafında. Yalnızca doğrudan Excel 2016'daysanız, Office 365'te değil, ortada - AL ve DÖNÜŞTÜR. Excel 2010 veya 2013'teyseniz, burada Power Query adında kendi sekmesi olacak ve bu sekmeye sahip değilseniz, o sekmeyi indirmeniz gerekecek. Mac veya Android veya Excel'in diğer sahte sürümlerinden herhangi birini kullanıyorsanız, üzgünüz, sizin için Power Query yok. Excel'in Windows sürümünü edinin ve bunu deneyin.
Pekala, öyleyse, TABLEDEN bir Power Query yapacağız, pekala ve yapacağım ilk şey bir INDEX SÜTUNU EKLEYECEĞİM ve 1'DEN başlayacağım. Tamam , yani, bu esasen 1. sıra, 2. sıra, 3. sıra, 4. sıra. Sonra bu sütunu seçeceğiz ve DÖNÜŞÜM sekmesinde DELIMITER TARAFINDAN SPLIT COLUMN'a gidiyoruz ve onlar bunu yapabildiler. bunun bir LineFeed olduğunu algılamak sınırlayıcıdır. Power Query'nin bunu algılamasını seviyorum. Şimdi, neden Excel, metinden sütunlara, evet, metinden sütunlara sınırlayıcının ne olduğunu anlamıyor? Ve her seferinde SATIRLARA AYIRACAĞIZ ve ÖZEL KARAKTER KULLANACAĞIZ. Pekala, hepsi iyi.
Şimdi burada ne olduğunu izleyin. 999 satırımız var ama şimdi bundan çok daha fazlasına sahibiz. Dolayısıyla, bu sipariş numarasındaki her öğe artık kendi satırıdır. Şimdi, bu soruyu soran kişi onun kendi satırı olmasını istemiyor ama biz bunu kendi satırı yapmalıyız ki birleştirmeyi yapabilelim. Buraya yeni bir INDEX sütunu ekleyeceğim. SÜTUNU, 1'DEN DİZİN SÜTUNU EKLE, ve elimizde… bunlar esasen sıra numaraları ve sonra bunlar sıra içindeki sıralar çünkü daha sonra bunların başka bir sırada olacağını belirledim. Hangi sıraya geçeceklerini bilmiyorum ama işte buradayız.
Pekala, ANA SAYFA, KAPAT VE YÜKLE düğmesi değil, KAPAT ve YÜKLE açılır menüsü ve KAPAT VE YÜKLE. Bu iletişim kutusunu ilk kez görüntülemelerinin neden 10 saniye sürdüğünü bilmiyorum. SADECE BAĞLANTI OLUŞTURACAĞIZ. Tamam'ı tıklayın. Güzel. İşte bu TABLE1, TABLE1.
Şimdi, LOOKUPTABLE'ımıza gidiyoruz. LOOKUPTABLE'ın işlenmesi kolay olacak. Bunu bir tablo olarak biçimlendireceğiz. CONTROL + T. Tamam'ı tıklayın. DATA veya eski bir sürümdeyseniz GÜÇ SORGUSU, TABLO'DAN. Bu, TABLE2 olarak adlandırılacaktır. Buna LOOKUPTABLE diyelim. Mükemmel. KAPATIN VE YÜKLEYİN, KAPATIN VE YÜKLEYİN, YALNIZCA BAĞLANTI OLUŞTURUN.
Peki. Şimdi, burada iki parçamız var ve bu ikisini birleştirmek istiyorum. Öyleyse, yeni bir noktaya gideceğiz ve ardından VERİ, VERİ AL, SORULARI BİRLEŞTİR, bir BİRLEŞTİRME yapacağız ve soldaki tablo TABLE1 olacak - bu bizim orijinal verilerimiz - - ve bu ÖĞE numarasını kullanacağız ve bunu LOOKUPTABLE ve bu ITEM numarası ile birleştireceğiz. Burada gerçekten sezgisel değil, her iki durumda da anahtarın ne olduğunu tanımlamak için ÖĞELER'e tıklamanız gerekiyor ve BİRİNCİ HEPi BİRİNCİNDEN İTİBAREN, İKİNCİDEN EŞLEŞEN bir DIŞ birleştirme ve bakın, bunların% 40'ı BAKILABİLİR. Bunların hepsi sahte verilerdir, ancak orijinal veriler de LOOKUPTABLE'da% 40 eksiktir. Gerçekten sinir bozucu. Peki. Öyleyse, işte ÖĞE numaramız, 2 INDEX alanımız ve sonra burada LOOKUPTABLE'ımız. BEN'm onu GENİŞLETECEK ve AÇIKLAMA sor. Pekala, burada bir sürü boş var.
Pekala, o halde, şartlı bir köşe yazacağız. Koşullu sütun, bu sütuna bak diyecek. Eğer = null ise, o zaman bu değeri üzerine getirin, aksi takdirde o sütundaki değeri kullanın. Yani burada, SÜTUN EKLE altında, KOŞULLU SÜTUN yapacağız - bize bu konuda yol gösterecek güzel küçük kullanıcı arayüzü - Eğer LOOKUPTABLEDESCRIPTION EQUALS NULL ise, burada ÖĞELERDEN bir SÜTUN kullanmak istiyoruz, aksi takdirde kullanmak istiyoruz LOOKUPDESCRIPTION adlı COLUMN, tamam. Tamam'ı tıklayın ve işte buradayız. LOOKUPTABLE'dan yeni değeri veya bulunamazsa orijinal değeri içeren ÖZEL sütunumuz var. Bu noktada sağ tıklayıp bu sütunu KALDIRMAK istediğimizi söyleyebiliriz. Geçici bir sütundu, yardımcı bir sütundu. Artık ihtiyacımız olan şey bizde olduğuna göre, artık o sütuna ihtiyacımız yok ve aslında, bu noktada,Artık bu sütuna da ihtiyacım yok. Yani, sağ tıklayıp bu sütunu KALDIRABİLİRİM. Peki. Şimdi, verilerimiz burada. Orijinal INDEX'e göre sıralamak istiyorum. Öyleyse, SIRALA ÇIKARMA. Bu, verilerimizi doğru sıraya sokar ve artık sıralandığına göre, bu sütunu gerçekten sağ tıklayıp KALDIRABİLİRİM.
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.
Şimdi, hey, bu genellikle senden kitabımı almanı istediğim nokta ama bugün bunun yerine gidip Miguel'in kitabını almanı isteyelim. Miguel Escobar ve Ken Puls, Power Query'deki en iyi kitap olan M Is For (DATA) MONKEY üzerine bu mükemmel kitabı yazdı. Şuna bir bak.
Pekala, özetle: Bugün gerçekten uzun bir bölüm; bir görüntüleyicimiz var, her bir öğenin ALT + ENTER ile ayrıldığı bir sistemden veri indiriyor ve her bir öğe için bir DÜŞEYARA yapmaya çalışıyoruz; bugün Power Query kullanarak ekstraktın yapılandırılmış sütun aracını içeren bir çözüm oluşturdu; ancak bu sadece bir listede çalışıyor, bir tabloda değil, bu yüzden tabloyu bir listeye dönüştürmek için TABLE.COLUMN işlevini kullanmak zorunda kaldım.
Peki, merhaba. Uğradığın için teşekkür ederim. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2151.xlsm
