CONCATENATEX στο Power Query. Η νέα λειτουργία TEXTJOIN είναι καταπληκτική. Μπορείτε να κάνετε το ίδιο πράγμα με το Power Query; Ναί. Τώρα μπορείς.
Δες το βίντεο
- Ένας θεατής λαμβάνει δεδομένα από ένα σύστημα όπου κάθε στοιχείο διαχωρίζεται με Alt + Enter
- Μπιλ: Γιατί το κάνεις αυτό; Πρόγραμμα προβολής: Με αυτόν τον τρόπο κληρονομούν τα δεδομένα. Θέλω να το κρατήσω έτσι.
- Μπιλ: Τι θέλετε να κάνετε με το 40% των τιμών που δεν περιλαμβάνονται στον πίνακα; Προβολή: Δεν υπάρχει απάντηση
- Bill: Υπάρχει ένας περίπλοκος τρόπος για να το λύσετε εάν έχετε τα πιο πρόσφατα εργαλεία Power Query.
- Αντ 'αυτού, μια μακροεντολή VBA για την επίλυσή της - η μακροεντολή πρέπει να λειτουργεί μέχρι το Excel 2007
- Αντί να κάνετε VLOOKUP, κάντε μια σειρά Εύρεση & Αντικατάσταση με VBA
Μεταγραφή βίντεο
Μάθετε το Excel από, Podcast Episode 2151.
Πραγματικά δεν ξέρω τι να ονομάσω αυτό. Αν προσπαθώ να προσελκύσω άτομα που χρησιμοποιούν DAX, θα έλεγα το ConcatenateX στο Power Query ή απλά τα άτομα που χρησιμοποιούν κανονικό Excel αλλά το Office 365, θα έλεγα TEXTJOIN στο Power Query ή, για να είμαι απόλυτα ειλικρινής, είναι ένα πολύ σύνθετο σύνολο βημάτων στο Power Query για να ενεργοποιήσετε μια υπερ-τρελή λύση στο Excel.
Γεια. Καλώς ήλθατε πίσω στο netcast. Είμαι ο Μπιλ Τζέλεν. Λοιπόν, χθες στο επεισόδιο 2150, περιέγραψα το πρόβλημα. Κάποιος έστειλε σε αυτό το αρχείο όπου το σύστημά του κατεβάζει τα στοιχεία που είναι παραγγελία με τροφοδοσία γραμμής μεταξύ τους. Με άλλα λόγια, ALT + ENTER και δείτε, το WRAP TEXT είναι ενεργοποιημένο και θέλουν να κάνουν ένα VLOOKUP σε αυτό το LOOKUPTABLE για καθένα από αυτά τα στοιχεία. Είμαι, τι; Γιατί το κάνεις αυτό? Αλλά το κάλυψα χθες. Ας προσπαθήσουμε να βρούμε πώς να το κάνουμε αυτό.
Στην πραγματικότητα είπα, λοιπόν, το Power Query θα ήταν ο καλύτερος τρόπος για να το κάνω αυτό, αλλά μπήκα στο πώς να κάνω το τελευταίο μέρος. Είπα, είναι εντάξει αν κάθε στοιχείο καταλήγει στη δική του σειρά; Όχι, πρέπει να επιστρέψουν σε αυτήν την αρχική ακολουθία. Είμαι σαν, αυτό είναι φρικτό, αλλά, στη ροή μου στο Twitter μόλις την περασμένη εβδομάδα, ο Tim Rodman, 27 Σεπτεμβρίου: «Τέλος διαβάζοντας αυτό το βιβλίο», υποθέτω ότι είναι το PowerPivot Alchemy - «και έχει ήδη την επιθυμία του ConcatenateX " Ήμουν έξυπνος όταν το έκανα αυτό, ζητώντας PERHAPS ROMANX, αλλά μάλλον πραγματικά ήθελα το ConcatenateX, και έτσι ο Tim μου έδωσε ένα κεφάλι που μπορώ τώρα να το κάνω αυτό στο Power BI.
Έτσι, βγήκα στους φίλους μου, τον Rob Collie στο Power Pivot Pro και τον Miguel Escobar και, ξέρετε, είναι και οι δύο συγγραφείς σπουδαίων βιβλίων. Έχω και τα δύο αυτά βιβλία, αλλά αυτή η δυνατότητα είναι πολύ νέα, ούτε σε κανένα βιβλίο. Είπα, γεια, ξέρεις πώς να το κάνεις αυτό; Και ο Μιγέλ κερδίζει το βραβείο επειδή ο Μιγέλ ήταν νωρίς το πρωί ή αργά χθες το βράδυ - δεν είμαι σίγουρος ποιο - και έστειλε τον κωδικό.
Εντάξει, λοιπόν, εδώ είναι το σχέδιο στο Power Query και αυτό είναι τόσο περίπλοκο. Δεν γράφω ποτέ ένα σχέδιο στο Power Query. Πάω να κάνω τα πάντα. Θα ξεκινήσω με τα αρχικά δεδομένα, θα προσθέσω μια στήλη INDEX, ώστε να μπορούμε να κρατήσουμε μαζί τα στοιχεία από μια παραγγελία, SPLIT COLUMN σε ROWS χρησιμοποιώντας ένα LINEFEED. Αυτή είναι η δεύτερη ή τρίτη φορά στο podcast που χρησιμοποίησα αυτήν τη νέα δυνατότητα. Πόσο δροσερό είναι αυτό. Είχα μια δεύτερη στήλη INDEX, ώστε να μπορούμε να ταξινομήσουμε τα στοιχεία στην αρχική ακολουθία και, στη συνέχεια, να αποθηκεύσουμε ως σύνδεση.
Στη συνέχεια, πρόκειται να έρθουμε στον πίνακα LOOKUP, να το κάνουμε τραπέζι, να το ζητήσετε από τον πίνακα, SAVE AS CONNECTION - αυτό θα ήταν το πιο εύκολο μέρος εκεί - και στη συνέχεια θα συγχωνεύσουμε αυτό το ερώτημα και αυτό το ερώτημα με βάση το στοιχείο αριθμός, όλα τα στοιχεία από τον αριστερό πίνακα, αυτός είναι ο αριστερός πίνακας, που ταιριάζει από τα δεξιά, αντικαταστήστε τις μηδενικές με τον αριθμό είδους. Βρισκόμαστε ακόμα στον αέρα για το τι θέλουμε να κάνουμε όταν κάτι δεν βρέθηκε για κάποιο λόγο. Έχω υποβάλει αυτήν την ερώτηση, αλλά το άτομο που έστειλε το αρχείο δεν απαντά, οπότε απλώς θα το αντικαταστήσω με τον αριθμό είδους. Ας ελπίσουμε ότι το σωστό πράγμα που πρέπει να κάνετε είναι να προσθέσετε περισσότερα στοιχεία στο LOOKUPTABLE, ώστε να μην υπάρχουν θεμέλια, αλλά εδώ είμαστε, και στη συνέχεια πρόκειται να ταξινομήσουμε κατά INDEX1 και INDEX2, έτσι έτσι,τα πράγματα είναι πίσω στη σωστή σειρά και τότε αυτό ήταν το μέρος που δεν μπορούσα να καταλάβω πώς να το κάνω.
Θα ομαδοποιήσουμε από τον INDEX1 κάνοντας το ισοδύναμο ενός TEXTJOIN ή ConcatenateX με τον χαρακτήρα 10 ως διαχωριστικό, ως αθροιστή και, φυσικά, αυτό είναι το μέρος που είναι το δύσκολο μέρος, αλλά είναι το μέρος που είναι πραγματικά νέο εδώ αυτό το σύνολο βημάτων. Έτσι, αν καταλαβαίνετε τι κάνει το TEXTJOIN ή μπορείτε να αντιληφθείτε τι θα έκανε το ConcatenateX, το κάνουμε ουσιαστικά χρησιμοποιώντας αυτό το είδος βήματος. Λοιπόν, εντάξει. Ας το κάνουμε λοιπόν.
Λοιπόν, θα ξεκινήσουμε εδώ. Εδώ είναι τα αρχικά μας δεδομένα, έχει μια επικεφαλίδα. Λοιπόν, πρόκειται να ΜΟΡΦΩΜΑ ΩΣ ΠΙΝΑΚΑΣ, CONTROL + T, Ο ΠΙΝΑΚΑΣ ΜΟΥ ΕΧΕΙ ΚΕΦΑΛΙΕΣ, ναι, και μετά θα χρησιμοποιήσουμε το Power Query. Τώρα, είμαι στο Excel 2016 Office 365, οπότε είναι εδώ στο αριστερό τμήμα της καρτέλας DATA. Εάν είστε απλώς στο straight Excel 2016, όχι στο Office 365, είναι στη μέση - GET & TRANSFORM. Εάν βρίσκεστε στο Excel 2010 ή 2013, θα είναι η δική του καρτέλα εδώ που ονομάζεται Power Query και εάν δεν έχετε αυτήν την καρτέλα, θα πρέπει να κάνετε λήψη αυτής της καρτέλας. Εάν χρησιμοποιείτε Mac ή Android ή οποιαδήποτε από τις άλλες ψεύτικες εκδόσεις του Excel, λυπούμαστε, δεν υπάρχει Power Query για εσάς. Αποκτήστε μια έκδοση του Excel για Windows και δοκιμάστε το.
Εντάξει, λοιπόν, θα κάνουμε ένα ερώτημα ενέργειας από έναν πίνακα, εντάξει, και το πρώτο πράγμα που πρόκειται να κάνω είναι να προσθέσω μια στήλη ΔΕΙΚΤΗΣ και θα ξεκινήσω ΑΠΟ 1. Εντάξει , λοιπόν, αυτή είναι ουσιαστικά η σειρά 1, η σειρά 2, η σειρά 3, η σειρά 4. Στη συνέχεια, θα επιλέξουμε αυτήν τη στήλη και, στην καρτέλα ΜΕΤΑΦΟΡΕΣ, θα μεταφέρουμε στο SPLIT COLUMN, BY DELIMITER και κατάφεραν να εντοπίστε ότι είναι ένα LineFeed είναι οριοθέτης. Λατρεύω ότι το Power Query το εντοπίζει. Τώρα, γιατί το Excel, το κείμενο σε στήλες, ναι, το κείμενο σε στήλες δεν καταλαβαίνει τι είναι ο οριοθέτης; Και σε κάθε περίπτωση πηγαίνουμε να ΜΠΟΡΟΥΜΕ ΣΕ ΣΕΙΡΕΣ, και ΧΡΗΣΙΜΟΠΟΙΗΣΤΕ ΕΙΔΙΚΟ ΧΑΡΑΚΤΗΡΑ. Εντάξει, έτσι όλα είναι καλά.
Τώρα παρακολουθήστε τι συμβαίνει εδώ. Έχουμε 999 σειρές, αλλά τώρα έχουμε πολύ περισσότερα από αυτό. Έτσι, κάθε στοιχείο σε αυτόν τον αριθμό παραγγελίας είναι τώρα η δική του σειρά. Τώρα, το άτομο που έθεσε αυτήν την ερώτηση δεν θέλει να είναι η δική του σειρά, αλλά θα πρέπει να το κάνουμε να είναι η δική του σειρά, ώστε να μπορούμε να κάνουμε τη συμμετοχή. Θα προσθέσω μια νέα στήλη INDEX εδώ. ΠΡΟΣΘΗΚΗ ΣΤΗΛΗΣ, ΣΤΗΛΗ ΔΕΙΚΤΗΣ, ΑΠΟ 1, και έτσι έχουμε… αυτοί είναι ουσιαστικά οι αριθμοί παραγγελίας και, στη συνέχεια, αυτές είναι οι ακολουθίες εντός της παραγγελίας, επειδή έχω αποφασίσει ότι, αργότερα, πρόκειται να είναι σε κάποια άλλη σειρά. Δεν ξέρω σε ποια σειρά αλλάζουν, αλλά εδώ είμαστε.
Εντάξει, λοιπόν, ΑΡΧΙΚΗ, όχι το κουμπί ΚΛΕΙΣΤΟ & ΦΟΡΤΙΟ, αλλά το αναπτυσσόμενο μενού ΚΛΕΙΣΤΟ & ΦΟΡΤΙΟ, και ΚΛΕΙΣΤΟ & ΦΟΡΤΙΟ. Δεν ξέρω γιατί χρειάζονται 10 δευτερόλεπτα για να εμφανίσουν αυτό το παράθυρο διαλόγου την πρώτη φορά. Θα δημιουργήσουμε ΜΟΝΟ ΣΥΝΔΕΣΗ. Κάντε κλικ στο OK. Πανεμορφη. Αυτό είναι ο ΠΙΝΑΚΑΣ1, ΠΙΝΑΚΑΣ1.
Τώρα, θα πάμε στο LOOKUPTABLE. Το LOOKUPTABLE θα είναι εύκολο να επεξεργαστεί. Θα το μορφοποιήσουμε ως τραπέζι. ΕΛΕΓΧΟΣ + Τ. Κάντε κλικ στο OK. ΔΕΔΟΜΕΝΑ ή ΕΡΩΤΗΣΗ ΔΥΝΑΜΙΚΗΣ εάν βρίσκεστε σε παλιά έκδοση, ΑΠΟ ΤΟΝ ΠΙΝΑΚΑ. Αυτό θα ονομάζεται ΠΙΝΑΚΑΣ2. Ας το ονομάσουμε ΠΟΛΥΤΕΛΕΣ. Τέλειος. ΚΛΕΙΣΤΟ & ΦΟΡΤΙΟ, ΚΛΕΙΣΤΟ & ΦΟΡΤΙΟ, ΔΗΜΙΟΥΡΓΙΑ ΜΟΝΟ.
Καλώς. Τώρα, έχουμε τα δύο κομμάτια μας εδώ και θέλω να τα συγχωνεύσω. Έτσι, απλώς πρόκειται να πάμε σε ένα νέο σημείο και στη συνέχεια DATA, GET DATA, COMBINE QUERIES, πρόκειται να κάνουμε MERGE και ο πίνακας στα αριστερά θα είναι ΠΙΝΑΚΑΣ1 - αυτά είναι τα αρχικά μας δεδομένα - - και θα χρησιμοποιήσουμε αυτόν τον αριθμό ITEM και θα το παντρευτούμε με το LOOKUPTABLE και τον αριθμό ITEM. Είναι πραγματικά μη διαισθητικό εκεί, πρέπει να κάνετε κλικ στα ΕΙΔΗ και στις δύο περιπτώσεις για να ορίσετε ποιο είναι το κλειδί και μια ΕΞΩΤΕΡΙΚΗ ένωση, ΟΛΑ ΑΠΟ ΠΡΩΤΗ, ΣΥΜΒΟΥΛΗ ΑΠΟ ΤΟ ΔΕΥΤΕΡΟ, και, δείτε, υπάρχει το 40% αυτών που λείπουν ΠΙΝΑΚΑΣ ΑΝΑΖΗΤΗΣΗΣ. Όλα αυτά είναι ψεύτικα δεδομένα, αλλά και τα αρχικά δεδομένα έλειπαν 40% από το LOOKUPTABLE. Πραγματικά απογοητευτικό. Καλώς. Λοιπόν, εδώ είναι ο αριθμός ITEM, τα 2 πεδία INDEX και, στη συνέχεια, ΠΡΟΣΟΧΗ ΕΓΩ'Θα το επεκτείνω και θα ζητήσω την ΠΕΡΙΓΡΑΦΗ. Εντάξει, βλέπετε ότι έχουμε μια σειρά μηδενικών εδώ.
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.
Τώρα, γεια, αυτό είναι το σημείο όπου συνήθως σας ζητώ να πάτε να αγοράσετε το βιβλίο μου, αλλά, σήμερα, αντί να σας ζητήσουμε να πάτε να αγοράσετε το βιβλίο του Miguel. Ο Miguel Escobar και ο Ken Puls έγραψαν αυτό το εξαιρετικό βιβλίο για το M Is For (DATA) MONKEY - το καλύτερο βιβλίο που υπάρχει στο Power Query. Πήγαινε να το ελέγξεις.
Εντάξει, ολοκληρώστε: σήμερα είναι ένα πολύ μεγάλο επεισόδιο. έχουμε ένα πρόγραμμα προβολής, κατεβάζει δεδομένα από ένα σύστημα όπου κάθε στοιχείο διαχωρίζεται από το ALT + ENTER και προσπαθούμε να κάνουμε ένα VLOOKUP για κάθε μεμονωμένο στοιχείο. δημιούργησε μια λύση σήμερα χρησιμοποιώντας το Power Query, συμπεριλαμβανομένου του εργαλείου δομημένης στήλης του εκχυλίσματος όπως: αλλά αυτό λειτουργεί μόνο σε μια λίστα, όχι σε έναν πίνακα, οπότε έπρεπε να χρησιμοποιήσω τη συνάρτηση TABLE.COLUMN για να μετατρέψω τον πίνακα σε λίστα.
Λοιπόν, γεια. Θέλω να σας ευχαριστήσω που σταματήσατε. Θα σας δούμε την επόμενη φορά για ένα άλλο netcast από.
Λήψη αρχείου
Κατεβάστε το δείγμα αρχείου εδώ: Podcast2151.xlsm
