TEXTJOIN i Power Query - Excel-tip

CONCATENATEX i Power Query. Den nye TEXTJOIN-funktion er fantastisk. Kan du gøre det samme med Power Query? Ja. Nu kan du.

Se video

• En seer downloader data fra et system, hvor hvert element er adskilt af Alt + Enter
• Bill: Hvorfor gør du det? Seer: Det er sådan, jeg arver dataene. Jeg vil holde det sådan.
• Bill: Hvad vil du gøre med de 40% af værdierne, der ikke er i tabellen? Seer: Intet svar
• Bill: Der er en kompliceret måde at løse dette på, hvis du har de nyeste Power Query-værktøjer.
• I stedet for en VBA-makro til at løse det - makroen skal fungere helt tilbage til Excel 2007
• I stedet for at lave VLOOKUP skal du lave en række Find & Erstat med VBA

Videoudskrift

Lær Excel fra, Podcast Episode 2151.

Jeg ved virkelig ikke, hvad jeg skal kalde denne. Hvis jeg prøver at tiltrække de mennesker, der bruger DAX, vil jeg sige ConcatenateX i Power Query eller bare de mennesker, der bruger almindelig Excel, men Office 365, vil jeg sige TEXTJOIN i Power Query, eller for at være helt ærlig er det et superkompleks sæt trin i Power Query for at muliggøre en super-sindssyg løsning i Excel.

Hej. Velkommen tilbage til netcast. Jeg er Bill Jelen. Nå, i går i afsnit 2150 beskrev jeg problemet. Nogen sendte denne fil, hvor deres system downloader de varer, der er en ordre med linjefeedninger imellem. Med andre ord, ALT + ENTER, og se, WRAP TEXT er slået til, og de vil lave en VLOOKUP i denne LOOKUPTABLE for hver af disse emner. Jeg er ligesom, hvad? Hvorfor gør du det her? Men jeg dækkede det i går. Lad os bare prøve at finde ud af, hvordan man gør dette.

Jeg sagde faktisk, ja, Power Query ville være den bedste måde at gøre dette på, men jeg fik stumped om, hvordan man laver den sidste del. Jeg sagde, er det okay, hvis hver vare ender i sin egen række? Nej, de skal være tilbage i denne originale rækkefølge. Jeg er ligesom det er forfærdeligt, men på min Twitter-feed lige i sidste uge, Tim Rodman, den 27. september: ”Endelig læser jeg denne bog,” - jeg gætter på, at det er PowerPivot Alchemy - “og har allerede fået sit ConcatenateX-ønske. ” Jeg var en smartass, da jeg gjorde dette og bad om PERHAPS ROMANX, men jeg ville sandsynligvis virkelig have ConcatenateX, og så Tim gav mig en heads up, at jeg nu kan gøre det i Power BI.

Så jeg gik ud til mine venner, Rob Collie hos Power Pivot Pro og Miguel Escobar, og du ved, de er begge forfattere af fantastiske bøger. Jeg har begge disse bøger, men denne funktion er for ny, ikke i nogen af ​​bøgerne. Jeg sagde, hej, ved I, hvordan man gør det? Og Miguel vinder prisen, fordi Miguel var oppe tidligt i morges eller sent i går aftes - jeg er ikke sikker på hvilken - og sendte koden ind.

Okay, så her er planen i Power Query, og denne er så kompliceret. Jeg skriver aldrig en plan ud i Power Query. Jeg går bare og gør de hele ting. Jeg begynder med de originale data, tilføjer en INDEX-kolonne, så vi kan holde varerne fra en ordre sammen, SPLIT KOLONNE til RÆKER ved hjælp af en LINEFEED. Dette er anden eller tredje gang på podcasten, jeg bruger denne nye funktion. Hvor sejt er det. Jeg havde en anden INDEX-kolonne, så vi kan sortere varer i den originale sekvens og derefter GEM SOM EN FORBINDELSE.

Derefter skal vi komme til LOOKUP-tabellen, gøre det til en tabel, forespørgsel fra tabel, GEM SOM FORBINDELSE - det ville være den nemmeste del lige der - og derefter flette denne forespørgsel og denne forespørgsel baseret på element nummer, alle varer fra venstre tabel, dette er den venstre tabel, der matcher fra højre, erstat nuller med varenummeret. Vi er stadig op i luften på, hvad vi vil gøre, når noget ikke findes af en eller anden grund. Jeg har stillet dette spørgsmål, men den person, der sendte filen, svarer ikke, så jeg skal bare erstatte det med varenummeret. Forhåbentlig er den rigtige ting at tilføje flere ting til LOOKUPTABLE, så der ikke er nogen, der ikke findes, men her er vi, og så skal vi sortere efter INDEX1 og INDEX2, så den måde,tingene er tilbage i den rigtige rækkefølge, og så var dette den del, som jeg ikke kunne finde ud af, hvordan jeg skulle gøre.

Vi grupperer efter INDEX1 ved at gøre det ækvivalente med en TEXTJOIN eller ConcatenateX med tegnet 10 som separator, som aggregator, og selvfølgelig er dette den del, der er den hårde del, men det er den del, der virkelig er ny her i dette sæt trin. Så hvis du forstår, hvad TEXTJOIN gør eller kan konceptualisere, hvad ConcatenateX ville have gjort, gør vi det i det væsentlige ved hjælp af denne slags trin. Så okay. Så lad os give det et skud.

Så vi starter her. Her er vores originale data, har en overskrift. Så jeg vil FORMATERE SOM TABEL, CONTROL + T, MIT TABEL HAR HEADERS, ja, og så skal vi bruge Power Query. Nu er jeg i Excel 2016 Office 365, så det er her til venstre på fanen DATA. Hvis du bare er i lige Excel 2016, ikke Office 365, er det i midten - FÅ & TRANSFORM. Hvis du er i Excel 2010 eller 2013, bliver det sin egen fane herude, der hedder Power Query, og hvis du ikke har denne fane, bliver du nødt til at downloade denne fane. Hvis du bruger en Mac eller Android eller nogen af ​​de andre falske versioner af Excel, er du ked af, ingen Power Query til dig. Få en Windows-version af Excel, og prøv det.

Okay, så vi skal foretage en Power-forespørgsel FRA EN TABEL, okay, og den første ting, jeg skal gøre er, at jeg TILFØJER en INDEKSKOLONNE, og jeg starter fra 1. Okay , så dette er i det væsentlige rækkefølge 1, rækkefølge 2, rækkefølge 3, rækkefølge 4. Derefter skal vi vælge denne kolonne, og på fanen TRANSFORM skal vi SPLIT KOLONNE, AF DELIMITER, og de var i stand til opdage, at det er en LineFeed er afgrænseren. Jeg elsker, at Power Query opdager dette. Nu, hvorfor regner ikke Excel, tekst til kolonner, ja, tekst til kolonner, hvad afgrænsningen er? Og hver begivenhed skal vi SPLIT I RÆKER og BRUGE SPECIAL KARAKTER. Okay, så alt dette er godt.

Se nu hvad der sker her. Vi har 999 rækker, men nu har vi langt mere end det. Så hver vare i det ordrenummer er nu sin egen række. Nu vil den person, der stillede dette spørgsmål, ikke have det til at være sin egen række, men vi bliver nødt til at gøre det til sin egen række, så vi kan deltage. Jeg vil tilføje en ny INDEX-kolonne her. TILFØJ KOLONNE, INDEKSKOLONNE, FRA 1, og så har vi … dette er i det væsentlige ordrenumre, og så er dette rækkefølgen inden for rækkefølgen, fordi jeg har bestemt, at disse senere vil være i en anden rækkefølge. Jeg ved ikke, hvilken rækkefølge de skifter til, men her er vi.

Okay, så HJEM, ikke knappen CLOSE & LOAD, men drop-down CLOSE & LOAD, og ​​CLOSE & LOAD TO. Jeg ved ikke, hvorfor det tager 10 sekunder for dem at vise denne dialogboks første gang. Vi opretter KUN OPRETTELSE. Klik på OK. Smuk. Så det er TABEL1, TABEL1.

Nu skal vi gå til vores LOOKUPTABLE. LOOKUPTABLE bliver let at behandle. Vi skal formatere dette som en tabel. CONTROL + T. Klik på OK. DATA eller POWER QUERY, hvis du er i en gammel version, FRA TABEL. Dette kaldes TABEL2. Lad os kalde det LOOKUPTABLE. Perfekt. LUK & LAD, LUK & LAD TIL, OPRET KUN TILSLUTNING.

I orden. Nu har vi vores to bits herovre, og jeg vil fusionere disse to. Så vi skal bare gå til et nyt sted, og DATA, FÅ DATA, KOMBINERE FORESPØRGSEL, vi skal gøre en FUSION, og tabellen til venstre bliver TABEL1 - det er vores originale data - - og vi skal bruge dette VARENUMMER, og vi gifter os med det op til LOOKUPTABLE og det VARENUMMER. Det er virkelig ikke-intuitivt der, du skal klikke på VARER i begge tilfælde for at definere, hvad nøglen er, og en OUTER-sammenføjning, ALT FRA FØRSTE, MATCHING FRA ANDET, og se, der er 40% af disse, der mangler fra LOOKUPTABLE. Dette er alle falske data, men de oprindelige data manglede 40% også i LOOKUPTABLE. Virkelig slags frustrerende. I orden. Så her er vores VARENUMMER, vores 2 INDEX-felter og derefter vores LOOKUPTABLE her. JEG'm skal udvide det og bede om BESKRIVELSEN. Okay, ser du, at vi har en masse nuller her.

Okay, så vi skal lave en betinget kolonne. Betinget kolonne vil sige se på denne kolonne. Hvis det er = til null, skal du bringe denne værdi over, ellers skal du bruge den værdi, der er i den kolonne. Så her, under TILFØJ KOLONNE, laver vi BETINGELSESKOLONNE - dejlig lille brugergrænseflade, der vil lede os igennem dette - hvis LOOKUPTABLEDESCRIPTION LIGER NUL, så vil vi bruge en KOLONNE her af PUNKTER, ellers vil vi bruge KOLONNEN kaldet LOOKUPDESCRIPTION, okay. Klik på OK, og så er vi der. Der er vores CUSTOM-kolonne med enten den nye værdi fra LOOKUPTABLE eller den oprindelige værdi, hvis den ikke findes. På dette tidspunkt kan vi højreklikke og sige, at vi vil FJERNE denne kolonne. Det var en midlertidig søjle, det var en hjælpesøjle. Nu hvor vi har det, vi har brug for, behøver vi ikke den kolonne mere, og faktisk på dette tidspunkt,Jeg har heller ikke brug for denne kolonne længere. Så jeg kan højreklikke og FJERNE den kolonne. I orden. Nu har vi vores data her. Jeg vil sortere det efter den originale INDEX. Så, Sorterende stigende. Det får vores data i den rigtige rækkefølge, og nu hvor de er sorteret, kan jeg faktisk højreklikke og FJERNE den kolonne.

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, dette er det punkt, hvor jeg normalt beder dig om at købe min bog, men i dag, lad os i stedet bede dig om at købe Miguels bog. Miguel Escobar og Ken Puls skrev denne fremragende bog om M Is For (DATA) MONKEY - den bedste bog der findes på Power Query. Gå og tjek det.

Okay, wrap up: i dag er en rigtig lang episode; vi har en seer, downloader data fra et system, hvor hvert emne er adskilt af ALT + ENTER, og vi prøver at lave en VLOOKUP for hvert individuelle emne; bygget en løsning i dag ved hjælp af Power Query inklusive det strukturerede kolonneværktøj til ekstrakt som men det fungerer kun på en liste, ikke på en tabel, så jeg var nødt til at bruge TABLE.COLUMN-funktionen til at konvertere tabellen til en liste.

Nå, hej. Jeg vil gerne takke dig, fordi du kom forbi. Vi ses næste gang til endnu en netcast fra.

Download fil

Download eksempelfilen her: Podcast2151.xlsm