CONCATENATEX v Power Query. Nová funkcia TEXTJOIN je úžasná. Môžete robiť to isté s Power Query? Áno. Teraz môžeš.
Pozeraj video
- Prehliadač sťahuje údaje zo systému, v ktorom sú jednotlivé položky oddelené klávesovou skratkou Alt + Enter
- Bill: Prečo to robíš? Prehliadač: Takto dedím údaje. Chcem to tak udržať.
- Bill: Čo chceš urobiť so 40% hodnôt, ktoré nie sú v tabuľke? Prehliadač: Žiadna odpoveď
- Bill: Existuje zložitý spôsob, ako to vyriešiť, ak máte najnovšie nástroje Power Query.
- Namiesto toho to treba vyriešiť makrom VBA - makro by malo fungovať až do programu Excel 2007
- Namiesto toho, aby ste robili VLOOKUP, urobte sériu Find & Replace with VBA
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2151.
Fakt neviem, ako mám nazvať tento. Ak sa snažím prilákať ľudí, ktorí používajú DAX, povedal by som ConcatenateX v Power Query, alebo len ľudí, ktorí používajú bežný Excel, ale Office 365, povedal by som TEXTJOIN v Power Query, alebo, ak mám byť úprimný, je to superkomplexná sada krokov v aplikácii Power Query, ktorá umožňuje superšialené riešenie v programe Excel.
Ahoj. Vitajte späť na netcaste. Som Bill Jelen. Včera v epizóde 2150 som popísal problém. Niekto poslal do tohto súboru, kde jeho systém sťahuje položky, ktoré sú objednávkou, s riadkami medzi nimi. Inými slovami, ALT + ENTER a uvidíte, že je zapnutý WRAP TEXT a pre každú z týchto položiek chcú urobiť VLOOKUP do tohto LOOKUPTABLE. Som rád, čo? Prečo to robíš? Ale včera som to zakryl. Skúsme to prísť na to, ako na to.
Vlastne som povedal, no, Power Query by bol najlepší spôsob, ako to urobiť, ale narazil som na to, ako urobiť posledný diel. Povedal som, je v poriadku, ak každá položka končí v samostatnom rade? Nie, musia byť späť v tomto pôvodnom poradí. Som rád, že je to hrozné, ale na mojom twitterovom kanáli z minulého týždňa Tim Rodman, 27. septembra: „Konečne si prečítam túto knihu“ - myslím, že je to alchýmia PowerPivot - „a už som dostal svoje želanie ConcatenateX. “ Keď som to robil, bol som smartass a žiadal som o PERHAPS ROMANX, ale pravdepodobne som skutočne chcel ConcatenateX, a tak mi Tim dal správu, že to teraz môžem urobiť v Power BI.
Išiel som teda za svojimi priateľmi, Robom Collieom z Power Pivot Pro a Miguelom Escobarom, a viete, obaja sú autormi skvelých kníh. Mám obe tieto knihy, ale táto funkcia je príliš nová, ani v jednej z nich. Povedal som, hej, viete chlapci, ako to urobiť? A Miguel vyhráva cenu, pretože Miguel bol hore skoro ráno alebo včera neskoro večer - nie som si istý, ktorý z nich - a poslal kód.
Dobre, tak, tu je plán v Power Query a tento je taký komplikovaný. Nikdy v Power Query nevypisujem plán. Proste idem robiť celé veci. Začnem pôvodnými údajmi, pridám stĺpec INDEX, aby sme mohli položky z objednávky udržať pohromade, SPLIT COLUMN do ROWS pomocou LINEFEED. Túto novú funkciu som v podcaste použil už druhý alebo tretíkrát. Aké je to super. Mal som druhý stĺpec INDEX, aby sme mohli triediť položky do pôvodnej postupnosti, a potom ULOŽIŤ AKO SPOJENIE.
Potom prídeme k tabuľke LOOKUP, urobíme z nej tabuľku, dopyt z tabuľky, ULOŽIŤ AKO PRIPOJENIE - to bude práve najľahšia časť - a potom spojíme tento dopyt a tento dotaz na základe položky číslo, všetky položky z ľavej tabuľky, toto je ľavá tabuľka, ktorá sa zhoduje z pravej strany, nahraďte nuly číslom položky. Stále sme vo vzduchu a hľadáme, čo chceme robiť, keď sa niečo z nejakého dôvodu nenájde. Túto otázku som položil, ale osoba, ktorá súbor odoslala, neodpovedá, takže ju iba nahradím číslom položky. Dúfajme, že správnou vecou je pridať do položky LOOKUPTABLE viac položiek, aby sa nenašli žiadne nenájdené položky, ale sme tu a potom ich zoradíme podľa indexov INDEX1 a INDEX2, takže tak,veci sú späť v správnom poradí a potom to bola časť, ktorú som nedokázal prísť na to, ako to urobiť.
Zoskupíme sa tak, že INDEX1 urobíme ekvivalent TEXTJOIN alebo ConcatenateX so znakom 10 ako oddeľovačom, ako agregátorom, a samozrejme, toto je tá časť, ktorá je tou najťažšou časťou, ale je to tá časť, ktorá je tu skutočne nová tejto sady krokov. Takže ak rozumiete tomu, čo TEXTJOIN robí alebo si môže predstaviť, čo by urobil ConcatenateX, robíme to v podstate pomocou tohto druhu kroku. Takže, dobre. Poďme to teda vyskúšať.
Takže začneme tu. Tu sú naše pôvodné údaje, má nadpis. Takže idem na FORMÁTOVAŤ AKO TABUĽKU, CONTROL + T, MOJA TABUĽKA MÁ Záhlavie, áno, a potom použijeme Power Query. Teraz používam Excel 2016 Office 365, takže je to tu v ľavej časti karty DATA. Ak ste práve v priamom excele 2016, nie v Office 365, je v strede - ZÍSKAJTE A PREMENTE. Ak ste v programe Excel 2010 alebo 2013, bude to tu jeho vlastná karta s názvom Power Query. Ak túto kartu nemáte, budete si ju musieť stiahnuť. Ak používate počítač Mac alebo Android alebo niektorú z ďalších falošných verzií Excelu, je nám ľúto, pre vás nie je k dispozícii žiadny Power Query. Získajte verziu programu Windows pre Windows a vyskúšajte ju.
V poriadku, urobíme Power Query Z TABUĽKY, v poriadku, a prvá vec, ktorú urobím, je PRIDANIE INDEXOVÉHO STĹPCA a začnem OD 1. V poriadku , takže toto je v podstate objednávka 1, objednávka 2, objednávka 3, objednávka 4. Potom zvolíme tento stĺpec a na karte PREMENU prejdeme na SPLIT COLUMN, BY DELIMITER, a oni boli schopní zistiť, že ide o LineFeed je oddeľovač. Páči sa mi, že to zisťuje Power Query. Prečo teraz Excel, text do stĺpcov, jo, text do stĺpcov nezistí, čo je to oddeľovač? A každý prípad sa chystáme ROZDIELAŤ NA RIADKY a POUŽIŤ ŠPECIÁLNY CHARAKTER. Dobre, takže všetko je dobré.
Teraz sledujte, čo sa tu deje. Máme 999 riadkov, ale teraz ich máme oveľa viac. Takže každá položka v danom čísle objednávky má teraz svoj vlastný riadok. Osoba, ktorá položila túto otázku, nechce, aby to bol jej vlastný riadok, ale budeme musieť urobiť, aby to bol jeho vlastný riadok, aby sme mohli urobiť spojenie. Sem pridám nový stĺpec INDEX. PRIDAŤ STĹPEC, INDEXOVÝ STĹPEC, OD 1, a teda máme … sú to v podstate poradové čísla a potom sú to poradia v poradí, pretože som určil, že neskôr budú v nejakom inom poradí. Neviem na aké poradie prechádzajú, ale sme tu.
Dobre, takže DOMOV, nie tlačidlo ZATVORIŤ a ZATVORIŤ, ale rozbaľovacia ponuka ZATVORIŤ a ZATIAŽIŤ a ZATVORIŤ a NAČÍTAŤ. Neviem, prečo trvá 10 sekúnd, kým sa im toto dialógové okno zobrazí prvýkrát. Ideme IBA VYTVORIŤ SPOJENIE. Kliknite na tlačidlo OK. Nádhera. Takže to je TABUĽKA1, TABUĽKA1.
Teraz pôjdeme do nášho VYHĽADÁVATEĽA. LOOKUPTABLE bude ľahko spracovateľný. Toto naformátujeme ako tabuľku. CONTROL + T. Kliknite na tlačidlo OK. DATA alebo POWER QUERY, ak máte starú verziu, Z TABUĽKY. Toto sa bude volať TABUĽKA2. Hovorme tomu LOOKUPTABLE. Perfektné. ZATVORIŤ A ZAŤAŽIŤ, ZATVORIŤ A ZAŤAŽIŤ, VYTVORIŤ IBA SPOJENIE.
Dobre. Teraz tu máme svoje dva bity a chcem ich zlúčiť. Takže ideme na nové miesto a potom DATA, ZÍSKAJTE DATA, KOMBINUJTE DOTAZY, urobíme ZLÚČENIE a tabuľka vľavo bude TABUĽKA 1 - to sú naše pôvodné údaje - - a my použijeme toto číslo POLOŽKY a vezmeme si to až za LOOKUPTABLE a toto číslo POLOŽKY. Je to naozaj neintuitívne, keď v obidvoch prípadoch musíte kliknúť na POLOŽKY, aby ste definovali, aký je kľúč, a VONKAJŠIE pripojenie, VŠETKO PRVÉ, ZHODUJÚCE OD DRUHÉHO, a, pozri, 40% z nich chýba v VYHĽADÁVACIA TABUĽKA. Toto sú všetko falošné údaje, ale pôvodné údaje tiež chýbali 40% v LOOKUPTABLE. Naozaj trochu frustrujúce. Dobre. Takže, tu je číslo našej POLOŽKY, naše 2 polia INDEXU a potom tu náš LOOKUPTABLE. JaChystám sa to ROZŠÍRIŤ a požiadať o POPIS. Dobre, vidíte, že tu máme kopu nulových hodnôt.
Dobre, takže urobíme podmienený stĺpec. Podmienený stĺpec bude hovoriť, pozrite sa na tento stĺpec. Ak je = null, potom preveďte túto hodnotu, inak použite hodnotu, ktorá je v danom stĺpci. Takže tu v časti PRIDAŤ STĹPEC urobíme PODMIENKOVÝ STĹPEC - pekné malé používateľské rozhranie, ktoré nás prevedie týmto - ak je VYHĽADÁVANIE ROVNAK ROVNÉ NULL, potom tu chceme použiť STĹPEC POLOŽIEK, inak chceme použiť STĹPEC s názvom LOOKUPDESCRIPTION, v poriadku. Kliknite na OK a sme tu. K dispozícii je náš stĺpec VLASTNÉ, ktorý obsahuje buď novú hodnotu z vyhľadávacieho programu LOOKUPTABLE, alebo pôvodnú hodnotu, ak sa nenájde. V tomto okamihu môžeme kliknúť pravým tlačidlom myši a povedať, že chceme ODSTRÁNIŤ tento stĺpec. Bol to dočasný stĺp, bol to pomocný stĺp. Teraz, keď máme to, čo potrebujeme, už tento stĺpec nepotrebujeme a vlastne v tejto chvíliAni tento stĺpec už nepotrebujem. Takže môžem kliknúť pravým tlačidlom myši a ODSTRÁNIŤ tento stĺpec. Dobre. Teraz tu máme naše údaje. Chcem to zoradiť podľa pôvodného INDEXU. Teda Triedenie Zostupne. Tým sa naše dáta dostanú do správnej postupnosti a teraz, keď sú zoradené, môžem v skutočnosti kliknúť pravým tlačidlom myši a ODSTRÁNIŤ tento stĺpec.
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, toto je bod, v ktorom ťa zvyčajne žiadam, aby si si kúpil moju knihu, ale dnes ťa radšej požiadajme, aby si si kúpil Miguelovu knihu. Miguel Escobar a Ken Puls napísali túto vynikajúcu knihu o M Is For (DATA) MONKEY - najlepšej knihe o Power Query. Choďte to skontrolovať.
Dobre, zhrňte sa: dnes je to naozaj dlhá epizóda; máme prehliadač, sťahuje dáta zo systému, kde je každá položka oddelená klávesovou skratkou ALT + ENTER a snažíme sa urobiť VLOOKUP pre každú jednotlivú položku; dnes sme vytvorili riešenie pomocou Power Query vrátane nástroja na štruktúrované stĺpce extraktu ako; ale to funguje iba na zozname, nie na tabuľke, takže som musel na prevedenie tabuľky na zoznam použiť funkciu TABLE.COLUMN.
No hej. Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2151.xlsm
