CONCATENATEX dans Power Query. La nouvelle fonction TEXTJOIN est géniale. Pouvez-vous faire la même chose avec Power Query? Oui. Maintenant vous pouvez.
Regarder la vidéo
- Un visualiseur télécharge des données à partir d'un système où chaque élément est séparé par Alt + Entrée
- Bill: Pourquoi fais-tu ça? Viewer: C'est ainsi que j'hérite des données. Je veux poursuivre dans cette voie.
- Bill: Que voulez-vous faire avec les 40% de valeurs qui ne figurent pas dans le tableau? Visionneuse: pas de réponse
- Bill: Il existe un moyen compliqué de résoudre ce problème si vous disposez des derniers outils Power Query.
- Au lieu de cela, une macro VBA pour le résoudre - la macro devrait fonctionner jusqu'à Excel 2007
- Au lieu de rechercher VLOOKUP, effectuez une série de Find & Replace with VBA
Transcription vidéo
Apprenez Excel de, Podcast Episode 2151.
Je ne sais vraiment pas comment appeler celui-ci. Si j'essaie d'attirer les personnes qui utilisent DAX, je dirais ConcatenateX dans Power Query, ou simplement les personnes qui utilisent Excel ordinaire mais Office 365, je dirais TEXTJOIN dans Power Query, ou, pour être tout à fait honnête, c'est un ensemble d'étapes super complexe dans Power Query pour activer une solution super insensée dans Excel.
Hey. Bienvenue à nouveau sur le netcast. Je suis Bill Jelen. Eh bien, hier dans l'épisode 2150, j'ai décrit le problème. Quelqu'un a envoyé ce fichier là où son système télécharge les articles qui sont une commande avec des sauts de ligne entre eux. En d'autres termes, ALT + ENTRÉE et voyez, WRAP TEXT est activé, et ils veulent effectuer une RECHERCHEV dans ce LOOKUPTABLE pour chacun de ces éléments. Je suis comme quoi? Pourquoi fais-tu ça? Mais j'ai couvert cela hier. Essayons simplement de comprendre comment faire cela.
En fait, j'ai dit, eh bien, Power Query serait le meilleur moyen de le faire, mais je me suis demandé comment faire la dernière partie. J'ai dit, est-ce que ça va si chaque élément se retrouve à sa propre ligne? Non, ils doivent être de retour dans cette séquence originale. Je suis comme, c'est horrible, mais, sur mon fil Twitter de la semaine dernière, Tim Rodman, le 27 septembre: "Enfin en train de lire ce livre," - je suppose que c'est PowerPivot Alchemy - "et a déjà eu son souhait ConcatenateX. " J'étais malin quand j'ai fait cela, demandant PERHAPS ROMANX, mais je voulais probablement vraiment ConcatenateX, et Tim m'a donc informé que je pouvais maintenant le faire dans Power BI.
Alors, je suis allé voir mes amis, Rob Collie de Power Pivot Pro et Miguel Escobar, et, vous savez, ils sont tous les deux auteurs de grands livres. J'ai ces deux livres, mais cette fonctionnalité est trop nouvelle, ni dans aucun de ces livres. J'ai dit, hé, savez-vous comment faire ça? Et Miguel remporte le prix parce que Miguel s'est levé tôt ce matin ou tard hier soir - je ne sais pas lequel - et a envoyé le code.
Très bien, voici le plan dans Power Query et celui-ci est tellement compliqué. Je n'écris jamais de plan dans Power Query. Je vais juste faire toutes les choses. Je vais commencer avec les données d'origine, ajouter une colonne INDEX afin que nous puissions conserver les articles d'une commande ensemble, SPLIT COLUMN en ROWS en utilisant un LINEFEED. C'est la deuxième ou la troisième fois sur le podcast que j'utilise cette nouvelle fonctionnalité. À quel point cela est cool. J'avais une deuxième colonne INDEX afin que nous puissions trier les éléments dans la séquence d'origine, puis ENREGISTRER COMME UNE CONNEXION.
Ensuite, nous allons arriver à la table LOOKUP, en faire une table, une requête à partir de la table, ENREGISTRER COMME CONNEXION - cela allait être la partie la plus simple là-bas - puis fusionner cette requête et cette requête en fonction de l'élément nombre, tous les éléments de la table de gauche, il s'agit de la table de gauche, correspondant à partir de la droite, remplacez les valeurs nulles par le numéro de l'élément. Nous ne savons toujours pas ce que nous voulons faire lorsque quelque chose n'est pas trouvé pour une raison quelconque. J'ai posé cette question, mais la personne qui a envoyé le fichier ne répond pas, alors je vais simplement la remplacer par le numéro d'article. J'espère que la bonne chose à faire est d'ajouter plus d'éléments à la LOOKUPTABLE pour qu'il n'y ait pas de fichiers non trouvés, mais nous y sommes, puis nous allons trier par INDEX1 et INDEX2, de cette façon,les choses sont de retour dans le bon ordre et c'est la partie que je ne savais pas comment faire.
Nous allons grouper par INDEX1 en faisant l'équivalent d'un TEXTJOIN ou ConcatenateX avec le caractère 10 comme séparateur, comme agrégateur, et, bien sûr, c'est la partie qui est la partie la plus difficile mais c'est la partie qui est vraiment nouvelle ici dans cet ensemble d'étapes. Donc, si vous comprenez ce que TEXTJOIN fait ou pouvez conceptualiser ce que ConcatenateX aurait fait, nous le faisons essentiellement en utilisant ce type d'étape. Alors, d'accord. Alors, essayons-le.
Donc, nous allons commencer ici. Voici nos données originales, a un titre. Donc, je vais FORMATER COMME TABLE, CONTROL + T, MA TABLE A DES EN-TÊTES, oui, puis nous allons utiliser Power Query. Maintenant, je suis dans Excel 2016 Office 365, donc c'est ici dans la partie gauche de l'onglet DONNÉES. Si vous êtes juste dans Excel 2016, pas Office 365, c'est au milieu - GET & TRANSFORM. Si vous êtes dans Excel 2010 ou 2013, il s'agira de son propre onglet appelé Power Query, et si vous n'avez pas cet onglet, vous devrez télécharger cet onglet. Si vous êtes sur un Mac ou Android ou l'une des autres fausses versions d'Excel, désolé, pas de Power Query pour vous. Obtenez une version Windows d'Excel et essayez-le.
D'accord, nous allons faire une Power Query À PARTIR D'UNE TABLE, d'accord, et la première chose que je vais faire est de AJOUTER une COLONNE D'INDEX et je vais commencer À PARTIR DE 1. D'accord , donc, il s'agit essentiellement de l'ordre 1, de l'ordre 2, de l'ordre 3, de l'ordre 4. Ensuite, nous allons choisir cette colonne et, sous l'onglet TRANSFORMER, nous allons SPLIT COLUMN, BY DELIMITER, et ils ont pu détecter qu'il s'agit d'un LineFeed est le délimiteur. J'adore que Power Query détecte cela. Maintenant, pourquoi Excel, texte en colonnes, oui, texte en colonnes ne comprend-il pas ce qu'est le délimiteur? Et à chaque occurrence, nous allons diviser en rangées et utiliser un personnage spécial. Très bien, tout ça c'est bien.
Maintenant, regardez ce qui se passe ici. Nous avons 999 lignes, mais maintenant nous en avons beaucoup plus. Ainsi, chaque article de ce numéro de commande est désormais sa propre ligne. Maintenant, la personne qui a posé cette question ne veut pas que ce soit sa propre ligne, mais nous allons devoir en faire sa propre ligne afin que nous puissions faire la jointure. Je vais ajouter une nouvelle colonne INDEX ici. ADD COLUMN, INDEX COLUMN, FROM 1, et donc nous avons… ce sont essentiellement les numéros d'ordre et ensuite ce sont la séquence dans l'ordre parce que j'ai déterminé que, plus tard, ils seront dans un autre ordre. Je ne sais pas dans quel ordre ils passent mais nous y voilà.
D'accord, donc, HOME, pas le bouton CLOSE & LOAD mais le menu déroulant CLOSE & LOAD et CLOSE & LOAD TO. Je ne sais pas pourquoi il leur faut 10 secondes pour afficher cette boîte de dialogue la première fois. Nous allons créer UNIQUEMENT UNE CONNEXION. Cliquez sur OK. Beau. Voilà donc TABLE1, TABLE1.
Maintenant, nous allons aller à notre LOOKUPTABLE. LOOKUPTABLE sera facile à traiter. Nous allons formater cela sous forme de tableau. CONTRÔLE + T. Cliquez sur OK. DATA, ou POWER QUERY si vous êtes dans une ancienne version, FROM TABLE. Cela s'appellera TABLE2. Appelons cela LOOKUPTABLE. Parfait. CLOSE & LOAD, CLOSE & LOAD TO, CRÉER UNIQUEMENT UNE CONNEXION.
Bien. Maintenant, nous avons nos deux éléments ici et je veux fusionner ces deux éléments. Donc, nous allons simplement aller à un nouvel endroit, puis DATA, GET DATA, COMBINE QUERIES, nous allons faire une MERGE, et le tableau de gauche sera TABLE1 - ce sont nos données d'origine - - et nous allons utiliser ce numéro ITEM et nous allons le marier avec le LOOKUPTABLE et ce numéro ITEM. Ce n'est vraiment pas intuitif, vous devez cliquer sur les ITEMS dans les deux cas pour définir la clé, et une jointure EXTÉRIEURE, TOUT DU PREMIER, CORRESPONDANT À PARTIR DE LA SECONDE, et voyez, il y en a 40% qui manquent dans le LOOKUPTABLE. Ce sont toutes de fausses données, mais les données d'origine manquaient également à 40% du LOOKUPTABLE. Vraiment un peu frustrant. Bien. Alors, voici notre numéro d'article, nos 2 champs INDEX, puis notre LOOKUPTABLE ici. JE'Je vais étendre cela et demander la DESCRIPTION. Très bien, vous voyez que nous avons un tas de nulls ici.
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.
Maintenant, hé, c'est le moment où je vous demande habituellement d'aller acheter mon livre mais, aujourd'hui, nous allons plutôt vous demander d'aller acheter le livre de Miguel. Miguel Escobar et Ken Puls ont écrit cet excellent livre sur M Is For (DATA) MONKEY - le meilleur livre sur Power Query. Allez vérifier ça.
Très bien, concluez: aujourd'hui est un très long épisode; nous avons une visionneuse, télécharge des données à partir d'un système où chaque élément est séparé par ALT + ENTRÉE et nous essayons de faire une RECHERCHEV pour chaque élément individuel; construit une solution aujourd'hui en utilisant Power Query, y compris l'outil de colonne structurée d'extrait comme; mais cela ne fonctionne que sur une liste, pas une table, j'ai donc dû utiliser la fonction TABLE.COLUMN pour convertir la table en liste.
Et bien Salut. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2151.xlsm
