CONCATENATEX i Power Query. Den nya TEXTJOIN-funktionen är fantastisk. Kan du göra samma sak med Power Query? Ja. Nu kan du.
Kolla på video
- En tittare laddar ner data från ett system där varje objekt separeras av Alt + Enter
- Bill: Varför gör du det här? Betraktare: Det är så jag ärver data. Jag vill hålla det så.
- Bill: Vad vill du göra med de 40% av värdena som inte finns i tabellen? Betraktare: Inget svar
- Bill: Det finns ett komplicerat sätt att lösa detta om du har de senaste Power Query-verktygen.
- Istället en VBA-makro för att lösa det - makrot ska fungera helt tillbaka till Excel 2007
- Istället för att göra VLOOKUP, gör en serie Sök och ersätt med VBA
Videoutskrift
Lär dig Excel från, Podcast avsnitt 2151.
Jag vet verkligen inte vad jag ska kalla den här. Om jag försöker attrahera de människor som använder DAX, skulle jag säga ConcatenateX i Power Query, eller bara de som använder vanlig Excel men Office 365, jag skulle säga TEXTJOIN i Power Query, eller, för att vara helt ärlig, det är en superkomplex uppsättning steg i Power Query för att möjliggöra en supergalen lösning i Excel.
Hallå. Välkommen tillbaka till netcast. Jag heter Bill Jelen. Tja, igår i avsnitt 2150 beskrev jag problemet. Någon skickade in den här filen där deras system laddar ner de artiklar som är en beställning med linjemätningar mellan dem. Med andra ord, ALT + ENTER, och se, WRAP TEXT är påslagen, och de vill göra en VLOOKUP i denna LOOKUPTABLE för vart och ett av dessa objekt. Jag är som, vad? Varför gör du detta? Men jag täckte det igår. Låt oss bara försöka lista ut hur man gör det.
Jag sa faktiskt, ja, Power Query skulle vara det bästa sättet att göra detta men jag blev snubblad på hur man gör den sista delen. Jag sa, är det okej om varje artikel hamnar på sin egen rad? Nej, de måste vara tillbaka i den här originalsekvensen. Jag är som, det är hemskt, men på mitt Twitter-flöde just förra veckan, Tim Rodman, den 27 september: "Äntligen läser jag den här boken," - jag antar att det är PowerPivot Alchemy - "och har redan fått sin ConcatenateX-önskan. ” Jag var en smartass när jag gjorde det här och frågade efter PERHAPS ROMANX, men jag ville nog verkligen ha ConcatenateX, så Tim gav mig ett försprång att jag nu kan göra det i Power BI.
Så jag gick ut till mina vänner, Rob Collie på Power Pivot Pro och Miguel Escobar, och du vet, de är båda författare till fantastiska böcker. Jag har båda dessa böcker, men den här funktionen är för ny, inte i någon av böckerna. Jag sa, hej, vet ni hur man gör det här? Och Miguel vinner priset eftersom Miguel var uppe tidigt i morse eller sent i går kväll - jag är inte säker på vilken - och skickade in koden.
Okej, så här är planen i Power Query och den här är så komplicerad. Jag skriver aldrig ut en plan i Power Query. Jag gör bara hela sakerna. Jag ska börja med originaldata, lägga till en INDEX-kolumn så att vi kan hålla artiklarna från en beställning tillsammans, SPLIT COLUMN to ROWS med hjälp av en LINEFEED. Det här är andra eller tredje gången på podcasten som jag använder den här nya funktionen. Hur coolt är inte det. Jag hade en andra INDEX-kolumn så att vi kan sortera objekt i originalsekvensen och sedan SPARA SOM EN ANSLUTNING.
Sedan ska vi komma till LOOKUP-tabellen, göra det till en tabell, fråga från tabell, SPARA SOM ANSLUTNING - det skulle bli den enklaste delen där - och sedan slå samman denna fråga och denna fråga baserat på artikel nummer, alla objekt från vänster tabell, detta är den vänstra tabellen, matchande från höger, ersätt nollar med artikelnumret. Vi är fortfarande uppe i luften på vad vi vill göra när något inte hittas av någon anledning. Jag har ställt den här frågan, men personen som skickade in filen svarar inte, så jag ska bara ersätta den med artikelnumret. Förhoppningsvis är det rätta att lägga till fler artiklar i LOOKUPTABLE så att det inte finns några som inte hittas, men här är vi, och sedan ska vi sortera efter INDEX1 och INDEX2, så på det sättet,saker är tillbaka i rätt ordning och då var det den delen som jag inte kunde ta reda på hur jag skulle göra.
Vi kommer att gruppera efter INDEX1 och göra motsvarigheten till en TEXTJOIN eller ConcatenateX med tecknet 10 som separator, som aggregat, och det är naturligtvis den del som är den svåra delen men det är den del som verkligen är ny här i denna uppsättning steg. Så om du förstår vad TEXTJOIN gör eller kan konceptualisera vad ConcatenateX skulle ha gjort, gör vi i huvudsak det med den här typen av steg. Så, okej. Så, låt oss ge det en chans.
Så vi ska börja här. Här är vår originaldata, har en rubrik. Så jag ska FORMATERA SOM TABELL, CONTROL + T, MITT TABELL HAR HEADERS, ja, och sedan ska vi använda Power Query. Nu är jag i Excel 2016 Office 365, så det är här till vänster på fliken DATA. Om du bara är i rakt Excel 2016, inte Office 365, är det i mitten - GET & TRANSFORM. Om du är i Excel 2010 eller 2013 kommer det att bli en egen flik här som heter Power Query, och om du inte har den fliken måste du ladda ner den fliken. Om du använder en Mac eller Android eller någon av de andra falska versionerna av Excel, tyvärr, ingen Power Query för dig. Skaffa en Windows-version av Excel och prova det.
Okej, så vi ska göra en Power Query FRÅN EN TABELL, okej, och det första jag ska göra är att jag lägger till en indexkolumn och jag ska börja från 1. Okej , så detta är i huvudsak ordning 1, ordning 2, ordning 3, ordning 4. Sedan ska vi välja den här kolumnen och på fliken TRANSFORM kommer vi att SPLITTA KOLONN, AV DELIMITER, och de kunde upptäcka att det är en LineFeed är avgränsaren. Jag älskar att Power Query upptäcker detta. Nu, varför räknar inte Excel, text till kolumner, ja, text till kolumner vad avgränsaren är? Och varje händelse kommer vi att SPLITTA I RADER och ANVÄNDA SPECIALKARAKTER. Okej, så allt är bra.
Titta nu på vad som händer här. Vi har 999 rader men nu har vi mycket mer än så. Så varje artikel i det ordernumret är nu sin egen rad. Nu vill den som ställde den här frågan inte att det ska vara en egen rad, men vi måste göra det till en egen rad så att vi kan gå med. Jag ska lägga till en ny INDEX-kolumn här. LÄGG TILL KOLONN, INDEXKOLONN, FRÅN 1, och så har vi … det här är i huvudsak ordernumren och då är det sekvensen i ordningen eftersom jag har bestämt att dessa senare kommer att vara i någon annan ordning. Jag vet inte vilken ordning de byter till men här är vi.
Okej, så, HEM, inte knappen STÄNG & LADD utan rullgardinsmenyn STÄNG & LADD och STÄNG & LADA TILL. Jag vet inte varför det tar 10 sekunder för dem att visa den här dialogrutan första gången. Vi ska ENDAST SKAPA ANSLUTNING. Klicka på OK. Skön. Så det är TABELL1, TABELL1.
Nu ska vi gå till vår LOOKUPTABLE. LOOKUPTABLE kommer att bli lätt att bearbeta. Vi ska formatera detta som en tabell. CONTROL + T. Klicka på OK. DATA eller POWER QUERY om du är i en gammal version, FRÅN TABELL. Detta kommer att kallas TABELL2. Låt oss kalla det LOOKUPTABLE. Perfekt. STÄNG & LADDA, STÄNG & LADDA TILL, SKAPA ENDAST ANSLUTNING.
OK. Nu har vi våra två bitar här och jag vill slå samman dessa två. Så vi ska bara gå till en ny plats och sedan DATA, FÅ DATA, KOMBINERA FRÅGOR, vi ska göra en FUSION, och tabellen till vänster kommer att vara TABELL1 - det är vår ursprungliga data - - och vi kommer att använda det här artikelnumret och vi ska gifta oss med det LOOKUPTABLE och det artikelnumret. Det är verkligen icke-intuitivt där du måste klicka på PUNKTERNA i båda fallen för att definiera vad nyckeln är, och en OUTER-koppling, ALL FROM FIRST, MATCHING FROM SECOND, och se, det finns 40% av dessa som saknas UPPSLAGSTABELL. Allt detta är falska data men de ursprungliga uppgifterna saknades 40% från LOOKUPTABLE också. Verkligen frustrerande. OK. Så här är vårt ITEM-nummer, våra 2 INDEX-fält och sedan vår LOOKUPTABLE här. Jag 'm ska utvidga det och be om BESKRIVNING. Okej, du ser att vi har en massa nollor här.
Okej, så vi ska göra en villkorlig kolumn. Villkorlig kolumn kommer att säga titta på den här kolumnen. Om det är = till null, ta sedan med det här värdet, annars använder du värdet i kolumnen. Så här, under ADD COLUMN, gör vi CONDITIONAL COLUMN - trevligt litet användargränssnitt som kommer att gå igenom detta - om LOOKUPTABLEDESCRIPTION EQUALS NULL, så vill vi använda en KOLONN här av PUNKTER, annars vill vi använda KOLONNEN heter LOOKUPDESCRIPTION, okej. Klicka på OK, och där är vi. Det finns vår CUSTOM-kolumn med antingen det nya värdet från LOOKUPTABLE eller det ursprungliga värdet om det inte hittas. Vid den här tiden kan vi högerklicka och säga att vi vill ta bort den här kolumnen. Det var en tillfällig kolumn, det var en hjälparpelare. Nu när vi har vad vi behöver behöver vi inte den kolumnen längre, och faktiskt, vid denna tidpunkt,Jag behöver inte heller den här kolumnen längre. Så jag kan högerklicka och ta bort den kolumnen. OK. Nu har vi våra uppgifter här. Jag vill sortera det efter original INDEX. Så, Sortera stigande. Det får våra data i rätt ordning, och nu när de är sorterade kan jag faktiskt högerklicka och ta bort den kolumnen.
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, hej, det här är den punkt där jag vanligtvis ber dig att köpa min bok, men låt oss istället be dig att köpa Miguels bok. Miguel Escobar och Ken Puls skrev den här utmärkta boken om M Is For (DATA) MONKEY - den bästa boken som finns på Power Query. Gå och kolla in det.
Okej, avsluta: idag är ett riktigt långt avsnitt; vi har en tittare, laddar ner data från ett system där varje artikel är åtskild av ALT + ENTER och vi försöker göra en VLOOKUP för varje enskild artikel; byggde en lösning idag med Power Query inklusive det strukturerade kolumnverktyget för extrakt som; men det fungerar bara på en lista, inte en tabell, så jag var tvungen att använda TABLE.COLUMN-funktionen för att konvertera tabellen till en lista.
Men Hej. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2151.xlsm
