CONCATENATEX i Power Query. Den nye TEXTJOIN-funksjonen er fantastisk. Kan du gjøre det samme med Power Query? Ja. Nå kan du.
Se på video
- En seer laster ned data fra et system der hvert element er skilt av Alt + Enter
- Bill: Hvorfor gjør du dette? Seer: Det er slik jeg arver dataene. Jeg vil beholde det slik.
- Bill: Hva vil du gjøre med 40% av verdiene som ikke er i tabellen? Seer: Ikke noe svar
- Bill: Det er en komplisert måte å løse dette på hvis du har de nyeste Power Query-verktøyene.
- I stedet en VBA-makro for å løse det - makroen skal fungere helt tilbake til Excel 2007
- I stedet for å gjøre VLOOKUP, gjør en serie Find & Replace med VBA
Videoutskrift
Lær Excel fra, Podcast Episode 2151.
Jeg vet egentlig ikke hva jeg skal kalle denne. Hvis jeg prøver å tiltrekke folk som bruker DAX, vil jeg si ConcatenateX i Power Query, eller bare folk som bruker vanlig Excel, men Office 365, vil jeg si TEXTJOIN i Power Query, eller for å være helt ærlig, det er et superkompleks sett med trinn i Power Query for å aktivere en super-sinnssyk løsning i Excel.
Hei. Velkommen tilbake til netcast. Jeg er Bill Jelen. Vel, i går i episode 2150 beskrev jeg problemet. Noen sendte inn denne filen der systemet deres laster ned varene som er en ordre med linjemating mellom seg. Med andre ord, ALT + ENTER, og se, WRAP TEXT er slått på, og de vil gjøre en VLOOKUP i denne LOOKUPTABLE for hvert av disse elementene. Jeg er som, hva? Hvorfor gjør du dette? Men jeg dekket det i går. La oss bare prøve å finne ut hvordan vi gjør dette.
Jeg sa faktisk, vel, Power Query ville være den beste måten å gjøre dette på, men jeg ble snublet over hvordan jeg gjorde den siste delen. Jeg sa, er det greit hvis hvert element havner på sin egen rad? Nei, de må være tilbake i denne originale sekvensen. Jeg er som, det er fryktelig, men på Twitter-feeden min forrige uke, Tim Rodman, 27. september: "Endelig å lese denne boken," - jeg antar at det er PowerPivot Alchemy - "og har allerede fått ConcatenateX-ønsket sitt. ” Jeg var en smartass da jeg gjorde dette, og ba om PERHAPS ROMANX, men jeg ville sannsynligvis virkelig ha ConcatenateX, og så ga Tim meg et forsøk på at jeg nå kan gjøre det i Power BI.
Så jeg gikk ut til vennene mine, Rob Collie hos Power Pivot Pro og Miguel Escobar, og du vet at de begge er forfattere av flotte bøker. Jeg har begge disse bøkene, men denne funksjonen er for ny, ikke i noen av bøkene. Jeg sa, hei, vet dere hvordan dere skal gjøre dette? Og Miguel vinner prisen fordi Miguel var oppe tidlig i morges eller sent i går kveld - jeg er ikke sikker på hvilken - og sendte inn koden.
Ok, så her er planen i Power Query, og denne er så komplisert. Jeg skriver aldri ut en plan i Power Query. Jeg bare gjør det hele. Jeg skal begynne med de opprinnelige dataene, legge til en INDEX-kolonne slik at vi kan holde varene fra en bestilling sammen, SPLIT KOLONNE til RADER ved hjelp av en LINEFEED. Dette er andre eller tredje gang på podcasten jeg bruker denne nye funksjonen. Hvor kult er det. Jeg hadde en annen INDEX-kolonne, slik at vi kan sortere elementer i den originale sekvensen, og deretter LAGRE SOM EN FORBINDELSE.
Så skal vi komme til LOOKUP-tabellen, lage den til en tabell, spørring fra tabell, LAGRE SOM FORBINDELSE - det skulle være den enkleste delen akkurat der - og deretter slå sammen dette spørsmålet og dette spørsmålet basert på varen nummer, alle elementene fra venstre tabell, dette er den venstre tabellen, som samsvarer fra høyre, erstatt null med varenummeret. Vi er fremdeles oppe i luften på hva vi vil gjøre når noe ikke blir funnet av en eller annen grunn. Jeg har stilt dette spørsmålet, men personen som sendte filen svarer ikke, så jeg skal bare erstatte det med varenummeret. Forhåpentligvis er den rette tingen å gjøre å legge til flere ting i LOOKUPTABLE, så det ikke er noen som ikke er funnet, men her er vi, og så skal vi sortere etter INDEX1 og INDEX2, så på den måten,ting er tilbake i riktig rekkefølge, og da var dette den delen jeg ikke kunne finne ut hvordan jeg skulle gjøre.
Vi kommer til å gruppere etter INDEX1 og gjøre ekvivalenten til en TEXTJOIN eller ConcatenateX med tegnet 10 som skilletegn, som aggregator, og selvfølgelig er dette den delen som er den vanskelige delen, men det er den delen som er virkelig ny her i dette settet med trinn. Så hvis du forstår hva TEXTJOIN gjør eller kan konseptualisere hva ConcatenateX ville ha gjort, gjør vi egentlig det ved hjelp av denne typen trinn. Så ok. Så la oss prøve.
Så vi skal begynne her. Her er de originale dataene våre, har en overskrift. Så jeg skal FORMATERE SOM TABELL, KONTROLL + T, MITT TABELL HAR HODERE, ja, og så skal vi bruke Power Query. Nå er jeg i Excel 2016 Office 365, så det er her til venstre i DATA-fanen. Hvis du bare er i rett Excel 2016, ikke Office 365, er det i midten - FÅ & TRANSFORM. Hvis du er i Excel 2010 eller 2013, vil det være sin egen fane her, kalt Power Query, og hvis du ikke har den kategorien, må du laste ned den kategorien. Hvis du bruker Mac eller Android eller noen av de andre falske versjonene av Excel, beklager, ingen Power Query for deg. Få en Windows-versjon av Excel og prøv dette.
OK, så vi skal gjøre en Power Query FRA EN TABELL, ok, og det første jeg skal gjøre er at jeg skal legge til en INDEKSKOLONN og jeg skal begynne FRA 1. OK , så dette er i hovedsak rekkefølge 1, rekkefølge 2, rekkefølge 3, rekkefølge 4. Så skal vi velge denne kolonnen, og i TRANSFORM-fanen skal vi SPLIT KOLONNE, AV DELIMITER, og de var i stand til å oppdage at det er en LineFeed er avgrenseren. Jeg elsker at Power Query oppdager dette. Nå, hvorfor finner ikke Excel, tekst til kolonner, ja, tekst til kolonner, ut hva avgrenseren er? Og hver forekomst skal vi SPLITTE I RADER, OG BRUKE SPESIELLE EGENSKAPER. Ok, så alt dette er bra.
Se nå hva som skjer her. Vi har 999 rader, men nå har vi langt mer enn det. Så hvert element i ordrenummeret er nå sin egen rad. Nå vil ikke personen som stilte dette spørsmålet at det skal være sin egen rad, men vi blir nødt til å gjøre det til sin egen rad, slik at vi kan delta. Jeg skal legge til en ny INDEX-kolonne her. LEGG TIL KOLONNE, INDEKSKOLONNE, FRA 1, og så har vi … dette er egentlig ordrenumrene, og så er dette sekvensen i rekkefølgen fordi jeg har bestemt at disse senere vil være i en annen rekkefølge. Jeg vet ikke hvilken rekkefølge de bytter til, men her er vi.
OK, så HJEM, ikke CLOSE & LOAD-knappen, men rullegardinmenyen CLOSE & LOAD, og CLOSE & LOAD TO. Jeg vet ikke hvorfor det tar 10 sekunder for dem å vise denne dialogboksen første gang. Vi skal KUN LAGE TILKOBLING. Klikk OK. Vakker. Så det er TABELL1, TABELL1.
Nå skal vi gå til LOOKUPTABLE. LOOKUPTABLE kommer til å være enkel å behandle. Vi skal formatere dette som en tabell. CONTROL + T. Klikk OK. DATA, eller POWER QUERY hvis du er i en gammel versjon, FRA TABELL. Dette kommer til å bli kalt TABLE2. La oss kalle det LOOKUPTABLE. Perfekt. Lukk & LAST, Lukk & LAST TIL, KUN LAG TILKOBLING.
Ok. Nå har vi to biter her, og jeg vil slå sammen de to. Så vi skal bare gå til et nytt sted, og deretter DATA, FÅ DATA, KOMBINERE SPØRSMÅL, vi skal gjøre en FUSION, og tabellen til venstre vil være TABELL1 - det er våre originale data - - og vi skal bruke dette VARENummeret, og vi skal gifte oss det opp til LOOKUPTABLE og det VARE-nummeret. Det er virkelig ikke-intuitivt der, du må klikke på VARENE i begge tilfeller for å definere hva nøkkelen er, og en YTRE sammenføyning, ALT FRA FØRSTE, SAMMENFØRING FRA ANDRE, og se, det er 40% av disse som mangler fra OPPSLAGSTABELL. Dette er alt falske data, men de opprinnelige dataene manglet 40% fra LOOKUPTABLE også. Virkelig frustrerende. Ok. Så, her er vårt VARENUMMER, våre 2 INDEKS-felt, og så LOOKUPTABLE her. JEG'Jeg skal utvide det og be om BESKRIVELSEN. Greit, du ser at vi har en haug med null her.
Ok, så vi skal gjøre en betinget kolonne. Betinget kolonne kommer til å si se på denne kolonnen. Hvis det er = å null, så ta denne verdien over, ellers bruk verdien som er i den kolonnen. Så her, under ADD COLUMN, vil vi gjøre CONDISIONAL COLUMN - fin liten brukergrensesnitt som vil lede oss gjennom dette - hvis LOOKUPTABLEDESCRIPTION LIKER NULL, så vil vi bruke en COLUMN her av ITEMS, ellers vil vi bruke KOLONNEN kalt LOOKUPDESCRIPTION, ok. Klikk OK, så er vi der. Det er vår CUSTOM-kolonne med enten den nye verdien fra LOOKUPTABLE eller den opprinnelige verdien hvis den ikke blir funnet. På dette punktet kan vi høyreklikke og si at vi vil FJERNE denne kolonnen. Det var en midlertidig kolonne, det var en hjelpekolonne. Nå som vi har det vi trenger, trenger vi ikke den kolonnen lenger, og faktisk, på dette punktet,Jeg trenger ikke denne kolonnen lenger. Så jeg kan høyreklikke og FJERNE den kolonnen. Ok. Nå har vi dataene våre her. Jeg vil sortere den etter den opprinnelige INDEX. Så, SORT stigende. Det får dataene våre i riktig rekkefølge, og nå som de er sortert, kan jeg faktisk høyreklikke og FJERNE den kolonnen.
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.
Nå, hei, dette er punktet der jeg vanligvis ber deg om å kjøpe boka mi, men i dag, la oss i stedet be deg om å kjøpe Miguels bok. Miguel Escobar og Ken Puls skrev denne utmerkede boka om M Is For (DATA) MONKEY - den beste boka som finnes om Power Query. Gå og sjekk det ut.
Greit, pakk opp: i dag er en veldig lang episode; vi har en seer, laster ned data fra et system der hvert element er atskilt med ALT + ENTER, og vi prøver å gjøre et VLOOKUP for hvert enkelt element; bygget en løsning i dag ved hjelp av Power Query inkludert det strukturerte kolonneverktøyet for ekstrakt som; men det fungerer bare på en liste, ikke en tabell, så jeg måtte bruke TABLE.COLUMN-funksjonen til å konvertere tabellen til en liste.
Vel hei. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2151.xlsm
