CONCATENATEX in Power Query. De nieuwe TEXTJOIN-functie is geweldig. Kunt u hetzelfde doen met Power Query? Ja. Nu kan je.
Bekijk video
- Een kijker downloadt gegevens van een systeem waarbij elk item wordt gescheiden door Alt + Enter
- Bill: Waarom doe je dit? Kijker: zo erf ik de gegevens. Ik wil het zo houden.
- Bill: Wat wil je doen met de 40% waarden die niet in de tabel staan? Kijker: geen antwoord
- Bill: er is een gecompliceerde manier om dit op te lossen als u over de nieuwste Power Query-tools beschikt.
- In plaats daarvan een VBA-macro om het op te lossen - de macro zou helemaal terug moeten werken naar Excel 2007
- In plaats van VERT.ZOEKEN, voert u een reeks Zoeken en vervangen uit met VBA
Videotranscriptie
Leer Excel van, Podcast Episode 2151.
Ik weet echt niet hoe ik deze moet noemen. Als ik probeer de mensen aan te trekken die DAX gebruiken, zou ik ConcatenateX in Power Query zeggen, of alleen de mensen die normaal Excel gebruiken, maar Office 365, zou ik TEXTJOIN zeggen in Power Query, of, om helemaal eerlijk te zijn, het is een supercomplexe reeks stappen in Power Query om een supergekke oplossing in Excel mogelijk te maken.
Hallo. Welkom terug bij de netcast. Ik ben Bill Jelen. Nou, gisteren in aflevering 2150 beschreef ik het probleem. Iemand heeft dit bestand verzonden waar hun systeem de items downloadt die een bestelling zijn met lijnfeeds ertussen. Met andere woorden, ALT + ENTER, en zie, WRAP TEXT is ingeschakeld en ze willen voor elk van die items een VERT.ZOEKEN doen naar deze LOOKUPTABLE. Ik ben zo van, wat? Waarom doe je dit? Maar dat heb ik gisteren besproken. Laten we gewoon proberen uit te vinden hoe we dit moeten doen.
Ik zei eigenlijk, nou, Power Query zou de beste manier zijn om dit te doen, maar ik raakte stomverbaasd over hoe ik het laatste deel moest doen. Ik zei: is het oké als elk item op zijn eigen rij terechtkomt? Nee, ze moeten terug zijn in deze oorspronkelijke volgorde. Ik heb zoiets van, dat is vreselijk, maar vorige week, op mijn Twitter-feed, Tim Rodman, 27 september: "Eindelijk dit boek gelezen," - ik vermoed dat het PowerPivot Alchemy is - "en heb al zijn ConcatenateX-wens gekregen. " Ik was een slimmerik toen ik dit deed en vroeg om MISSCHIEN ROMANX, maar ik wilde waarschijnlijk echt ConcatenateX, en dus gaf Tim me een waarschuwing dat ik dat nu kan doen in Power BI.
Dus ging ik uit naar mijn vrienden, Rob Collie van Power Pivot Pro en Miguel Escobar, en, weet je, ze zijn allebei auteurs van geweldige boeken. Ik heb beide boeken, maar deze functie is te nieuw, niet in beide boeken. Ik zei, hé, weten jullie hoe je dit moet doen? En Miguel wint de prijs omdat Miguel vanmorgen vroeg op was of gisteravond laat - ik weet niet zeker welke - en de code opstuurde.
Oké, hier is het plan in Power Query en dit is zo ingewikkeld. Ik schrijf nooit een plan in Power Query. Ik ga gewoon de hele dingen doen. Ik begin met de originele gegevens, voeg een INDEX-kolom toe zodat we de items van een bestelling bij elkaar kunnen houden, SPLIT KOLOM naar RIJEN met een LINEFEED. Dit is de tweede of derde keer op de podcast dat ik deze nieuwe functie heb gebruikt. Hoe cool is dat. Ik had een tweede INDEX-kolom, zodat we items in de oorspronkelijke volgorde kunnen sorteren en vervolgens OPSLAAN ALS VERBINDING.
Dan gaan we naar de LOOKUP-tabel, er een tabel van maken, een query uit de tabel, SAVE AS CONNECTION - dat zou het gemakkelijkste deel zijn - en dan deze query en deze query samenvoegen op basis van item nummer, alle items van de linkertabel, dit is de linkertabel, komt overeen met rechts, vervang nulls door het itemnummer. We zijn nog steeds in de lucht over wat we willen doen als iets om de een of andere reden niet wordt gevonden. Ik heb deze vraag gesteld, maar de persoon die het bestand heeft ingezonden, antwoordt niet, dus ik ga het vervangen door het artikelnummer. Hopelijk is het juiste om te doen meer items aan de LOOKUPTABLE toe te voegen, zodat er geen items niet worden gevonden, maar hier zijn we dan, en dan gaan we sorteren op INDEX1 en INDEX2, dus op die manier,dingen zijn weer in de goede volgorde en toen was dit het deel waarvan ik niet wist hoe ik het moest doen.
We gaan groeperen op INDEX1 en doen het equivalent van een TEXTJOIN of ConcatenateX met het teken 10 als scheidingsteken, als aggregator, en dit is natuurlijk het moeilijkste deel, maar het is het deel dat hier echt nieuw is deze reeks stappen. Dus als u begrijpt wat TEXTJOIN doet of kunt bedenken wat ConcatenateX zou hebben gedaan, doen we dat in wezen met behulp van dit soort stap. Dus oké. Dus laten we het proberen.
Dus we gaan hier beginnen. Hier zijn onze originele gegevens, heeft een titel. Dus ik ga FORMATEN ALS TABEL, CONTROL + T, MIJN TABEL HEAD HEADERS, ja, en dan gaan we Power Query gebruiken. Nu zit ik in Excel 2016 Office 365, dus het staat hier aan de linkerkant van het tabblad GEGEVENS. Als u zich alleen in Excel 2016 bevindt, niet in Office 365, zit het in het midden - OPHALEN EN TRANSFORMEREN. Als u zich in Excel 2010 of 2013 bevindt, wordt het een eigen tabblad hier genaamd Power Query, en als u dat tabblad niet hebt, moet u dat tabblad downloaden. Als u een Mac of Android of een van de andere nepversies van Excel gebruikt, sorry, geen Power Query voor u. Download een Windows-versie van Excel en probeer dit eens.
Oké, dus we gaan een Power Query doen VANUIT EEN TABEL, oké, en het eerste wat ik ga doen is dat ik een INDEXKOLOM TOEVOEGT en ik begin VANAF 1. Oké , dus dit is in wezen order 1, order 2, order 3, order 4. Vervolgens gaan we deze kolom kiezen en, op het tabblad TRANSFORM, gaan we naar SPLIT COLUMN, BY DELIMITER, en ze waren in staat om detecteren dat het een LineFeed is, is het scheidingsteken. Ik vind het geweldig dat Power Query dit detecteert. Waarom komt Excel, tekst naar kolommen, ja, tekst naar kolommen er niet achter wat het scheidingsteken is? En elke keer dat we voorkomen, gaan we SPLITSEN IN RIJEN, en SPECIAAL KARAKTER GEBRUIKEN. Oké, dus dat is allemaal goed.
Kijk nu wat er hier gebeurt. We hebben 999 rijen, maar nu hebben we veel meer dan dat. Dus elk item in dat ordernummer is nu een eigen rij. Nu, de persoon die deze vraag stelde, wil niet dat het zijn eigen rij is, maar we zullen het zijn eigen rij moeten maken, zodat we de join kunnen doen. Ik ga hier een nieuwe INDEX-kolom toevoegen. VOEG KOLOM TOE, INDEX KOLOM, VANAF 1, en dus hebben we… dit zijn in wezen de volgnummers en dan zijn dit de volgorde binnen de volgorde, omdat ik heb vastgesteld dat deze later in een andere volgorde zullen zijn. Ik weet niet naar welke volgorde ze overschakelen, maar hier zijn we dan.
Oké, dus THUIS, niet de CLOSE & LOAD-knop maar de CLOSE & LOAD drop-down, en CLOSE & LOAD TO. Ik weet niet waarom het 10 seconden duurt voordat ze dit dialoogvenster de eerste keer weergeven. We gaan ALLEEN VERBINDING MAKEN. Klik OK. Mooi. Dus dat is TABLE1, TABLE1.
Nu gaan we naar onze LOOKUPTABLE. LOOKUPTABLE wordt gemakkelijk te verwerken. We gaan dit opmaken als een tabel. CONTROL + T. Klik OK. DATA, of POWER QUERY als je in een oude versie bent, FROM TABLE. Dit gaat TABLE2 heten. Laten we het LOOKUPTABLE noemen. Perfect. SLUITEN EN LADEN, SLUITEN EN LADEN NAAR, MAAK ALLEEN VERBINDING.
Oké. Nu hebben we onze twee stukjes hier en ik wil die twee samenvoegen. Dus we gaan gewoon naar een nieuwe plek en dan DATA, GET DATA, COMBINE QUERIES, we gaan een SAMENVOEGING doen, en de tabel aan de linkerkant wordt TABLE1 - dat zijn onze originele data - - en we gaan dit ITEM-nummer gebruiken en we gaan daarmee trouwen tot de LOOKUPTABLE en dat ITEM-nummer. Het is echt niet-intuïtief daar je in beide gevallen op de ITEMS moet klikken om te definiëren wat de sleutel is, en een BUITENSTE join, ALLES VAN EERSTE, AANPASSEND VANAF TWEEDE, en zie, er ontbreken 40% hiervan in de OPZOEKTABEL. Dit zijn allemaal nepgegevens, maar de originele gegevens ontbraken ook 40% in de LOOKUPTABLE. Echt frustrerend. Oké. Hier is ons ITEM-nummer, onze 2 INDEX-velden en dan hier onze OPZOEKBARE. IK'Ik ga dat UITBREIDEN en om de BESCHRIJVING vragen. Oké, we hebben hier een heleboel nullen.
Alright, so, we're going to do a conditional column. Conditional column’s going to say look at this column. If it's = to null, then bring this value over, otherwise, use the value that's in that column. So here, under ADD COLUMN, we’ll do CONDITIONAL COLUMN -- nice little UI that'll walk us through this -- if the LOOKUPTABLEDESCRIPTION EQUALS NULL, then we want to use a COLUMN here of ITEMS, otherwise, we want to use the COLUMN called LOOKUPDESCRIPTION, alright. Click OK, and there we are. There's our CUSTOM column with either the new value from the LOOKUPTABLE or the original value if it's not found. At this point, we can right click and say that we want to REMOVE this column. It was a temporary column, it was a helper column. Now that we have what we need, we don't need that column anymore, and actually, at this point, I don't need this column anymore either. So, I can right click and REMOVE that column. Alright. Now, we have our data here. I want to sort it by the original INDEX. So, SORT ASCENDING. That gets our data into the right sequence, and now that it's sorted, I can actually right click and REMOVE that column.
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.
Nu, hey, dit is het punt waarop ik je meestal vraag om mijn boek te gaan kopen, maar laten we je vandaag in plaats daarvan vragen Miguels boek te gaan kopen. Miguel Escobar en Ken Puls schreven dit uitstekende boek over M Is For (DATA) MONKEY - het beste boek dat er is over Power Query. Ga dat eens kijken.
Oké, afsluiting: vandaag is een heel lange aflevering; we hebben een viewer, downloaden gegevens van een systeem waarin elk item wordt gescheiden door ALT + ENTER en we proberen een VERT.ZOEKEN uit te voeren voor elk afzonderlijk item; heeft vandaag een oplossing gebouwd met Power Query inclusief de gestructureerde kolomtool van extract as; maar dat werkt alleen op een lijst, niet op een tabel, dus ik moest de functie TABLE.COLUMN gebruiken om de tabel naar een lijst te converteren.
Hallo daar. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2151.xlsm
