CONCATENATEX „Power Query“. Naujoji TEXTJOIN funkcija yra nuostabi. Ar galite padaryti tą patį su „Power Query“? Taip. Dabar gali.
Žiūrėti video
- Žiūrintysis atsisiunčia duomenis iš sistemos, kurioje kiekvienas elementas yra atskirtas Alt + Enter
- Bilas: Kodėl tu tai darai? Žiūrovas: Aš taip paveldu duomenis. Aš noriu tai išlaikyti.
- Bilas: Ką norite daryti su 40% reikšmių, kurios nėra lentelėje? Žiūrovas: neatsakyta
- Bilas: Yra sudėtingas būdas tai išspręsti, jei turite naujausius „Power Query“ įrankius.
- Vietoj to, VBA makrokomandą jai išspręsti - makrokomanda turėtų veikti iki pat „Excel 2007“
- Užuot atlikę VLOOKUP, atlikite „Find & Replace with VBA“ seriją
Vaizdo įrašo nuorašas
Mokykitės „Excel“ iš „Podcast“ serijos 2151.
Tikrai nežinau, kaip šį pavadinti. Jei bandyčiau pritraukti DAX naudojančius žmones, „Power Query“ sakyčiau „ConcatenateX“ arba tik tuos, kurie naudoja įprastą „Excel“, bet „Office 365“, „Power Query“ sakyčiau „TEXTJOIN“ arba, tiesą sakant, tai labai sudėtingas „Power Query“ žingsnių rinkinys, kad „Excel“ būtų įmanomas beprotiškas sprendimas.
Ei. Sveiki sugrįžę į internetinę transliaciją. Aš esu Billas Jelenas. Na, vakar 2150 epizode aprašiau problemą. Kažkas atsiuntė šį failą, kur jo sistema atsisiunčia užsakymą turinčius elementus su eilutėmis. Kitaip tariant, ALT + ENTER ir matote, kad WRAP TEXT yra įjungtas, ir jie nori atlikti VLOOKUP į šį LOOKUPTABLE kiekvieną iš šių elementų. Aš kaip, ką? Kodėl tu tai darai? Bet aš apie tai vakar kalbėjau. Pabandykime tik išsiaiškinti, kaip tai padaryti.
Aš iš tikrųjų pasakiau, kad „Power Query“ būtų geriausias būdas tai padaryti, bet aš susimąstiau, kaip atlikti paskutinę dalį. Aš pasakiau, ar gerai, jei kiekvienas daiktas atsiduria savo eilėje? Ne, jie turi grįžti šia originalia seka. Man patinka, kad tai siaubinga, bet, mano praeitos savaitės „Twitter“ sraute, Timas Rodmanas, rugsėjo 27 d.: „Pagaliau perskaitydamas šią knygą“ - spėju, kad tai „PowerPivot Alchemy“ - ir jau sulaukiau jo noro „ConcatenateX“. “ Tai darydamas buvau protingas, prašydamas „PERHAPS ROMANX“, bet tikriausiai labai norėjau „ConcatenateX“, todėl Timas man davė galvą, kad dabar galiu tai padaryti „Power BI“.
Taigi, aš išėjau pas savo draugus Robą Collie iš „Power Pivot Pro“ ir Miguelį Escobarą, ir, žinote, jie abu yra puikių knygų autoriai. Turiu abi šias knygas, tačiau ši funkcija yra per nauja, nėra nė vienoje knygoje. Aš pasakiau: ei, ar jūs, vaikinai, mokate tai padaryti? Ir Migelis laimi prizą, nes Migelis buvo anksti ryte ar vakar vakare - nesu tikras, kuris - ir atsiuntė kodą.
Na, gerai, štai „Power Query“ planas yra toks sudėtingas. Niekada nerašau plano „Power Query“. Aš tiesiog einu daryti visus reikalus. Pradėsiu nuo pradinių duomenų, pridėsiu INDEX stulpelį, kad elementus iš užsakymo galėtume laikyti kartu, SPLIT COLUMN to ROWS using LINEFEED. Tai antrą ar trečią kartą podcast'e naudoju šią naują funkciją. Argi tai ne šaunu. Turėjau antrą INDEX stulpelį, kad galėtume rūšiuoti elementus į pradinę seką ir tada IŠSAUGOTI KAIP JUNGTĮ.
Tada mes eisime prie lentelės LOOKUP, padarysime ją lentele, užklausa iš lentelės, IŠSAUGOTI KAIP JUNGTIS - tai buvo lengviausia dalis ten - ir tada sujungsime šią ir šią užklausą pagal elementą numeris, visi elementai iš kairės lentelės, tai yra kairė lentelė, deranti iš dešinės, pakeiskite nulį elemento numeriu. Mes vis dar kyla ore, ką norime padaryti, kai kažkas dėl kažkokių priežasčių nerandama. Aš uždaviau šį klausimą, tačiau failą atsiuntęs asmuo neatsako, todėl aš jį tiesiog pakeisiu prekės numeriu. Tikimės, kad teisingas dalykas yra pridėti daugiau elementų prie LOOKUPTABLE, kad nebūtų jokių nerastų daiktų, bet štai mes esame, ir tada mes rūšiuosime pagal INDEX1 ir INDEX2, taigi tokiu būdu,viskas grįžta teisinga seka, ir tada aš negalėjau suprasti, kaip tai padaryti.
Grupuosime pagal INDEX1, atlikdami „TEXTJOIN“ arba „ConcatenateX“ atitikmenį, kurio skiriamasis simbolis yra 10, kaip agregatorius, ir, žinoma, tai yra sunkiausia dalis, bet čia tikrai nauja šį žingsnių rinkinį. Taigi, jei suprantate, ką „TEXTJOIN“ daro arba galite konceptualizuoti, ką būtų padaręs „ConcatenateX“, iš esmės tai darome naudodami tokį žingsnį. Taigi, gerai. Taigi, duokime tai.
Taigi, mes pradėsime čia. Čia yra mūsų pirminiai duomenys, turi antraštę. Taigi, aš suformuluosiu kaip lentelę, „Control + T“, mano lentelėje yra antraštės, taip, tada mes naudosimės „Power Query“. Dabar aš naudojuosi „Excel 2016 Office 365“, taigi čia yra kairėje skirtuko DUOMENYS dalyje. Jei esate tik tiesiai „Excel 2016“, o ne „Office 365“, jis yra viduryje - GET & TRANSFORM. Jei naudojate „Excel 2010“ arba „2013“, tai bus čia jo paties skirtukas, vadinamas „Power Query“, o jei šio skirtuko neturite, turėsite atsisiųsti tą skirtuką. Jei naudojate „Mac“ ar „Android“ ar bet kurią kitą netikrą „Excel“ versiją, atsiprašau, jums nereikia „Power Query“. Gaukite „Windows“ versiją „Excel“ ir išbandykite.
Gerai, taigi, atliksime maitinimo užklausą IŠ LENTELĖS, gerai, ir pirmas dalykas, kurį aš padarysiu, bus PRIDĖTI RODYKLĖS SKILTĮ ir pradėsiu nuo 1. Gerai , taigi, tai iš esmės yra 1, 2, 3, 4, 4 eilės. Tada mes pasirinksime šį stulpelį, o skirtuke TRANSFORM pereisime į SPLIT COLUMN BY BY DELIMITER ir jie galėjo nustatyti, kad tai yra „LineFeed“ yra ribotuvas. Man patinka, kad „Power Query“ tai aptinka. Kodėl „Excel“, tekstas į stulpelius, taip, tekstas į stulpelius nesupranta, kas yra ribotuvas? Kiekvieną atvejį mes suskaidysime į eiles ir NAUDOSIME SPECIALIĄ CHARAKTERĮ. Gerai, todėl viskas gerai.
Dabar stebėkite, kas čia vyksta. Turime 999 eiles, bet dabar turime kur kas daugiau. Taigi, kiekvienas to užsakymo numerio elementas dabar yra jo eilutė. Dabar asmuo, kuris uždavė šį klausimą, nenori, kad tai būtų jo paties eilutė, bet mes turėsime padaryti tai savo eilute, kad galėtume prisijungti. Aš čia pridėsiu naują INDEX stulpelį. PRIDĖTI SKILTĮ, RODYKLĖS SKILTĮ, NUO 1, taigi mes turime … tai iš esmės yra eilės numeriai, o tada seka eilės tvarka, nes aš nusprendžiau, kad vėliau tai bus kita tvarka. Nežinau, į kurią eilę jie pereina, bet štai mes.
Gerai, taigi, NAMAI, ne mygtukas UŽDARYTI IR ĮKROSTI, bet išskleidžiamasis meniu UŽDARYTI IR ĮKROSTI, o UŽDARYTI IR ĮKELTI. Nežinau, kodėl užtrunka 10 sekundžių, kol jie pirmą kartą rodo šį dialogo langą. Mes ketiname sukurti TIK JUNGTĮ. Spustelėkite Gerai. Graži. Taigi tai yra LENTELĖ1, LENTELĖ1.
Dabar eisime į savo LOOKUPTABLE. LOOKUPTABLE bus lengva apdoroti. Formuosime tai kaip lentelę. CONTROL + T. Spustelėkite Gerai. DUOMENYS arba „POWER QUERY“, jei naudojate senesnę versiją, IŠ LENTELĖS. Tai bus vadinama LENTELE2. Pavadinkime tai LOOKUPTABLE. Puikus. UŽDARYTI IR ĮKELTI, UŽDARYTI IR ĮKELTI TIK JUNGTĮ.
Gerai. Dabar mes turime čia savo du bitus ir aš noriu tuos du sujungti. Taigi, mes tiesiog eisime į naują vietą ir tada DUOMENYS, GAUTI DUOMENIS, DERINKITE UŽKLAUSAS, mes sujungsime, o kairėje esanti lentelė bus TABLE1 - tai yra mūsų pirminiai duomenys - - ir mes naudosime šį ITEM numerį ir vesime tai iki LOOKUPTABLE ir to ITEM numerio. Tai tikrai ne intuityvus dalykas. Jei norite apibrėžti, kas yra raktas, abiem atvejais turite spustelėti PREKIUS ir prisijungti prie IŠORĖS, VISŲ NUO PIRMOSIOS, SUSITIKTŲ NUO ANTROS, ir, matote, jų trūksta 40% PAŽIŪRĖTINAS. Tai visi netikri duomenys, tačiau 40% trūksta pradinių duomenų ir LOOKUPTABLE. Tikrai kažkaip apmaudu. Gerai. Taigi, čia yra mūsų ITEM numeris, mūsų 2 INDEX laukai ir tada čia LOOKUPTABLE. Aš 'Aš ketinu IŠPLĖSTI tai ir paprašyti APRAŠYMO. Gerai, matai, kad čia yra krūva niekų.
Gerai, taigi, atliksime sąlyginį stulpelį. Sąlyginis stulpelis pasakys pažvelgti į šią stulpelį. Jei tai = iki nulio, tada perkelkite šią vertę, kitaip naudokite vertę, esančią tame stulpelyje. Taigi čia, po PRIDĖTI SKILTĮ, atliksime SĄLYGOS SKILTĮ - maloni maža vartotojo sąsaja, kuri padės mums tai padaryti - jei LOOKUPTABLEDESCRIPTION LYGINA NULL, tada mes norime čia naudoti PASTABAS, PASTABOS, kitaip mes norime naudoti kolona pavadinimu LOOKUPDESCRIPTION, gerai. Spustelėkite Gerai, ir mes esame. Čia yra mūsų CUSTOM stulpelis su nauja reikšme iš LOOKUPTABLE arba pradine verte, jei ji nerasta. Šiuo metu galime spustelėti dešiniuoju pelės klavišu ir pasakyti, kad norime PAŠALINTI šį stulpelį. Tai buvo laikina kolona, tai buvo pagalbinė kolona. Dabar, kai turime tai, ko mums reikia, to stulpelio mums nebereikia, ir iš tikrųjų šiuo metuMan nebereikia ir šio stulpelio. Taigi, galiu spustelėti dešiniuoju pelės mygtuku ir IŠIMTI tą stulpelį. Gerai. Dabar mes turime savo duomenis. Noriu rūšiuoti pagal originalų INDEX. Taigi, RŪŠIUOTI DIDĖJANTI. Taip mūsų duomenys patenka į reikiamą seką, o dabar, kai jie yra surūšiuoti, aš iš tikrųjų galiu spustelėti dešiniuoju pelės mygtuku ir IŠIMTI tą stulpelį.
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.
Ei, tai yra ta vieta, kur paprastai tavęs paprašau nueiti nusipirkti mano knygos, bet šiandien paprašykime tavęs nusipirkti Migelio knygą. Miguelis Escobaras ir Kenas Pulsas parašė šią puikią knygą apie „M Is For“ (DATA) MONKEY - geriausią knygą apie „Power Query“. Eik tai patikrinti.
Gerai, baigk: šiandien yra tikrai ilgas epizodas; mes turime žiūriklį, atsisiunčiame duomenis iš sistemos, kurioje kiekvienas elementas yra atskirtas ALT + ENTER, ir mes bandome atlikti VLOOKUP kiekvienam elementui; šiandien sukūrė sprendimą naudodamas „Power Query“, įskaitant struktūrinio stulpelio įrankį „ekstraktas“ bet tai veikia tik sąraše, o ne lentelėje, todėl norėdamas konvertuoti lentelę į sąrašą, turėjau naudoti funkciją TABLE.COLUMN.
Na, ei. Noriu padėkoti, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2151.xlsm
