TEXTJOIN în Power Query - Excel Tips

CONCATENATEX în Power Query. Noua funcție TEXTJOIN este minunată. Puteți face același lucru cu Power Query? Da. Acum poti.

Urmăriți videoclipul

• Un vizualizator descarcă date dintr-un sistem în care fiecare element este separat de Alt + Enter
• Bill: De ce faci asta? Vizualizator: așa moștenesc datele. Vreau să o țin așa.
• Bill: Ce doriți să faceți cu 40% din valori care nu sunt în tabel? Vizualizator: fără răspuns
• Bill: Există o modalitate complicată de a rezolva acest lucru dacă aveți cele mai recente instrumente de interogare Power.
• În schimb, o macro VBA pentru a o rezolva - macro-ul ar trebui să funcționeze până la Excel 2007
• În loc să faceți VLOOKUP, faceți o serie de Căutare și înlocuire cu VBA

Transcriere video

Aflați Excel din, Podcast Episodul 2151.

Chiar nu știu cum să-i spun asta. Dacă încerc să atrag oamenii care folosesc DAX, aș spune ConcatenateX în Power Query sau doar oamenii care folosesc Excel obișnuit, dar Office 365, aș spune TEXTJOIN în Power Query sau, pentru a fi complet sincer, este un set super complex de pași în Power Query pentru a activa o soluție super-nebună în Excel.

Hei. Bine ați venit înapoi pe netcast. Eu sunt Bill Jelen. Ei bine, ieri, în episodul 2150, am descris problema. Cineva a trimis acest fișier de unde sistemul lor descarcă articolele care sunt o comandă cu linii între ele. Cu alte cuvinte, ALT + ENTER, și vezi, WRAP TEXT este activat și vor să facă o VLOOKUP în acest LOOKUPTABLE pentru fiecare dintre aceste elemente. Sunt ca, ce? De ce faci asta? Dar am tratat asta ieri. Să încercăm și să ne dăm seama cum să facem acest lucru.

De fapt, am spus, ei bine, Power Query ar fi cel mai bun mod de a face acest lucru, dar m-am gândit cum să fac ultima parte. Am spus, este în regulă dacă fiecare articol ajunge la rândul său? Nu, trebuie să revină în această secvență originală. Sunt oribil, dar, pe fluxul meu de Twitter de săptămâna trecută, Tim Rodman, 27 septembrie: „În sfârșit, citesc această carte” - cred că este PowerPivot Alchemy - „și deja și-a primit dorința ConcatenateX. ” Când am făcut acest lucru, eram un smartass, cerând PERHAPS ROMANX, dar probabil că mi-am dorit cu adevărat ConcatenateX, așa că Tim mi-a dat un cap în sus că acum pot face asta în Power BI.

Așadar, am ieșit la prietenii mei, Rob Collie la Power Pivot Pro și Miguel Escobar și, știți, amândoi sunt autori de cărți grozave. Am ambele cărți, dar această caracteristică este prea nouă, nu în nici una dintre cărți. Am spus, hei, știți cum să faceți asta? Și Miguel câștigă premiul pentru că Miguel s-a trezit devreme în această dimineață sau seara târziu - nu sunt sigur care - și a trimis codul.

Bine, deci, iată planul din Power Query, iar acesta este atât de complicat. Nu scriu niciodată un plan în Power Query. Mă duc să fac toate lucrurile. Voi începe cu datele originale, adăug o coloană INDEX, astfel încât să putem păstra împreună articolele dintr-o comandă, SPLIT COLUMN la ROWS folosind un LINEFEED. Este a doua sau a treia oară pe podcast când folosesc această nouă funcție. Cat de tare e asta. Am avut o a doua coloană INDEX, astfel încât să putem sorta articolele în secvența originală și apoi să SALVĂM CA O CONEXIUNE.

Apoi, vom ajunge la tabelul CĂUTARE, îl vom transforma într-un tabel, interogăm din tabel, SALVAȚI CA CONEXIUNE - care urma să fie cea mai ușoară parte chiar acolo - și apoi să combinăm această interogare și această interogare pe baza elementului numărul, toate articolele din tabelul din stânga, acesta este tabelul din stânga, care se potrivește din dreapta, înlocuiți valorile nule cu numărul articolului. Suntem încă în aer în legătură cu ceea ce vrem să facem când ceva nu este găsit din anumite motive. Am pus această întrebare, dar persoana care a trimis fișierul nu răspunde, așa că o voi înlocui doar cu numărul articolului. Sperăm că ceea ce trebuie făcut este să adăugați mai multe articole la LOOKUPTABLE, astfel încât să nu existe fonduri, dar iată-ne, și apoi vom ordona după INDEX1 și INDEX2, astfel,lucrurile s-au întors în ordinea potrivită și atunci aceasta a fost partea pe care nu mi-am putut da seama cum să o fac.

Vom grupa după INDEX1 făcând echivalentul unui TEXTJOIN sau ConcatenateX cu caracterul 10 ca separator, ca agregator și, desigur, aceasta este partea care este partea dificilă, dar este partea care este cu adevărat nouă aici în acest set de pași. Deci, dacă înțelegeți ce face TEXTJOIN sau puteți conceptualiza ceea ce ar fi făcut ConcatenateX, în esență facem acest lucru folosind acest tip de pas. Deci, bine. Așadar, să-i dăm o lovitură.

Deci, vom începe de aici. Iată datele noastre originale, are un titlu. Așadar, voi FORMAȚI CA TABEL, CONTROL + T, TABELUL MEU ARE HEADERS, da, și apoi vom folosi Power Query. Acum, sunt în Excel 2016 Office 365, deci este aici, în partea stângă a filei DATA. Dacă sunteți doar în Excel 2016, nu Office 365, este în mijloc - OBȚINEȚI ȘI TRANSFORMAȚI. Dacă sunteți în Excel 2010 sau 2013, va fi propria filă aici numită Power Query și, dacă nu aveți acea filă, va trebui să descărcați acea filă. Dacă sunteți pe un Mac sau Android sau pe oricare dintre celelalte versiuni false de Excel, îmi pare rău, nu aveți nicio interogare Power pentru dvs. Obțineți o versiune Windows de Excel și încercați acest lucru.

Bine, deci, vom face o interogare de putere dintr-un tabel, bine, și primul lucru pe care îl voi face este că voi adăuga o coloană de index și voi începe de la 1. Bine , deci, aceasta este în esență ordinea 1, ordinea 2, ordinea 3, ordinea 4. Apoi vom alege această coloană și, în fila TRANSFORMARE, vom merge la SPLIT COLUMN, BY DELIMITER, și au reușit să detectează că este un LineFeed este delimitatorul. Îmi place că Power Query detectează acest lucru. Acum, de ce nu Excel, textul în coloane, da, textul în coloane nu-și dau seama ce este delimitatorul? Și fiecare apariție o vom împărți în rânduri și vom folosi CARACTER SPECIAL. Bine, deci toate acestea sunt bune.

Acum urmăriți ce se întâmplă aici. Avem 999 de rânduri, dar acum avem mult mai mult decât atât. Deci, fiecare articol din acel număr de comandă este acum propriul rând. Acum, persoana care a pus această întrebare nu dorește ca acesta să fie propriul rând, dar va trebui să îl facem să fie propriul rând, astfel încât să putem face unirea. Voi adăuga aici o nouă coloană INDEX. ADĂUGAȚI CULOANĂ, CULOANĂ DE INDICE, DE LA 1, așa că avem … acestea sunt în esență numerele de ordine și apoi acestea sunt secvența din ordine, deoarece am stabilit că, mai târziu, acestea vor fi într-o altă ordine. Nu știu la ce ordine trec, dar iată-ne.

Bine, deci, ACASĂ, nu butonul ÎNCHIDE și ÎNCĂRCARE, ci meniul derulant ÎNCHIS și ÎNCĂRCARE și ÎNCHIS și ÎNCARCĂ. Nu știu de ce durează 10 secunde să afișeze această casetă de dialog prima dată. Vom CREA DOAR CONEXIUNEA. Faceți clic pe OK. Frumos. Deci acesta este TABLE1, TABLE1.

Acum, vom merge la căutarea noastră. LOOKUPTABLE va fi ușor de procesat. Vom forma acest lucru ca un tabel. CONTROL + T. Faceți clic pe OK. DATA, sau POWER QUERY dacă sunteți într-o versiune veche, FROM TABLE. Aceasta se va numi TABLE2. Să-i spunem CAUTĂ. Perfect. ÎNCHIDE ȘI ÎNCARCĂ, ÎNCHIDE ȘI ÎNCARCĂ, CREAȚI DOAR CONEXIUNEA.

Bine. Acum, avem cei doi biți aici și vreau să-i îmbin pe cei doi. Deci, vom merge doar într-un loc nou și apoi DATA, OBȚINERE DE DATE, COMBINĂ ÎNTREBĂRI, vom face o FUZIE, iar tabelul din stânga va fi TABELUL 1 - acestea sunt datele noastre originale - - și vom folosi acest număr ARTICOL și o vom căsători până la CĂUTAREA și acel număr ARTICOL. Este într-adevăr non-intuitiv acolo, trebuie să faceți clic pe ITEMS în ambele cazuri pentru a defini care este cheia, și o înscriere EXTERIOARĂ, TOT DE LA PRIM, MATCHING DIN SECOND, și, vezi, există 40% din acestea care lipsesc din CAUTABIL. Acestea sunt toate date false, dar datele originale lipseau de 40% și din LOOKUPTABLE. Foarte frustrant. Bine. Deci, iată numărul articolului nostru, cele 2 câmpuri INDEX și apoi căutarea noastră aici. Eu 'Mă voi extinde și voi cere DESCRIEREA. În regulă, vezi că avem o grămadă de nuluri aici.

Bine, deci, vom face o coloană condiționată. Coloana condiționată va spune că uitați-vă la această coloană. Dacă este = to null, atunci aduceți această valoare, în caz contrar, utilizați valoarea din coloana respectivă. Deci, aici, sub ADAUGĂ CULONĂ, vom face COLOANĂ CONDIȚIONALĂ - o interfață UI frumoasă care ne va conduce prin asta - dacă DESCRIEREA LOOKUPTABLEDE EGALS NULL, atunci vrem să folosim o COLOANĂ aici de ARTICOLE, altfel, vrem să folosim coloana numită LOOKUPDESCRIPTION, bine. Faceți clic pe OK și iată-ne. Există coloana noastră PERSONALIZATĂ, fie cu noua valoare din LOOKUPTABLE, fie cu valoarea originală, dacă nu este găsită. În acest moment, putem face clic dreapta și putem spune că vrem să ÎNLĂTURĂM această coloană. Era o coloană temporară, era o coloană de ajutor. Acum că avem ceea ce avem nevoie, nu mai avem nevoie de acea coloană și, de fapt, în acest moment,Nici nu mai am nevoie de această coloană. Așadar, pot face clic dreapta și ÎNLIMINARE acea coloană. Bine. Acum, avem datele noastre aici. Vreau să-l sortez după INDEX original. Deci, SORT ASCENDENT. Asta face ca datele noastre să fie în secvența corectă, iar acum, când sunt sortate, pot face clic dreapta și ÎNLIMINAREA coloanei respective.

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.

Acum, hei, acesta este punctul în care de obicei îți cer să mergi să îmi cumperi cartea, dar, astăzi, să te rog să mergi să cumperi cartea lui Miguel. Miguel Escobar și Ken Puls au scris această carte excelentă despre M Is For (DATA) MONKEY - cea mai bună carte din Power Query. Du-te și verifică asta.

Bine, încheie: astăzi este un episod foarte lung; avem un vizualizator, descarcă date dintr-un sistem în care fiecare element este separat de ALT + ENTER și încercăm să facem un VLOOKUP pentru fiecare articol individual; a construit astăzi o soluție folosind Power Query, incluzând instrumentul de coloane structurate de extract as; dar asta funcționează doar pe o listă, nu pe un tabel, așa că a trebuit să folosesc funcția TABLE.COLUMN pentru a converti tabelul într-o listă.

Ei bine, hei. Vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.

Descărcare fișier

Descărcați exemplarul de fișier aici: Podcast2151.xlsm