CONCATENATEX Power Query'is. Uus funktsioon TEXTJOIN on vinge. Kas saate Power Queryga sama teha? Jah. Nüüd sa saad.
Vaata videot
- Vaataja laadib andmed alla süsteemist, kus iga üksus on eraldatud klahvidega Alt + Enter
- Bill: Miks sa seda teed? Vaataja: nii pärin andmed. Ma tahan seda nii hoida.
- Bill: Mida soovite teha 40% väärtustest, mida tabelis pole? Vaataja: vastust pole
- Bill: Selle lahendamiseks on keeruline viis, kui teil on uusimad Power Query tööriistad.
- Selle asemel VBA makro selle lahendamiseks - makro peaks töötama kogu aeg tagasi Excel 2007-ni
- Selle asemel, et teha VLOOKUP, tehke seeria Leia ja asenda VBA-ga
Video ärakiri
Õppige Exceli kohta, Podcast Episode 2151.
Ma tõesti ei tea, kuidas seda nimetada. Kui ma üritan meelitada DAX-i kasutavaid inimesi, siis ma ütleksin Power Query's ConcatenateX või lihtsalt tavalist Exceli, kuid Office 365 kasutavaid inimesi, ma ütleksin Power Query's TEXTJOIN või kui aus olla, siis see on ülikeeruline sammude komplekt Power Query's, et Excelis ülihull lahendus lahendada.
Hei. Tere tulemast tagasi netisaate juurde. Olen Bill Jelen. Noh, eile 2150. osas kirjeldasin probleemi. Keegi saatis selle faili sinna, kus tema süsteem laadib alla üksusi, mis on tellimus, mille vahel on reafoorid. Teisisõnu, ALT + ENTER ja vaata, WRAP TEXT on sisse lülitatud ja nad soovivad teha kõigi nende üksuste VLOOKUP sellesse LOOKUPTABLE. Ma olen nagu, mis? Miks sa seda teed? Kuid ma kajastasin seda eile. Proovime lihtsalt välja mõelda, kuidas seda teha.
Ma tegelikult ütlesin, et noh, Power Query oleks parim viis seda teha, kuid mul oli mõte, kuidas seda viimast osa teha. Ma ütlesin, kas see on okei, kui iga üksus satub oma ritta? Ei, nad peavad selles algses järjestuses tagasi olema. Ma olen nagu, see on kohutav, kuid minu eelmise nädala Twitteri voos Tim Rodman, 27. september: "Lõpuks lugesin seda raamatut," - ma arvan, et see on PowerPivot Alchemy - "ja sain juba oma ConcatenateX soovi. ” Olin seda tehes nutikas, paludes PERHAPS ROMANX-i, kuid ma tõesti tahtsin ConcatenateX-i ja nii andis Tim mulle pea üles, et ma saan seda nüüd Power BI-s teha.
Niisiis, läksin välja oma sõprade, Rob Collie juures Power Pivot Pro juures ja Miguel Escobari juures ning teate, et nad mõlemad on suurepäraste raamatute autorid. Mul on mõlemad need raamatud, kuid see funktsioon on liiga uus, mitte kummaski raamatus. Ma ütlesin, et hei, kas te teate, kuidas seda teha? Ja Miguel võidab auhinna, sest Miguel oli täna varahommikul või eile hilisõhtul üleval - ma pole kindel, kumb - ja saatis koodi.
Hästi, nii, siin on Power Query plaan ja see on nii keeruline. Ma ei kirjuta kunagi Power Query'is plaani välja. Ma lihtsalt käin asju ajamas. Alustan algandmetest, lisan veeru INDEX, et saaksime üksusi tellimusest koos hoida, SPLIT COLUMN to ROWS, kasutades LINEFEED. Seda uut funktsiooni kasutasin podcastis teist või kolmandat korda. Kui lahe see on. Mul oli teine veerg INDEX, et saaksime üksused algsesse järjestusse sorteerida ja seejärel SALVESTADA ÜHENDUSena.
Seejärel jõuame tabeli LOOKUP juurde, teeme sellest tabeli, tabelist päringu, SALVESTA ÜHENDUSEKS - see oli seal kõige lihtsam osa - ja seejärel ühendame selle päringu ja selle päringu üksuse põhjal number, kõik vasakpoolse tabeli üksused, see on vasak tabel, mis sobib paremalt, asendage nullid üksuse numbriga. Oleme endiselt õhus, mida me tahame teha, kui midagi pole mingil põhjusel leitud. Olen selle küsimuse esitanud, kuid faili saatnud inimene ei vasta, seega asendan selle lihtsalt üksuse numbriga. Loodetavasti on õige asi lisada LOOKUPTABLE-i veel üksusi, nii et ühtegi leidmata asukohta pole, kuid siin me oleme ja siis sorteerime INDEX1 ja INDEX2 järgi, nii etasjad on õiges järjekorras tagasi ja siis oli see osa, mida ma ei suutnud välja mõelda, kuidas seda teha.
Rühmitame INDEX1 järgi, tehes TEXTJOINi või ConcatenateXi ekvivalendi, eraldajana märkena 10 kui koondaja, ja loomulikult on see see osa, mis on raske osa, kuid see on see osa, mis on siin tõesti uus see sammude komplekt. Niisiis, kui mõistate, mida TEXTJOIN teeb või suudate kontseptualiseerida, mida ConcatenateX oleks teinud, teeme seda sisuliselt seda tüüpi sammude abil. Nii, olgu. Nii et laseme selle maha.
Alustame siit. Siin on meie algandmed, sellel on pealkiri. Niisiis, ma vormindan TABELI, CONTROL + T, MINU LAUAL ON PÄISI, jah, ja siis hakkame kasutama Power Query't. Nüüd olen rakenduses Excel 2016 Office 365, nii et see on siin vahekaardi DATA vasakpoolses osas. Kui olete lihtsalt sirgjooneliselt Excel 2016, mitte Office 365, on see keskel - Hangi ja teisendage. Kui olete rakenduses Excel 2010 või 2013, on see siin oma vahekaart nimega Power Query ja kui teil seda vahekaarti pole, peate selle vahelehe alla laadima. Kui kasutate Maci või Androidi või mõnda muud Exceli võltsversiooni, siis vabandage, teie jaoks pole ühtegi Power Query'i. Hankige Exceli Windowsi versioon ja proovige seda.
Hästi, nii, me teeme elektriküsimuse tabelist, olgu, ja esimene asi, mida ma teen, on see, et ma lisan INDEKSIVEERUD ja alustan 1. ALG. , nii et see on sisuliselt järjekord 1, järjekord 2, järjekord 3, järjekord 4. Seejärel valime selle veeru ja vahekaardil TRANSFORM läheme SPLIT COLUMN, DELIMITER BY, ja nad said tuvastada, et see on LineFeed on eraldaja. Mulle meeldib, et Power Query seda tuvastab. Miks ei saa Excel, tekst veergudeks, jah, tekst veergudeks, mis on eraldaja? Ja iga kord läheme RIDADEKS RIDADEKS ja KASUTAME ERITUNNUST. Hästi, nii et see kõik on hea.
Vaadake nüüd, mis siin toimub. Meil on 999 rida, kuid nüüd on seda palju rohkem. Seega on iga selle järjekorranumbri üksus nüüd oma rida. Nüüd ei soovi selle küsimuse esitaja, et see oleks tema enda rida, kuid me peame selle muutma oma reaks, et saaksime liituda. Lisan siia uue veeru INDEX. ADD COLUMN, INDEX COLUMN, FROM 1, ja nii meil on … need on sisuliselt järjekorranumbrid ja siis need on järjestuses olevad järjestused, sest olen kindlaks teinud, et hiljem on need mingis muus järjekorras. Ma ei tea, mis järjekorda nad üle lähevad, aga siin me oleme.
Hästi, nii et KODU, mitte nuppu SULETA & LAADI, vaid rippmenüüd SULGE ja LAADI ning SULGE & LAADI. Ma ei tea, miks nende dialoogiboksi esmakordne kuvamine võtab 10 sekundit. Loome AINULT ÜHENDUSE. Klõpsake nuppu OK. Ilus. Nii et see on TABEL1, TABEL1.
Nüüd läheme meie LOOKUPTABLE'i. LOOKUPTABLE-i on lihtne töödelda. Vormindame selle tabelina. CONTROL + T. Klõpsake nuppu OK. ANDMED või POWER QUERY, kui olete vanas versioonis, TABELIST. Seda nimetatakse TABEL2. Nimetagem seda LOOKUPTABLE. Täiuslik. LOE AINULT ÜHENDUS SULETUD, LAADI, SULGE JA LAADI.
Hästi. Nüüd on meil siin kaks bitti ja ma tahan need kaks ühendada. Niisiis, me läheme lihtsalt uude kohta ja siis ANDMED, HANKI ANDMEID, Kombineerige päringuid, teeme ÜHINEMISE ja vasakpoolne tabel on TABEL1 - see on meie algandmed - - ja me kasutame seda üksuse ITEM numbrit ning abiellume sellega kuni LOOKUPTABLE ja selle üksuse numbrini. See pole tõesti intuitiivne. Seal peate mõlemal juhul klõpsama üksustel, et määratleda, mis on võti, ja VÄLIS liituda, KÕIK ESIMEST, KOKKU LEPPUVAD TEISEST, ja vaadake, et neist on 40% puudu VÄLJATUD. Need kõik on võltsandmed, kuid ka LOOKUPTABLE-is puudus algandmetest 40%. Tõesti masendav. Hästi. Niisiis, siin on meie ITEM-i number, meie 2 INDEX-välja ja seejärel meie LOOKUPTABLE. Minakavatsen seda laiendada ja küsida KIRJELDUST. Hästi, näete, et meil on siin hulk nullisid.
Hästi, nii et teeme tingimusliku veeru. Tingimuslik veerg ütleb, et vaadake seda veergu. Kui see on = nullini, siis tooge see väärtus üle, vastasel juhul kasutage selles veerus olevat väärtust. Nii et siin teeme veerus ADD COLUMN TINGIMUSELISE VEERU - kena väike kasutajaliides, mis juhatab meid läbi - kui LOOKUPTABLEDESCRIPTION võrdub NULL, siis tahame siin kasutada veergu PUNKTE, muidu tahame kasutada veerg nimega LOOKUPDESCRIPTION, olgu. Klõpsake nuppu OK ja seal me oleme. Seal on meie veerg CUSTOM, kus on kas LOOKUPTABLE'i uus väärtus või algne väärtus, kui seda ei leita. Siinkohal saame paremklõpsata ja öelda, et tahame selle veeru EEMALDADA. See oli ajutine veerg, see oli abikolonn. Nüüd, kui meil on olemas see, mida vajame, pole seda veergu enam vaja ja tegelikult selles punktisKa seda veergu pole mul vaja. Niisiis, saan paremklõpsata ja selle veeru EEMALDADA. Hästi. Nüüd on meil siin oma andmed. Ma tahan selle sortida algse INDEXi järgi. Niisiis, SORDI ÜLEMINE. See viib meie andmed õigesse järjestusse ja nüüd, kui need on sorteeritud, saan tegelikult paremklõpsata ja selle veeru EEMALDADA.
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.
Kuule, see on hetk, kus ma tavaliselt palun teil minna minu raamatut ostma, kuid täna palume teil hoopis Migueli raamatut osta. Miguel Escobar ja Ken Puls kirjutasid selle suurepärase raamatu teemal M Is For (DATA) MONKEY - parim raamat on Power Query kohta. Mine kontrollige seda.
Hästi, mähkige kokku: täna on tõesti pikk episood; meil on vaataja, laadime andmed alla süsteemist, kus iga üksus on eraldatud ALT + ENTER ja proovime iga üksuse jaoks VLOOKUP-i teha; lõi täna lahenduse Power Query, sealhulgas väljavõtte struktureeritud veeru tööriista as; kuid see töötab ainult loendis, mitte tabelis, seega pidin tabeli loendiks teisendamiseks kasutama funktsiooni TABLE.COLUMN.
Noh, hei. Tahan teid tänada, et peatusite. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2151.xlsm
