CONCATENATEX in Power Query. La nuova funzione TEXTJOIN è fantastica. Puoi fare la stessa cosa con Power Query? Sì. Ora puoi.
Guarda un video
- Un visualizzatore scarica i dati da un sistema in cui ogni elemento è separato da Alt + Invio
- Bill: Perché lo fai? Visualizzatore: è il modo in cui eredito i dati. Voglio mantenerlo così.
- Bill: cosa vuoi fare con il 40% dei valori non nella tabella? Visualizzatore: nessuna risposta
- Bill: C'è un modo complicato per risolvere questo problema se si dispone degli ultimi strumenti di Power Query.
- Invece, una macro VBA per risolverlo: la macro dovrebbe funzionare fino a Excel 2007
- Invece di fare CERCA.VERT, esegui una serie di Trova e sostituisci con VBA
Trascrizione del video
Impara Excel da, Podcast Episode 2151.
Non so davvero come chiamarlo. Se sto cercando di attirare le persone che usano DAX, direi ConcatenateX in Power Query o solo le persone che usano Excel normale ma Office 365, direi TEXTJOIN in Power Query o, per essere completamente onesti, è un set super complesso di passaggi in Power Query per abilitare una soluzione super folle in Excel.
Hey. Bentornato al netcast. Sono Bill Jelen. Ebbene, ieri nell'episodio 2150, ho descritto il problema. Qualcuno ha inviato questo file in cui il loro sistema sta scaricando gli articoli che sono un ordine con avanzamenti di riga tra di loro. In altre parole, ALT + INVIO e vedi, WRAP TEXT è attivato e vogliono eseguire un CERCA.VERT in questa TABELLA DI RICERCA per ciascuno di questi elementi. Sono come, cosa? Perché stai facendo questo? Ma l'ho spiegato ieri. Proviamo solo a capire come farlo.
In realtà ho detto, beh, Power Query sarebbe il modo migliore per farlo, ma sono rimasto perplesso su come fare l'ultima parte. Ho detto, va bene se ogni articolo finisce nella propria riga? No, devono tornare in questa sequenza originale. Sono come, è orribile, ma, sul mio feed Twitter proprio la scorsa settimana, Tim Rodman, 27 settembre: "Finalmente leggendo questo libro," - immagino sia PowerPivot Alchemy - "e ha già ottenuto il suo desiderio di ConcatenateX. " Ero un furbo quando l'ho fatto, chiedendo PERHAPS ROMANX, ma probabilmente volevo davvero ConcatenateX, e quindi Tim mi ha avvisato che ora posso farlo in Power BI.
Quindi sono uscito con i miei amici, Rob Collie di Power Pivot Pro e Miguel Escobar, e, sai, sono entrambi autori di grandi libri. Ho entrambi questi libri, ma questa funzione è troppo nuova, non in nessuno dei due libri. Ho detto, ehi, voi ragazzi sapete come farlo? E Miguel vince il premio perché Miguel si è alzato presto questa mattina o ieri sera tardi - non sono sicuro di quale - e ha inviato il codice.
Va bene, quindi, ecco il piano in Power Query e questo è così complicato. Non scrivo mai un piano in Power Query. Vado a fare tutte le cose. Inizierò con i dati originali, aggiungerò una colonna INDICE in modo da poter mantenere gli articoli di un ordine insieme, SPLIT COLUMN in ROWS usando un LINEFEED. Questa è la seconda o la terza volta nel podcast che utilizzo questa nuova funzionalità. Quant'è fico. Avevo una seconda colonna INDICE in modo da poter ordinare gli elementi nella sequenza originale, quindi SALVA COME CONNESSIONE.
Quindi, arriveremo alla tabella CERCA, ne faremo una tabella, interrogheremo dalla tabella, SALVA COME CONNESSIONE - quella sarebbe stata la parte più semplice proprio lì - e quindi uniremo questa query e questa query in base all'elemento numero, tutti gli elementi dalla tabella di sinistra, questa è la tabella di sinistra, corrispondente da destra, sostituire i valori nulli con il numero dell'articolo. Siamo ancora in sospeso su cosa vogliamo fare quando qualcosa non viene trovato per qualche motivo. Ho posto questa domanda, ma la persona che ha inviato il file non risponde, quindi lo sostituirò con il numero dell'articolo. Si spera che la cosa giusta da fare sia aggiungere più elementi a LOOKUPTABLE in modo che non ci siano non trovati, ma eccoci qui, e quindi ordineremo per INDEX1 e INDEX2, quindi in questo modo,le cose sono tornate nella giusta sequenza e quindi questa era la parte che non riuscivo a capire come fare.
Raggrupperemo per INDICE1 facendo l'equivalente di un TEXTJOIN o ConcatenateX con il carattere 10 come separatore, come aggregatore e, ovviamente, questa è la parte che è la parte difficile ma è la parte che è davvero nuova qui in questa serie di passaggi. Quindi, se capisci cosa fa TEXTJOIN o puoi concettualizzare cosa avrebbe fatto ConcatenateX, lo stiamo facendo essenzialmente usando questo tipo di passaggio. Quindi, va bene. Quindi, proviamoci.
Quindi, inizieremo da qui. Ecco i nostri dati originali, ha un'intestazione. Quindi, ho intenzione di FORMATTARE COME TABELLA, CTRL + T, LA MIA TABELLA HA INTESTAZIONI, sì, e quindi useremo Power Query. Ora sono in Excel 2016 Office 365, quindi è qui nella parte sinistra della scheda DATI. Se sei solo in Excel 2016, non in Office 365, è nel mezzo: OTTIENI E TRASFORMA. Se sei in Excel 2010 o 2013, sarà la sua scheda qui chiamata Power Query e, se non hai quella scheda, dovrai scaricare quella scheda. Se sei su un Mac o Android o una qualsiasi delle altre versioni false di Excel, mi dispiace, nessuna query di alimentazione per te. Ottieni una versione Windows di Excel e provalo.
Va bene, quindi, faremo una query di alimentazione DA UNA TABELLA, va bene, e la prima cosa che farò è AGGIUNGERE una COLONNA INDICE e inizierò DA 1. Va bene , quindi, questo è essenzialmente l'ordine 1, l'ordine 2, l'ordine 3, l'ordine 4. Quindi sceglieremo questa colonna e, nella scheda TRASFORMA, andremo su SPLIT COLUMN, BY DELIMITER, e sono stati in grado di rilevare che si tratta di un LineFeed è il delimitatore. Mi piace che Power Query lo rilevi. Ora, perché Excel, testo in colonne, sì, testo in colonne non riesce a capire qual è il delimitatore? E ad ogni occorrenza divideremo in righe e useremo un carattere speciale. Va bene, quindi va tutto bene.
Ora guarda cosa succede qui. Abbiamo 999 righe ma ora ne abbiamo molte di più. Quindi, ogni articolo in quel numero d'ordine è ora la propria riga. Ora, la persona che ha posto questa domanda non vuole che sia la sua riga, ma dovremo fare in modo che sia la sua riga in modo da poter fare l'unione. Aggiungerò una nuova colonna INDICE qui. AGGIUNGI COLONNA, INDICE COLONNA, DA 1, e così abbiamo … questi sono essenzialmente i numeri d'ordine e quindi questi sono la sequenza all'interno dell'ordine perché ho determinato che, in seguito, questi saranno in un altro ordine. Non so in che ordine passano, ma eccoci qui.
Va bene, quindi, HOME, non il pulsante CHIUDI E CARICA ma il menu a discesa CHIUDI E CARICA e CHIUDI E CARICA SU. Non so perché ci vogliono 10 secondi per visualizzare questa finestra di dialogo la prima volta. Creeremo SOLO LA CONNESSIONE. Fare clic su OK. Bellissimo. Quindi questa è TABLE1, TABLE1.
Ora andremo al nostro LOOKUPTABLE. LOOKUPTABLE sarà facile da elaborare. Lo formatteremo come una tabella. CTRL + T. Fare clic su OK. DATA, o POWER QUERY se sei in una vecchia versione, FROM TABLE. Questo si chiamerà TABELLA2. Chiamiamolo LOOKUPTABLE. Perfetto. CLOSE & LOAD, CLOSE & LOAD TO, CREA SOLO CONNESSIONE.
Tutto a posto. Ora, abbiamo i nostri due pezzi qui e voglio unire questi due. Quindi, andremo solo in un nuovo punto e poi DATA, GET DATA, COMBINE QUERIES, faremo un MERGE e la tabella a sinistra sarà TABLE1 - questi sono i nostri dati originali - - e useremo questo numero ITEM e lo sposeremo fino a LOOKUPTABLE e quel numero ITEM. È davvero non intuitivo, devi fare clic su ELEMENTI in entrambi i casi per definire quale sia la chiave, e un'unione ESTERNA, TUTTO DAL PRIMO, CORRISPONDENTE DAL SECONDO, e, vedi, ce ne sono il 40% mancanti dal TABELLA DI RICERCA. Questi sono tutti dati falsi, ma i dati originali avevano anche il 40% mancante dal CERCHIO. Davvero un po 'frustrante. Tutto a posto. Quindi, ecco il nostro numero ITEM, i nostri 2 campi INDEX e quindi il nostro LOOKUPTABLE qui. IO'Vado a ESPANDERE quello e chiedere la DESCRIZIONE. Va bene, vedi che abbiamo un mucchio di null qui.
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.
Ora, ehi, questo è il punto in cui di solito ti chiedo di andare a comprare il mio libro ma, oggi, ti chiediamo invece di andare a comprare il libro di Miguel. Miguel Escobar e Ken Puls hanno scritto questo eccellente libro su M Is For (DATA) MONKEY - il miglior libro che ci sia su Power Query. Vai a controllare.
Va bene, concludi: oggi è un episodio davvero lungo; abbiamo un visualizzatore, scarica i dati da un sistema in cui ogni elemento è separato da ALT + INVIO e stiamo cercando di fare un CERCA.VERT per ogni singolo elemento; ha costruito una soluzione oggi utilizzando Power Query, incluso lo strumento a colonne strutturate di extract as; ma funziona solo su un elenco, non su una tabella, quindi ho dovuto utilizzare la funzione TABLE.COLUMN per convertire la tabella in un elenco.
Beh ciao. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2151.xlsm
