CONCATENATEX a Power Query-ben. Az új TEXTJOIN funkció fantasztikus. Megteheti ugyanezt a Power Query-vel? Igen. Most már tudod.
Videót nézni
- A megtekintő adatokat tölt le egy rendszerből, ahol az egyes elemeket az Alt + Enter billentyűk választják el egymástól
- Bill: Miért csinálod ezt? Néző: Így öröklöm az adatokat. Így akarom megtartani.
- Bill: Mit akarsz kezdeni a táblázatban nem szereplő értékek 40% -ával? Néző: Nincs válasz
- Bill: Ennek megoldására bonyolult módszer áll rendelkezésre, ha rendelkezik a legújabb Power Query eszközökkel.
- Ehelyett egy VBA makró a megoldáshoz - a makrónak egészen az Excel 2007-ig kell működnie
- A VLOOKUP helyett végezzen egy sor Keresést és cserét VBA-val
Videó átirat
Ismerje meg az Excel programot, a Podcast 2151. epizód.
Tényleg nem tudom, hogy hívjam ezt. Ha megpróbálom vonzani azokat az embereket, akik DAX-ot használnak, akkor a ConcatenateX-et mondanám a Power Query-ben, vagy csak azokat, akik a szokásos Excel-t, de az Office 365-et használják, a TEXTJOIN-t a Power Query-ben, vagy, hogy őszinte legyek, ez szuper összetett lépéskészlet a Power Query-ben egy szuper őrült megoldás engedélyezéséhez az Excelben.
Hé. Üdvözöljük a netcaston. Bill Jelen vagyok. Nos, tegnap a 2150-es epizódban leírtam a problémát. Valaki elküldte ezt a fájlt, ahová a rendszere letölti azokat az elemeket, amelyek sorrendben vannak, sorközökkel. Más szavakkal, az ALT + ENTER, és lássuk be, a WRAP TEXT be van kapcsolva, és szeretnének egy VLOOKUP-ot csinálni ebből a LOOKUPTABLE-ből mindegyik elemnél. Olyan vagyok, mi? Miért csinálod ezt? De tegnap erről tudósítottam. Próbáljuk meg kitalálni, hogyan lehet ezt megtenni.
Valójában azt mondtam, hogy a Power Query lenne a legjobb módszer erre, de elgondolkodtam azon, hogyan kell csinálni az utolsó részt. Azt mondtam, rendben van, ha minden elem a saját sorára kerül? Nem, ebben az eredeti sorrendben kell visszatérniük. Szeretem, ez borzalmas, de a múlt heti Twitter-hírcsatornámban Tim Rodman, szeptember 27-én: "Végre elolvastam ezt a könyvet" - feltételezem, hogy ez a PowerPivot Alchemy -, és már megkapta a ConcatenateX kívánságát. ” Intelligens voltam, amikor ezt csináltam, és megkértem a PERHAPS ROMANX-ot, de valószínűleg nagyon szerettem volna a ConcatenateX-et, és ezért Tim olyan fejjel adott nekem, hogy ezt most meg tudom csinálni a Power BI-ban.
Szóval kimentem a barátaimhoz, Rob Collie-hoz a Power Pivot Pro-hoz és Miguel Escobarhoz, és tudják, hogy mindketten remek könyvek szerzői. Mindkét könyv megvan, de ez a funkció túl új, egyik könyvben sem. Azt mondtam, hé, tudjátok ezt? És Miguel nyeri a díjat, mert Miguel ma kora reggel vagy tegnap késő este volt fent - nem tudom, melyik - és beküldte a kódot.
Rendben, szóval itt van a Power Query terve, és ez olyan bonyolult. Soha nem írok ki tervet a Power Query-ben. Csak megyek, csinálom az egész dolgot. Kezdem az eredeti adatokkal, adjak hozzá egy INDEX oszlopot, hogy a sorrendben szereplő elemeket együtt tudjuk tartani, a SPLIT COLUMN to ROWS segítségével LINEFEED-et. Ez a podcast második vagy harmadik alkalma, amikor ezt az új funkciót használtam. Mennyire klassz. Nekem volt egy második INDEX oszlopom, így az elemeket az eredeti sorrendbe tudjuk rendezni, majd MENTÉS CSATLAKOZÁSként.
Ezután eljutunk a LOOKUP táblához, táblává tesszük, lekérdezés a táblából, MENTÉS CSATLAKOZÁSNAK - ami ott a legegyszerűbb rész volt -, majd egyesítjük ezt a lekérdezést és ezt a lekérdezést elem alapján szám, az összes elem a bal oldali táblából, ez a bal oldali táblázat, jobbról illeszkedve, cserélje le a nullákat a tétel számával. Még mindig a levegőben vagyunk, mit akarunk csinálni, ha valami valamiért nem található. Feltettem ezt a kérdést, de az a személy, aki beküldte a fájlt, nem válaszol, ezért csak lecserélem a tételszámra. Remélhetőleg a helyes dolog az, ha további elemeket ad a LOOKUPTABLE-hez, így nincsenek nem találtak, de itt vagyunk, majd rendezni fogjuk az INDEX1 és az INDEX2 alapján, így így,a dolgok visszatértek a megfelelő sorrendben, és akkor ez volt az a rész, amit nem tudtam kitalálni, hogyan kell csinálni.
Az INDEX1 szerint fogunk csoportosítani, és egy TEXTJOIN vagy ConcatenateX egyenértéket alkalmazunk, elválasztóként a 10 karaktert, mint összesítőt, és természetesen ez az a rész, amely a legnehezebb, de ez az a rész, amely itt igazán új ezt a lépést. Tehát, ha megértette, hogy a TEXTJOIN mit csinál, vagy képes megfogalmazni, hogy a ConcatenateX mit tett volna, akkor ezt lényegében egy ilyen lépés segítségével tesszük. Szóval, rendben. Tehát adjunk egy lövést.
Tehát itt kezdjük. Itt vannak az eredeti adataink, van címsora. Tehát meg fogom formázni a táblázatot, a CONTROL + T, A TÁBLÁMON FEJEZŐK vannak, igen, és akkor a Power Query-t fogjuk használni. Most az Excel 2016 Office 365-ben vagyok, tehát itt van a DATA fül bal oldalán. Ha csak egyenesen az Excel 2016-ot használja, nem pedig az Office 365-et, akkor az a közepén van - GET & TRANSFORM. Ha az Excel 2010-ben vagy a 2013-ban van, akkor itt lesz a saját lapja, itt Power Query néven, és ha még nem rendelkezik ezzel a lappal, le kell töltenie ezt a lapot. Ha Mac vagy Android vagy az Excel bármely más hamis verzióját használja, sajnálom, nincs Power Query az Ön számára. Szerezzen be egy Excel Windows-verziót, és próbálja ki.
Rendben, szóval, áramellátást fogunk csinálni egy táblából, rendben, és az első dolog, amit tenni fogok, hogy HOZZÁADOK INDEX OSZLOPT, és 1-től indulok. , tehát ez lényegében az 1. sorrend, a 2. sorrend, a 3. sorrend, a 4. sorrend. Ezután ezt az oszlopot választjuk, és a TRANSFORM fülön a SPLIT COLUMN, DELIMITER BY-vel megyünk, és képesek voltak észlelni, hogy LineFeed a határoló. Szeretem, hogy a Power Query észleli ezt. Miért nem deríti ki az Excel, a szöveg oszlopokba, igen, a szöveg oszlopokba, mi az elválasztó? És minden egyes esetnél fel fogunk osztani a sorokba, és a KÜLÖNLEGES JELLEMZŐKET HASZNÁLJUK. Rendben, szóval ez minden jó.
Most figyelje meg, mi történik itt. 999 sorunk van, de most ennél sokkal több van. Tehát, minden tétel ebben a rendelési számban most a saját sora. Az a személy, aki feltette ezt a kérdést, nem akarja, hogy a saját sora legyen, de a saját sorává kell tennünk, hogy meg tudjuk csinálni a csatlakozást. Új INDEX oszlopot adok ide. ADD OSZLOP, INDEX OSZLOP, 1-TŐL, és így van … ezek lényegében a rendelési számok, és akkor ezek a sorrend a sorrendben, mert megállapítottam, hogy később ezek valamilyen más sorrendben lesznek. Nem tudom, milyen sorrendre váltanak, de itt vagyunk.
Rendben, tehát, a HOME, nem a ZÁRÁS ÉS TÖLTÉS gombra, hanem a ZÁRÁS ÉS TÖLTÉS legördülő menüre, valamint a ZÁRÁS ÉS TÖLTÉS gombra. Nem tudom, miért telik el 10 másodperc, mire első alkalommal megjelenítik ezt a párbeszédpanelt. KIZÁRÓLAG CSATLAKOZÁST fogunk létrehozni. Kattintson az OK gombra. Gyönyörű. Tehát ez a TABLE1, TABLE1.
Most megyünk a LOOKUPTABLE-ra. A LOOKUPTABLE könnyen feldolgozható lesz. Ezt táblázatként formázzuk. CONTROL + T. Kattintson az OK gombra. ADATOK, vagy POWER QUERY, ha régi verzióban van, FROM TABLE. Ezt TABLE2-nek fogják hívni. Nevezzük úgy, hogy LOOKUPTABLE. Tökéletes. CSAK CSATLAKOZTATÁST LÉTRE ZÁRNI ÉS TÖLTENI, ZÁRNI ÉS TÖLTENI
Rendben. Most itt van a két bitünk, és ezt a kettőt össze akarom egyesíteni. Tehát csak egy új helyre megyünk, majd az ADATOK, AZ ADATOK KERESÉSE, A KÉRDÉSEK ÖSSZEFOGLALÁSA, EGYESÍTÉSET fogunk végezni, és a bal oldali táblázat TABLE1 lesz - ez az eredeti adatunk - - és ezt az ITEM számot fogjuk használni, és feleségül veszjük ezt a LOOKUPTABLE és az ITEM számig. Ez valóban nem intuitív, és mindkét esetben a TÉTELEKRE kell kattintanod, hogy meghatározd, mi a kulcs, és egy KÜLSŐ csatlakozáshoz, MINDENTŐL ELSŐ, MÁSODTÓL MEGFELELŐ, és, lám, ezek 40% -a hiányzik a KERESÉSI TÁBLÁZAT. Ez mind hamis adat, de az eredeti adatok 40% -a hiányzott a LOOKUPTABLE-ből is. Tényleg olyan frusztráló. Rendben. Tehát itt van az ITEM számunk, a 2 INDEX mezőnk, majd itt a LOOKUPTABLE. ÉN'ezt kibővítem, és megkérem a LEÍRÁST. Rendben, látod, hogy van egy csomó nullánk.
Rendben, tehát feltételes oszlopot fogunk csinálni. A feltételes oszlop azt fogja mondani, hogy nézze meg ezt az oszlopot. Ha ez = nullára áll, akkor vigye át ezt az értéket, különben használja az oszlopban szereplő értéket. Tehát itt, a HOZZÁADÁS OSZLOP alatt, FELTÉTELES OSZLOPOT fogunk csinálni - szép kis felhasználói felület, amely végigvezeti velünk - ha a LOOKUPTABLEDESCRIPTION NULL egyenlő, akkor itt egy OSZLOPT akarunk használni a TÉTELEKBŐL, ellenkező esetben használni szeretnénk az OSZLOP nevű LOOKUPDESCRIPTION, rendben. Kattintson az OK gombra, és ott vagyunk. Van CUSTOM oszlopunk, amely vagy a LOOKUPTABLE új értékét, vagy az eredeti értéket, ha nem található. Ezen a ponton jobb egérgombbal kattinthatunk, és azt mondhatjuk, hogy el akarjuk távolítani ezt az oszlopot. Ez egy ideiglenes oszlop volt, ez egy segítő oszlop. Most, hogy megvan, amire szükségünk van, már nincs szükségünk erre az oszlopra, és valójában ezen a pontonNekem sem kell ez az oszlop már. Tehát jobb egérgombbal kattintva eltávolíthatom ezt az oszlopot. Rendben. Itt vannak az adataink. Az eredeti INDEX alapján szeretném rendezni. Tehát, SORT FELEMELŐ. Ez az adatainkat a megfelelő sorrendbe állítja, és most, hogy rendezve vannak, valójában jobb egérgombbal kattintva eltávolíthatom ezt az oszlopot.
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.
Na, hé, itt szoktam megkérni, hogy menjen a könyvem megvásárlásához, de ma inkább kérjük meg, hogy menjen meg Miguel könyvének megvásárlásához. Miguel Escobar és Ken Puls írták ezt a kiváló könyvet az M Is For (DATA) MONKEY-ról - a legjobb könyv a Power Query-nél található. Menj, nézd meg.
Rendben, zárjuk le: a mai egy igazán hosszú epizód; van megtekintőnk, adatokat tölt le egy rendszerből, ahol az egyes elemeket ALT + ENTER választja el egymástól, és megpróbálunk VLOOKUP-ot csinálni minden egyes elemhez; ma épített egy megoldást a Power Query használatával, beleértve az extraktum strukturált oszlop eszközét; de ez csak egy listán működik, nem egy táblán, ezért a TABLE.COLUMN függvényt kellett használnom a táblázat listává alakításához.
Hát hello. Szeretném megköszönni, hogy megálltál. Legközelebb találkozunk egy újabb netcast-lal.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2151.xlsm
