CONCATENATEX in Power Query. Die neue TEXTJOIN-Funktion ist fantastisch. Können Sie mit Power Query dasselbe tun? Ja. Jetzt kannst du.
Schau Video
- Ein Viewer lädt Daten von einem System herunter, bei dem jedes Element durch Alt + Eingabetaste getrennt ist
- Bill: Warum machst du das? Viewer: So erbe ich die Daten. Ich möchte es so halten.
- Bill: Was möchten Sie mit den 40% der Werte tun, die nicht in der Tabelle enthalten sind? Zuschauer: Keine Antwort
- Bill: Es gibt eine komplizierte Möglichkeit, dies zu lösen, wenn Sie über die neuesten Power Query-Tools verfügen.
- Stattdessen ein VBA-Makro, um es zu lösen - das Makro sollte bis zu Excel 2007 zurück funktionieren
- Führen Sie anstelle von VLOOKUP eine Reihe von Suchen und Ersetzen mit VBA durch
Video-Transkript
Lerne Excel aus, Podcast Episode 2151.
Ich weiß wirklich nicht, wie ich das nennen soll. Wenn ich versuche, die Leute anzulocken, die DAX verwenden, würde ich ConcatenateX in Power Query sagen, oder nur die Leute, die reguläres Excel, aber Office 365 verwenden, würde ich TEXTJOIN in Power Query sagen, oder, um ganz ehrlich zu sein, es ist Eine super komplexe Reihe von Schritten in Power Query, um eine super verrückte Lösung in Excel zu ermöglichen.
Hallo. Willkommen zurück im Netcast. Ich bin Bill Jelen. Nun, gestern in Folge 2150 habe ich das Problem beschrieben. Jemand hat diese Datei gesendet, in der sein System die Artikel herunterlädt, bei denen es sich um eine Bestellung mit Zeilenvorschüben handelt. Mit anderen Worten, ALT + EINGABETASTE, und siehe, WRAP TEXT ist aktiviert, und sie möchten für jedes dieser Elemente einen VLOOKUP in diesem LOOKUPTABLE durchführen. Ich bin wie, was? Warum tust du das? Aber das habe ich gestern besprochen. Versuchen wir einfach herauszufinden, wie das geht.
Ich sagte tatsächlich, Power Query wäre der beste Weg, dies zu tun, aber ich war ratlos darüber, wie man den letzten Teil macht. Ich sagte, ist es in Ordnung, wenn jeder Gegenstand in einer eigenen Reihe landet? Nein, sie müssen wieder in dieser ursprünglichen Reihenfolge sein. Ich denke, das ist schrecklich, aber in meinem Twitter-Feed erst letzte Woche, Tim Rodman, 27. September: "Endlich dieses Buch lesen" - ich vermute, es ist PowerPivot Alchemy - "und hat bereits seinen ConcatenateX-Wunsch erhalten. ” Ich war ein Schlauer, als ich das tat und nach PERHAPS ROMANX fragte, aber ich wollte wahrscheinlich wirklich ConcatenateX, und so gab mir Tim einen Hinweis, dass ich das jetzt in Power BI tun kann.
Also ging ich zu meinen Freunden, Rob Collie von Power Pivot Pro und Miguel Escobar, und sie sind beide Autoren großartiger Bücher. Ich habe beide Bücher, aber diese Funktion ist zu neu, nicht in beiden Büchern. Ich sagte, hey, wisst ihr, wie das geht? Und Miguel gewinnt den Preis, weil Miguel heute früh oder spät in der Nacht aufgestanden ist - ich bin mir nicht sicher, welcher - und den Code eingeschickt hat.
Also gut, hier ist der Plan in Power Query und dieser ist so kompliziert. Ich schreibe nie einen Plan in Power Query. Ich mache einfach die ganzen Dinge. Ich beginne mit den Originaldaten und füge eine INDEX-Spalte hinzu, damit wir die Artikel aus einer Bestellung zusammenhalten können. SPLIT COLUMN to ROWS mit einem LINEFEED. Dies ist das zweite oder dritte Mal im Podcast, dass ich diese neue Funktion verwendet habe. Wie cool ist das. Ich hatte eine zweite INDEX-Spalte, damit wir Elemente in der ursprünglichen Reihenfolge sortieren und dann als Verbindung speichern können.
Dann kommen wir zur LOOKUP-Tabelle, machen sie zu einer Tabelle, fragen aus der Tabelle ab, SPEICHERN ALS VERBINDUNG - das war genau dort der einfachste Teil - und führen diese Abfrage und diese Abfrage basierend auf dem Element zusammen Nummer, alle Elemente aus der linken Tabelle, dies ist die linke Tabelle, die von rechts übereinstimmt, ersetzen Nullen durch die Elementnummer. Wir sind immer noch in der Luft, was wir tun wollen, wenn etwas aus irgendeinem Grund nicht gefunden wird. Ich habe diese Frage gestellt, aber die Person, die die Datei gesendet hat, antwortet nicht. Deshalb werde ich sie nur durch die Artikelnummer ersetzen. Hoffentlich ist es das Richtige, der LOOKUPTABLE weitere Elemente hinzuzufügen, damit keine gefundenen gefunden werden, aber hier sind wir, und dann werden wir nach INDEX1 und INDEX2 sortieren.Die Dinge sind wieder in der richtigen Reihenfolge und dann war dies der Teil, den ich nicht herausfinden konnte, wie es geht.
Wir werden nach INDEX1 gruppieren, indem wir das Äquivalent eines TEXTJOIN oder ConcatenateX mit dem Zeichen 10 als Trennzeichen, als Aggregator ausführen, und natürlich ist dies der Teil, der der schwierige Teil ist, aber es ist der Teil, der hier wirklich neu ist diese Reihe von Schritten. Wenn Sie also verstehen, was TEXTJOIN tut oder was ConcatenateX getan hätte, können wir dies im Wesentlichen mit dieser Art von Schritt tun. Also gut. Also, lass es uns versuchen.
Also fangen wir hier an. Hier sind unsere Originaldaten, hat eine Überschrift. Also werde ich AS TABLE, CONTROL + T FORMATIEREN, MEINE TABELLE HAT HEADERS, ja, und dann werden wir Power Query verwenden. Jetzt bin ich in Excel 2016 Office 365, also ist es hier auf der linken Seite der Registerkarte DATEN. Wenn Sie sich nur in Excel 2016 und nicht in Office 365 befinden, befindet es sich in der Mitte - GET & TRANSFORM. Wenn Sie sich in Excel 2010 oder 2013 befinden, wird es hier draußen eine eigene Registerkarte namens Power Query geben. Wenn Sie diese Registerkarte nicht haben, müssen Sie diese Registerkarte herunterladen. Wenn Sie einen Mac oder Android oder eine der anderen gefälschten Versionen von Excel verwenden, gibt es leider keine Power Query für Sie. Holen Sie sich eine Windows-Version von Excel und probieren Sie es aus.
Also gut, wir werden eine Power-Abfrage von einer Tabelle aus durchführen, okay, und das erste, was ich tun werde, ist, eine INDEX-SPALTE HINZUFÜGEN und von 1 an zu beginnen. Okay Dies ist also im Wesentlichen Reihenfolge 1, Reihenfolge 2, Reihenfolge 3, Reihenfolge 4. Dann wählen wir diese Spalte aus und auf der Registerkarte TRANSFORM gehen wir zu SPLIT COLUMN, BY DELIMITER, und sie konnten Erkennen, dass es sich um ein LineFeed handelt, ist das Trennzeichen. Ich finde es toll, dass Power Query dies erkennt. Warum findet Excel, Text zu Spalten, ja, Text zu Spalten nicht heraus, was das Trennzeichen ist? Und jedes Mal werden wir in Reihen spalten und Sonderzeichen verwenden. Okay, das ist alles gut.
Beobachten Sie jetzt, was hier passiert. Wir haben 999 Zeilen, aber jetzt haben wir weit mehr als das. Jeder Artikel in dieser Bestellnummer ist jetzt eine eigene Zeile. Nun, die Person, die diese Frage gestellt hat, möchte nicht, dass es sich um eine eigene Zeile handelt, aber wir müssen sie zu einer eigenen Zeile machen, damit wir den Join durchführen können. Ich werde hier eine neue INDEX-Spalte hinzufügen. ADD COLUMN, INDEX COLUMN, FROM 1, und so haben wir … das sind im Wesentlichen die Bestellnummern und dann die Reihenfolge innerhalb der Bestellung, weil ich festgestellt habe, dass diese später in einer anderen Reihenfolge sein werden. Ich weiß nicht, in welche Reihenfolge sie wechseln, aber hier sind wir.
Also gut, HOME, nicht die Schaltfläche CLOSE & LOAD, sondern das Dropdown-Menü CLOSE & LOAD und CLOSE & LOAD TO. Ich weiß nicht, warum es 10 Sekunden dauert, bis sie dieses Dialogfeld zum ersten Mal anzeigen. Wir werden NUR VERBINDUNG SCHAFFEN. OK klicken. Wunderschönen. Das ist also TABELLE1, TABELLE1.
Jetzt gehen wir zu unserem LOOKUPTABLE. LOOKUPTABLE wird einfach zu verarbeiten sein. Wir werden dies als Tabelle formatieren. STEUERUNG + T. OK klicken. DATA oder POWER QUERY, wenn Sie in einer alten Version sind, FROM TABLE. Dies wird TABELLE2 genannt. Nennen wir es LOOKUPTABLE. Perfekt. SCHLIESSEN & LADEN, SCHLIESSEN & LADEN, NUR VERBINDUNG ERSTELLEN.
In Ordung. Jetzt haben wir unsere zwei Teile hier und ich möchte diese beiden zusammenführen. Also gehen wir einfach zu einem neuen Ort und dann DATA, GET DATA, COMBINE QUERIES, wir machen eine MERGE und die Tabelle auf der linken Seite wird TABLE1 sein - das sind unsere Originaldaten - - und wir werden diese ARTIKEL-Nummer verwenden und diese bis zur LOOKUPTABLE und dieser ARTIKEL-Nummer heiraten. Es ist wirklich nicht intuitiv, da Sie in beiden Fällen auf die EINZELTEILE klicken müssen, um zu definieren, was der Schlüssel ist, und einen OUTER-Join, ALL FROM FIRST, MATCHING FROM SECOND, und sehen, dass 40% davon in der fehlen NACHSCHLAGWERK. Dies sind alles gefälschte Daten, aber die ursprünglichen Daten fehlten auch zu 40% in der LOOKUPTABLE. Wirklich frustrierend. In Ordung. Hier ist also unsere ARTIKELNUMMER, unsere 2 INDEX-Felder und dann unsere LOOKUPTABLE hier. ICH'Ich werde das erweitern und nach der Beschreibung fragen. Okay, Sie sehen, wir haben hier ein paar 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.
Nun, hey, dies ist der Punkt, an dem ich Sie normalerweise auffordere, mein Buch zu kaufen, aber heute bitten wir Sie stattdessen, Miguels Buch zu kaufen. Miguel Escobar und Ken Puls haben dieses hervorragende Buch über M Is For (DATA) MONKEY geschrieben - das beste Buch über Power Query. Schau dir das an.
Okay, zum Abschluss: Heute ist eine wirklich lange Episode; Wir haben einen Viewer, laden Daten von einem System herunter, bei dem jedes Element durch ALT + ENTER getrennt ist, und versuchen, für jedes einzelne Element einen VLOOKUP zu erstellen. hat heute eine Lösung mit Power Query erstellt, einschließlich des strukturierten Spaltenwerkzeugs von extract as; Da dies jedoch nur für eine Liste und nicht für eine Tabelle funktioniert, musste ich die Funktion TABLE.COLUMN verwenden, um die Tabelle in eine Liste zu konvertieren.
Nun, hey. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast2151.xlsm
