CONCATENATEX u Power Queryju. Nova funkcija TEXTJOIN je sjajna. Možete li učiniti isto sa Power Queryjem? Da. Sada možeš.
Gledaj video
- Preglednik preuzima podatke iz sustava u kojem je svaka stavka odvojena tipkama Alt + Enter
- Bill: Zašto to radiš? Preglednik: Tako nasljeđujem podatke. Želim da tako i ostane.
- Bill: Što želite učiniti s 40% vrijednosti koje nisu u tablici? Gledatelj: Nema odgovora
- Bill: Postoji složen način da se to riješi ako imate najnovije alate za Power Query.
- Umjesto toga, VBA makronaredba za njegovo rješavanje - makronaredba bi trebala raditi sve do Excel 2007
- Umjesto da radite VLOOKUP, napravite seriju Pronađi i zamijeni VBA
Prijepis videozapisa
Naučite Excel iz, Podcast Epizoda 2151.
Stvarno ne znam kako bih ovu nazvao. Ako pokušavam privući ljude koji koriste DAX, rekao bih ConcatenateX u Power Queryju ili samo ljude koji koriste redoviti Excel, ali Office 365, rekao bih TEXTJOIN u Power Queryju ili, da budem potpuno iskren, to je super složeni niz koraka u programu Power Query kako bi se omogućilo superludo rješenje u Excelu.
Hej. Dobrodošli natrag na netcast. Ja sam Bill Jelen. Pa, jučer u epizodi 2150, opisao sam problem. Netko je poslao u ovu datoteku gdje njihov sustav preuzima stavke koje su narudžbe s linijskim feedovima između njih. Drugim riječima, ALT + ENTER i pogledajte, WRAP TEXT je uključen i oni žele napraviti VLOOKUP u ovaj LOOKUPTABLE za svaku od tih stavki. Ja sam kao, što? Zašto radiš to? Ali to sam pokrivao jučer. Pokušajmo shvatiti kako to učiniti.
Zapravo sam rekao, dobro, Power Query bi bio najbolji način za to, ali zapamtio sam kako da izvedem zadnji dio. Rekao sam, je li u redu ako svaka stavka završi u svom redu? Ne, oni se moraju vratiti u ovom izvornom slijedu. Mislim da je to užasno, ali na svom Twitter feedu prošli tjedan Tim Rodman, 27. rujna: "Napokon čitajući ovu knjigu" - pretpostavljam da je to PowerPivot Alchemy - "i već je dobio svoju želju za ConcatenateX-om. " Bio sam pametnjak kad sam to učinio, tražeći PERHAPS ROMANX, ali vjerojatno sam stvarno želio ConcatenateX, i tako mi je Tim dao do znanja da to sada mogu raditi u Power BI-u.
Pa sam izašao kod svojih prijatelja, Roba Collieja iz Power Pivot Proa i Miguela Escobara, i, znate, obojica su autori sjajnih knjiga. Imam obje ove knjige, ali ova je značajka previše nova, ni u jednoj knjizi. Rekao sam, hej, znate li vi to? A Miguel osvaja nagradu jer je Miguel ustao rano jutros ili kasno sinoć - nisam siguran u koju - i poslao šifru.
U redu, pa, evo plana u Power Queryu, a ovaj je tako kompliciran. Nikad ne napišem plan u Power Queryju. Jednostavno odem raditi sve stvari. Započet ću s izvornim podacima, dodajte stupac INDEX da bismo zajedno mogli držati stavke iz narudžbe, PODJELITI STOLPAK na REDOVE pomoću LINEFEED-a. Ovo je drugi ili treći put na podcastu da koristim ovu novu značajku. Kako je to cool. Imao sam drugi stupac INDEX da bismo mogli sortirati stavke u izvorni niz, a zatim SPREMITI KAO VEZU.
Zatim ćemo doći do tablice LOOKUP, napraviti tablicu, upit iz tablice, SPREMI KAO VEZU - to će biti najlakši dio upravo tamo - i zatim spojiti ovaj upit i ovaj upit na temelju stavke broj, sve stavke iz lijeve tablice, ovo je lijeva tablica, podudara se s desne, nule zamijenite brojem stavke. Još uvijek smo u zraku što želimo učiniti kad iz nekog razloga nešto nije pronađeno. Postavio sam ovo pitanje, ali osoba koja je poslala datoteku ne odgovara, pa ću je zamijeniti brojem stavke. Nadamo se da je ispravna stvar dodati još stavki u LOOKUPTABLE kako ne bi bilo pronađenih, ali evo nas, a onda ćemo sortirati po INDEX1 i INDEX2, pa na taj način,stvari su se vratile u pravom slijedu i tada je ovo bio dio koji nisam mogao smisliti kako to učiniti.
Grupirat ćemo po INDEX1 radeći ekvivalent TEXTJOIN ili ConcatenateX sa znakom 10 kao separatorom, kao agregatorom, i, naravno, ovo je onaj dio koji je najteži, ali to je dio koji je ovdje zaista nov ovaj niz koraka. Dakle, ako razumijete što TEXTJOIN radi ili može konceptualizirati ono što bi ConcatenateX učinio, u osnovi to radimo koristeći ovakav korak. Dakle, u redu. Pa, probajmo malo.
Dakle, počet ćemo ovdje. Evo naših izvornih podataka, ima naslov. Dakle, idem na FORMAT KAO TABELA, CONTROL + T, MOJ TABLICA IMA GLAVE, da, a onda ćemo koristiti Power Query. Sada sam u programu Excel 2016 Office 365, tako da je ovdje na lijevom dijelu kartice DATA. Ako ste samo u direktnom programu Excel 2016, a ne u programu Office 365, to je u sredini - DOBITI I PRETVORITI. Ako ste u programu Excel 2010 ili 2013, to će ovdje biti vlastita kartica pod nazivom Power Query, a ako nemate tu karticu, morat ćete je preuzeti. Ako ste na Macu ili Androidu ili bilo kojoj drugoj lažnoj verziji Excela, nažalost, nema Power Queryja za vas. Nabavite Windows verziju Excela i pokušajte.
U redu, dakle, napravit ćemo Power Query iz tabele, u redu, i prvo što ću učiniti je DODATI INDEKS STOLPAC i započet ću OD 1. U redu , dakle, ovo je u osnovi red 1, nalog 2, red 3, red 4. Tada ćemo odabrati ovaj stupac i na kartici TRANSFORMIRATI ćemo SPLIT COLUMN, BY DELIMITER, i oni su mogli prepoznati da je LineFeed graničnik. Sviđa mi se što Power Query ovo otkriva. E sad, zašto Excel, tekst u stupce, da, tekst u stupce ne shvati koji je graničnik? I svaku ćemo pojavu PODIJELITI U REDOVE, I KORISTEĆI POSEBNI LIK. U redu, pa sve je to dobro.
Sad pazi što se ovdje događa. Imamo 999 redova, ali sada imamo daleko više od toga. Dakle, svaka stavka s tog broja poretka sada je svoj redak. Sada, osoba koja je postavila ovo pitanje ne želi da to bude njegov vlastiti redak, ali morat ćemo ga učiniti svojim redom kako bismo mogli izvršiti spajanje. Ovdje ću dodati novi stupac INDEX. DODAJ STUPAC, INDEKS STOLPAC, OD 1, i tako imamo … ovo su u osnovi brojevi redoslijeda, a zatim su to redoslijedi unutar naloga, jer sam utvrdio da će kasnije biti u nekom drugom redoslijedu. Ne znam u koji red se prebacuju, ali tu smo.
U redu, dakle, HOME, ne tipka ZATVORI I UTOVARI, već padajući izbornik ZATVORI I Učitaj i ZATVORI I UTOVARI. Ne znam zašto im treba 10 sekundi da prvi put prikažu ovaj dijaloški okvir. Idemo SAMO STVARATI POVEZU. Kliknite U redu. Lijep. Dakle, to je TABELA1, TABELA1.
Sad ćemo ići na naš POGLEDAJUĆI STO. LOOKUPTABLE će biti lako obraditi. Oblikovat ćemo ovo kao tablicu. CONTROL + T. Kliknite U redu. PODACI ili POWER QUERY ako imate staru verziju, OD TABLE. Ovo će se zvati TABELA2. Nazovimo to LOOKUPTABLE. Savršen. ZATVORI I UTOVARI, ZATVORI I UTOVARI, SAMO IZRADI VEZU.
U redu. Sada imamo svoja dva bita ovdje i želim spojiti ta dva. Dakle, idemo samo na novo mjesto, a zatim DATA, GET DATA, COMBINE QUERIES, napravit ćemo MERGE, a tablica s lijeve strane bit će TABELA1 - to su naši izvorni podaci - - i mi ćemo upotrijebiti ovaj ITEM broj i vjenčat ćemo ga do LOOKUPTABLE i tog ITEM broja. Tamo je stvarno ne intuitivno, u oba slučaja morate kliknuti na ARTIKLE kako biste definirali što je ključ, a VANJSKO se pridružilo, SVE OD PRVOG, PODUZEĆE OD DRUGOG, i, vidite, 40% njih nedostaje u POGLEDAJTE. Ovo su svi lažni podaci, ali izvornim podacima je i 40% nedostajalo u LOOKUPTABLE. Stvarno frustrirajuće. U redu. Dakle, ovdje je naš ITEM broj, naša 2 INDEKS polja, a zatim naš LOOKUPTABLE ovdje. Ja 'Idem to PROŠIRITI i zatražiti OPIS. Dobro, vidite da ovdje imamo gomilu nula.
U redu, napravit ćemo uvjetni stupac. Uvjetni stupac će reći pogledaj ovaj stupac. Ako je = null, prenesite ovu vrijednost u suprotnom, upotrijebite vrijednost u tom stupcu. Dakle, ovdje ćemo, pod DODATI KOLONU, napraviti KONDICIONALNI KOLONU - lijepo malo korisničko sučelje koje će nas provesti kroz ovo - ako je LOOKUPTABLEDESCRIPTION JEDNAKO NULL, onda ovdje želimo upotrijebiti STOLPAK, ali u suprotnom, želimo koristiti KOLONA koja se zove LOOKUPDESCRIPTION, u redu. Kliknite U redu i eto nas. Tu je naš PRILAGOĐENI stupac s novom vrijednošću LOOKUPTABLE ili originalnom vrijednošću ako nije pronađena. U ovom trenutku možemo kliknuti desnom tipkom i reći da želimo UKLONITI ovaj stupac. Bila je to privremena kolona, to je bila pomoćna kolona. Sad kad imamo ono što nam treba, ta nam kolona više nije potrebna, i zapravo, u ovom trenutku,Ni meni više nije potrebna ova kolumna. Dakle, mogu kliknuti desnom tipkom i UKLONITI taj stupac. U redu. Sada ovdje imamo svoje podatke. Želim to sortirati prema izvornom INDEKSU. Dakle, SORTIRAJ SE POVOLJNO. To dobiva naše podatke u pravom slijedu, a sada kad su razvrstani, zapravo mogu kliknuti desnom tipkom i UKLONITI taj stupac.
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.
Hej, ovo je točka u kojoj obično tražim da odeš kupiti moju knjigu, ali danas, umjesto toga, zamolimo te da odeš kupiti Miguelovu knjigu. Miguel Escobar i Ken Puls napisali su ovu izvrsnu knjigu o M Is For (DATA) MONKEY - najboljoj knjizi koja postoji o Power Queryju. Idi provjeri to.
U redu, završi: danas je stvarno duga epizoda; imamo preglednik, preuzima podatke iz sustava gdje je svaka stavka odvojena ALT + ENTER i pokušavamo napraviti VLOOKUP za svaku pojedinu stavku; danas je izgradio rješenje koristeći Power Query, uključujući alat za strukturirane stupce ekstrakta as; ali to radi samo na popisu, a ne na tablici, pa sam morao koristiti funkciju TABLE.COLUMN da pretvorim tablicu u popis.
Pa, hej. Želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2151.xlsm
