CONCATENATEX v Power Query. Nova funkcija TEXTJOIN je izjemna. Ali lahko storite isto s Power Query? Da. Zdaj lahko.
Oglejte si video
- Pregledovalnik prenese podatke iz sistema, kjer je vsak element ločen s kombinacijo tipk Alt + Enter
- Bill: Zakaj to počneš? Pregledovalnik: Tako podedujem podatke. Tako naj tudi ostane.
- Bill: Kaj želite storiti s 40% vrednosti, ki niso v tabeli? Gledalec: Brez odgovora
- Bill: Če imate najnovejša orodja Power Query, lahko to rešite na zapleten način.
- Namesto tega makro VBA za njegovo rešitev - makro bi moral delovati vse do Excel 2007
- Namesto da izvajate VLOOKUP, izvedite serijo Najdi in zamenjaj z VBA
Video zapis
Naučite se iz Excela, Podcast Episode 2151.
Res ne vem, kako bi temu rekel. Če poskušam pritegniti ljudi, ki uporabljajo DAX, bi rekel ConcatenateX v Power Queryju ali samo ljudi, ki uporabljajo običajni Excel, vendar Office 365, bi rekel TEXTJOIN v Power Query ali, če sem povsem iskren, super kompleksen nabor korakov v Power Query, ki omogoča izjemno noro rešitev v Excelu.
Zdravo. Dobrodošli nazaj na netcast. Jaz sem Bill Jelen. No, včeraj v epizodi 2150 sem opisal težavo. Nekdo je poslal to datoteko, kjer njen sistem nalaga elemente, ki so naročilo, in med njimi podajajo vrstice. Z drugimi besedami, ALT + ENTER in glej, WRAP TEXT je vklopljen in želijo narediti VLOOKUP v tej LOOKUPTABLE za vsak od teh elementov. Sem kot, kaj? Zakaj to počneš? Ampak to sem pokril včeraj. Poskusimo samo ugotoviti, kako to narediti.
Pravzaprav sem rekel, no, Power Query bi bil najboljši način za to, vendar sem se zataknil, kako narediti zadnji del. Rekel sem, ali je v redu, če vsak predmet konča v svoji vrstici? Ne, vrniti se morajo v tem prvotnem zaporedju. Všeč mi je, to je grozno, toda na mojem Twitterju prejšnji teden je Tim Rodman, 27. septembra: "Končno prebral to knjigo," - predvidevam, da je to PowerPivot Alchemy - "in že dobil svojo željo ConcatenateX. " Ko sem to počel, sem bil pameten, prosil sem za PERHAPS ROMANX, toda verjetno sem si res želel ConcatenateX, zato mi je Tim povedal, da lahko zdaj to storim v Power BI.
Tako sem šel k prijateljem, Robu Collieju iz Power Pivot Pro in Miguelu Escobarju, in veste, oba sta avtorja odličnih knjig. Imam obe knjigi, vendar je ta funkcija preveč nova, ne v nobeni knjigi. Sem rekel, hej, ali veste kako to narediti? In Miguel je dobil nagrado, ker je bil Miguel zgodaj zjutraj ali pozno zvečer - nisem prepričan, katero - in poslal kodo.
Torej, tukaj je načrt v Power Queryju, ta pa je tako zapleten. Nikoli ne izpišem načrta v Power Query. Jaz grem samo narediti vse stvari. Začel bom z izvirnimi podatki, dodal stolpec INDEX, da bomo lahko elemente iz naročila skupaj obdržali, RAZDELI STOLPEC na VRSTE s pomočjo LINEFEED. To novo funkcijo uporabljam že drugič ali tretjič v podcastu. Kako kul je to. Imel sem drugi stolpec INDEX, da lahko razvrstimo elemente v prvotno zaporedje in nato SHRANIMO KOT POVEZAVO.
Nato bomo prišli do tabele LOOKUP, naredili tabelo, poizvedbo iz tabele, SHRANI KOT POVEZAVO - to bi bil najlažji del tam - in nato poizvedbo in to poizvedbo združimo na podlagi elementa številka, vsi elementi iz leve tabele, to je leva tabela, ki se ujema z desne, zamenjajte ničle s številko elementa. Še vedno smo v zraku, kaj želimo početi, če iz nekega razloga česa ne najdemo. Zastavil sem to vprašanje, toda oseba, ki je poslala datoteko, ne odgovori, zato jo bom le zamenjala s številko elementa. Upajmo, da je prava stvar, da v POGLEDAJOČO TABO dodate več elementov, da ne bo nobenih najdb, ampak tu smo, nato pa bomo razvrstili po INDEX1 in INDEX2, tako da na ta način,stvari so spet v pravem zaporedju in potem je bil to del, ki ga nisem mogel ugotoviti, kako to storiti.
Združili se bomo po INDEX1, ki bo naredil ekvivalent TEXTJOIN ali ConcatenateX z znakom 10 kot ločilom kot agregatorjem in, seveda, to je del, ki je najtežji del, toda del, ki je tukaj resnično nov ta sklop korakov. Torej, če razumete, kaj TEXTJOIN počne ali lahko konceptualizira, kaj bi naredil ConcatenateX, to v bistvu počnemo s tovrstnimi koraki. Torej, v redu. Torej, poskusimo.
Torej, začeli bomo tukaj. Tu so naši prvotni podatki, ki imajo naslov. Torej, oblikoval bom FORMAT AS TABLE, CONTROL + T, MOJA MIZA IMA GLAVE, da, potem pa bomo uporabili Power Query. Zdaj sem v Excelu 2016 Office 365, tako da je tukaj na levem delu zavihka PODATKI. Če uporabljate samo Excel 2016, ne Office 365, je na sredini - GET & TRANSFORM. Če uporabljate Excel 2010 ali 2013, bo tu svoj lasten zavihek, imenovan Power Query, in če ga nimate, ga boste morali prenesti. Če uporabljate Mac ali Android ali katero koli drugo ponarejeno različico Excela, vam žal ni Power Query. Pridobite različico Excela za Windows in poskusite.
Torej, naredili bomo Power Query IZ TABELE, v redu, in prva stvar, ki jo bom naredil, je DODATI INDEKSNI STOLPEC in začel bom OD 1. V redu , torej, to je v bistvu naročilo 1, naročilo 2, naročilo 3, naročilo 4. Nato bomo izbrali ta stolpec in na zavihku TRANSFORMACIJA bomo razdelili stolpec z DELIMITER in lahko bodo zazna, da gre za LineFeed, je ločilo. Všeč mi je, da Power Query to zazna. Zakaj zdaj Excel, besedilo v stolpce, ja, besedilo v stolpce ne ugotovi, kaj je ločilo? In vsakič, ko se bomo razdelili na vrstice, in z uporabo posebnega znaka. Torej, vse to je dobro.
Zdaj pazi, kaj se tukaj dogaja. Imamo 999 vrstic, zdaj pa imamo veliko več od tega. Torej, vsak element na tej zaporedni številki je zdaj lastna vrstica. Zdaj oseba, ki je postavila to vprašanje, noče, da je to lastna vrstica, vendar jo bomo morali narediti kot svojo vrstico, da bomo lahko združili. Sem bom dodal nov stolpec INDEX. DODAJ STOLPEC, KAZALO STOLPEC, OD 1, in tako imamo … to so v bistvu številke vrstnega reda in potem so to zaporedja znotraj naročila, ker sem ugotovil, da bodo kasneje ta v nekem drugem vrstnem redu. Ne vem, v kakšen vrstni red se preusmerijo, ampak tu smo.
V redu, torej HOME, ne gumba ZAPRI IN NALOŽI, temveč spustni meni ZAPRI IN NALOŽI in ZAPRI IN NALOŽI. Ne vem, zakaj traja 10 sekund, da prvič prikažejo to pogovorno okno. Ustvarili bomo SAMO POVEZAVO. Kliknite V redu. Čudovito. Torej, to je TABLE1, TABLE1.
Zdaj bomo šli v naš IŠČI. LOOKUPTABLE bo enostavno obdelati. To bomo formatirali kot tabelo. CONTROL + T. Kliknite V redu. PODATKI ali POWER QUERY, če ste v stari različici, FROM TABLE. To se bo imenovalo TABLE2. Poimenujmo ga LOOKUPTABLE. Odlično. ZAPRI IN NALOŽI, ZAPRI IN NAROČI, SAMO Ustvari povezavo.
Vredu. Zdaj imamo tukaj svoja dva bita in ta dva želim združiti. Torej, šli bomo samo na novo mesto in nato DATA, GET DATA, COMBINE QUERIES, naredili bomo MERGE in tabela na levi bo TABELA1 - to so naši prvotni podatki - - in uporabili bomo to številko ITEM in jo bomo poročili do LOOKUPTABLE in te številke ITEM. Tam je res neintuitivno. V obeh primerih morate klikniti na POSTAVKE, da določite, kaj je ključ, in ZUNANJO pridružitev, VSE OD PRVEGA, KI SE UDARI OD DRUGE, in glejte, teh manjka 40% IŠČI. To so vsi ponarejeni podatki, vendar je tudi v LOOKUPTABLE manjkalo prvotnim podatkom. Resnično frustrirajoče. Vredu. Torej, tu je naša številka ITEM, naši dve polji INDEX in nato naš IŠČI. JAZ'MORAM TO RAZŠIRITI in vprašati za OPIS. V redu, vidite, tukaj imamo kup nič.
Torej, naredili bomo pogojni stolpec. Pogojni stolpec bo rekel, poglejte ta stolpec. Če je = nič, potem to vrednost prenesite, sicer uporabite vrednost, ki je v tem stolpcu. Torej, tukaj bomo pod DODAJ STOLPEC naredili POGOJEN STOLPEC - lep mali uporabniški vmesnik, ki nas bo vodil skozi to - če je LOOKUPTABLEDESCRIPTION ENAKS NULL, potem želimo tukaj uporabiti STOLPEC STAVK, sicer želimo uporabiti stolpec, imenovan LOOKUPDESCRIPTION, v redu. Kliknite V redu in tu smo. Obstaja naš stolpec CUSTOM z novo vrednostjo iz LOOKUPTABLE ali prvotno vrednostjo, če je ni mogoče najti. Na tej točki lahko z desno miškino tipko kliknemo in rečemo, da želimo ODSTRANITI ta stolpec. Bila je začasna kolona, pomožna kolona. Zdaj, ko imamo tisto, kar potrebujemo, tega stolpca ne potrebujemo več in dejansko na tej točkiTudi te kolumne ne rabim več. Tako lahko z desno miškino tipko kliknem in ODSTRANIM ta stolpec. Vredu. Zdaj imamo tukaj svoje podatke. Razvrstiti ga želim po prvotnem INDEKSU. Torej, RAZPOREDI POVEČALO. Tako se naši podatki prikažejo v pravem zaporedju in zdaj, ko so razvrščeni, lahko dejansko kliknem z desno miškino tipko in ODSTRANIM ta stolpec.
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.
Zdaj, hej, to je točka, kjer vas običajno prosim, da greste kupiti mojo knjigo, danes pa vas prosimo, da greste kupiti Miguelovo knjigo. Miguel Escobar in Ken Puls sta to izvrstno knjigo napisala na temo OPA M JE ZA (PODATKE) - najboljša knjiga o Power Query. Pojdi preveriti to.
V redu, zaključite: danes je res dolga epizoda; imamo pregledovalnik, prenese podatke iz sistema, kjer je vsak element ločen z ALT + ENTER in poskušamo narediti VLOOKUP za vsak posamezen element; danes zgradil rešitev z uporabo Power Query, vključno z orodjem za strukturirane stolpce izvlečka as; vendar to deluje samo na seznamu, ne pa na tabeli, zato sem moral za pretvorbo tabele v seznam uporabiti funkcijo TABLE.COLUMN.
No, hej. Želim se vam zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2151.xlsm
