CONCATENATEX v Power Query. Nová funkce TEXTJOIN je úžasná. Můžete dělat to samé s Power Query? Ano. Teď můžeš.
Sledovat video
- Prohlížeč stáhne data ze systému, kde je každá položka oddělena klávesami Alt + Enter
- Bill: Proč to děláš? Prohlížeč: Tímto způsobem zdědím data. Chci to tak udržet.
- Bill: Co chcete dělat se 40% hodnot, které nejsou v tabulce? Prohlížeč: Žádná odpověď
- Bill: Existuje složitý způsob, jak to vyřešit, pokud máte nejnovější nástroje Power Query.
- Místo toho makro VBA k jeho vyřešení - makro by mělo fungovat až do aplikace Excel 2007
- Místo VLOOKUPU proveďte řadu Najít a nahradit VBA
Přepis videa
Naučte se Excel z Podcastu, epizoda 2151.
Opravdu nevím, jak tomu říkat. Pokud se snažím přilákat lidi, kteří používají DAX, řekl bych ConcatenateX v Power Query, nebo jen lidi, kteří používají běžný Excel, ale Office 365, řekl bych TEXTJOIN v Power Query, nebo, abych byl upřímný, je to superkomplexní sada kroků v Power Query umožňující superbláznivé řešení v aplikaci Excel.
Ahoj. Vítejte zpět na netcastu. Jsem Bill Jelen. Včera v epizodě 2150 jsem popsal problém. Někdo poslal tento soubor, kde jeho systém stahuje položky, které jsou objednávkou, s řádky mezi nimi. Jinými slovy, ALT + ENTER a uvidíte, WRAP TEXT je zapnutý a chtějí pro každou z těchto položek provést VLOOKUP do tohoto LOOKUPTABLE. Jsem jako, co? Proč to děláš? Ale včera jsem to zakryl. Zkusme to přijít na to, jak to udělat.
Vlastně jsem řekl, no, Power Query by byl nejlepší způsob, jak to udělat, ale dostal jsem pauzu, jak udělat poslední část. Řekl jsem, je to v pořádku, když každá položka skončí ve své vlastní řadě? Ne, musí být zpět v této původní posloupnosti. Jsem rád, to je hrozné, ale na mém twitterovém kanálu z minulého týdne Tim Rodman, 27. září: „Konečně si přečtu tuto knihu,“ - hádám, že je to PowerPivot Alchemy - „a už jsem si přál ConcatenateX. “ Když jsem to udělal, byl jsem smartass a žádal jsem o PERHAPS ROMANX, ale pravděpodobně jsem opravdu chtěl ConcatenateX, a tak mi Tim dal heads up, že to teď můžu udělat v Power BI.
Takže jsem šel ven svým přátelům, Robovi Colliemu v Power Pivot Pro a Miguelovi Escobarovi, a jak víte, oba jsou autory skvělých knih. Mám obě tyto knihy, ale tato funkce je příliš nová, ani v jedné z nich. Řekl jsem, hej, víte, jak na to? A Miguel vyhrává cenu, protože Miguel byl vzhůru dnes ráno nebo včera pozdě v noci - nejsem si jistý, který z nich - a poslal kód.
Dobře, tady je plán v Power Query a tento je tak komplikovaný. V Power Query nikdy nevypíšu plán. Prostě jdu dělat všechno. Začnu s původními daty, přidám sloupec INDEX, abychom mohli udržet položky z objednávky společně, SPLIT COLUMN to ROWS pomocí LINEFEED. Tuto novou funkci používám podcast podruhé nebo potřetí. To je skvělé. Měl jsem druhý sloupec INDEX, abychom mohli třídit položky do původní sekvence, a pak ULOŽIT JAKO PŘIPOJENÍ.
Pak přijdeme k tabulce LOOKUP, uděláme z ní tabulku, dotaz z tabulky, ULOŽIT JAKO PŘIPOJENÍ - to bude právě tam nejjednodušší část - a poté spojíme tento dotaz a tento dotaz na základě položky number, all items from the left table, this is the left table, matching from the right, replace nulls with the item number. Stále jsme ve vzduchu, co chceme dělat, když se něco z nějakého důvodu nenašlo. Zeptal jsem se na tuto otázku, ale osoba, která soubor odeslala, neodpovídá, takže ji prostě nahradím číslem položky. Doufejme, že správná věc je přidat do položky LOOKUPTABLE více položek, takže nebudou nalezeny žádné nenalezené, ale tady jsme, a potom budeme třídit podle INDEX1 a INDEX2, takže tímto způsobem,věci jsou zpět ve správném pořadí a pak to byla ta část, kterou jsem nemohl přijít na to, jak to udělat.
Seskupíme se tak, že INDEX1 provede ekvivalent TEXTJOIN nebo ConcatenateX se znakem 10 jako oddělovačem, jako agregátorem, a samozřejmě je to ta část, která je těžkou částí, ale je to část, která je zde opravdu nová tuto sadu kroků. Takže pokud rozumíte tomu, co TEXTJOIN dělá nebo může pojímat, co by udělal ConcatenateX, děláme to v podstatě pomocí tohoto druhu kroku. Takže, dobře. Pojďme to tedy vyzkoušet.
Takže začneme tady. Tady jsou naše původní data, má záhlaví. Takže se chystám FORMÁTOVAT JAKO TABULKU, CONTROL + T, MÁ TABULKA MÁ Záhlaví, ano, a pak použijeme Power Query. Teď jsem v Excel 2016 Office 365, takže je to tady v levé části karty DATA. Pokud jste právě v přímém Excelu 2016, ne v Office 365, je uprostřed - ZÍSKEJTE A PŘEMĚNĚTE. Pokud jste v aplikaci Excel 2010 nebo 2013, bude to zde vlastní karta s názvem Power Query a pokud tuto kartu nemáte, budete si ji muset stáhnout. Pokud používáte Mac nebo Android nebo jinou falešnou verzi Excelu, omlouvám se, žádný Power Query pro vás není. Získejte verzi Excel pro Windows a zkuste to.
Dobře, takže uděláme Power Query Z TABULKY, dobře, a první věc, kterou udělám, je PŘIDAT Sloupec INDEXU a začnu OD 1. Dobře , takže toto je v zásadě objednávka 1, objednávka 2, objednávka 3, objednávka 4. Pak zvolíme tento sloupec a na kartě PŘEVOD přejdeme na SPLIT COLUMN, BY DELIMITER, a oni byli schopni zjistit, že se jedná o oddělovač LineFeed. Líbí se mi, že to Power Query detekuje. Proč tedy Excel, text do sloupců, jo, text do sloupců nezjistí, co je to oddělovač? A každý výskyt se ROZDĚLÍME DO ŘÁDŮ a POUŽÍVÁME ZVLÁŠTNÍ CHARAKTER. Dobře, takže všechno je dobré.
Nyní sledujte, co se tady děje. Máme 999 řádků, ale nyní jich máme mnohem víc. Takže každá položka v tomto čísle objednávky má nyní svůj vlastní řádek. Osoba, která položila tuto otázku, nechce, aby to byla jeho vlastní řada, ale budeme ji muset udělat jako její vlastní řadu, abychom mohli provést spojení. Zde přidám nový sloupec INDEX. PŘIDAT COLUMN, INDEX COLUMN, OD 1, a tak máme … to jsou v zásadě čísla objednávek a pak jsou to pořadí v pořadí, protože jsem určil, že později budou v nějakém jiném pořadí. Nevím, na jaké pořadí přecházejí, ale jsme tady.
Dobře, takže HOME, ne tlačítko ZAVŘÍT a načíst, ale rozevírací nabídka ZAVŘÍT a načíst, a ZAVŘÍT a načíst. Nevím, proč to trvá 10 sekund, než se toto dialogové okno zobrazí poprvé. Jdeme POUZE VYTVOŘIT PŘIPOJENÍ. Klikněte na OK. Krásná. Takže to je TABULKA1, TABULKA1.
Teď půjdeme k našemu VYHLEDÁVACÍU. LOOKUPTABLE bude snadné zpracovat. Budeme to formátovat jako tabulku. CONTROL + T. Klikněte na OK. DATA nebo POWER QUERY, pokud máte starou verzi, Z TABULKY. Toto se bude jmenovat TABULKA2. Říkejme tomu LOOKUPTABLE. Perfektní. ZAVŘÍT A ZATÍŽIT, ZAVŘÍT A ZATÍŽIT, POUZE VYTVOŘIT PŘIPOJENÍ.
V pořádku. Nyní máme naše dva bity tady a já chci ty dva spojit. Jdeme tedy na nové místo a pak DATA, ZÍSKAT DATA, KOMBINOVAT DOTAZY, uděláme SLOUČENÍ a tabulka vlevo bude TABULKA1 - to jsou naše původní data - - a budeme používat toto číslo POLOŽKY a vezmeme si to až do LOOKUPTABLE a tohoto čísla POLOŽKY. Je to opravdu neintuitivní, tam musíte kliknout na POLOŽKY v obou případech, abyste definovali, jaký je klíč, a VNĚJŠÍ připojení, VŠECHNY Z PRVNÍHO, MATCHING Z DRUHÉHO, a, viz, 40% z nich chybí v LOOKUPTABLE. To jsou všechna falešná data, ale původní data měla 40% chybí také v LOOKUPTABLE. Opravdu trochu frustrující. V pořádku. Tady je tedy naše číslo POLOŽKY, naše 2 pole INDEX a poté zde náš LOOKUPTABLE. Jájdu to ROZŠÍŘIT a požádat o POPIS. Dobře, vidíte, že tu máme spoustu nulových.
Dobře, takže uděláme podmíněný sloupec. Podmíněný sloupec řekne podívejte se na tento sloupec. Pokud je to = null, přeneste tuto hodnotu, jinak použijte hodnotu, která je v daném sloupci. Takže tady, pod PŘIDAT SLOUPEC, uděláme PODMÍNĚNÝ SLOUPEK - pěkné malé uživatelské rozhraní, které nás provede tímto - pokud je LOOKUPTABLEDESCRIPTION ROVNÁ NULL, pak zde chceme použít SLOUPEK POLOŽEK, jinak chceme použít SLOUPEC s názvem LOOKUPDESCRIPTION, dobře. Klikněte na OK a jsme tady. K dispozici je náš sloupec CUSTOM, který obsahuje buď novou hodnotu z LOOKUPTABLE, nebo původní hodnotu, pokud není nalezena. V tomto okamžiku můžeme kliknout pravým tlačítkem a říct, že chceme ODSTRANIT tento sloupec. Byl to dočasný sloup, byl to pomocný sloup. Nyní, když máme to, co potřebujeme, tento sloupec již nepotřebujeme a ve skutečnosti, v tomto okamžiku,Ani tento sloupec už nepotřebuji. Mohu tedy kliknout pravým tlačítkem a ODSTRANIT tento sloupec. V pořádku. Nyní tu máme naše data. Chci to seřadit podle původního INDEXU. TŘÍDIT VZDÁLENĚ. Tím se naše data dostanou do správné sekvence a teď, když jsou tříděna, mohu ve skutečnosti kliknout pravým tlačítkem a ODSTRANIT tento sloupec.
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.
Ahoj, tohle je místo, kde tě obvykle žádám, abys si koupil moji knihu, ale dnes tě místo toho požádáme, abys si koupil Miguelovu knihu. Miguel Escobar a Ken Puls napsali tuto vynikající knihu o M Is For (DATA) MONKEY - nejlepší knize o Power Query. Jděte se podívat.
Dobře, zabalte: dnes je opravdu dlouhá epizoda; máme prohlížeč, stahuje data ze systému, kde je každá položka oddělena klávesami ALT + ENTER a zkoušíme provést VLOOKUP pro každou jednotlivou položku; dnes jsme vytvořili řešení pomocí Power Query včetně nástroje pro strukturované sloupce extraktu jako; ale to funguje pouze na seznamu, ne na tabulce, takže jsem musel použít funkci TABLE.COLUMN k převodu tabulky na seznam.
No, hej. Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2151.xlsm
