CONCATENATEX en Power Query. La nueva función TEXTJOIN es asombrosa. ¿Puedes hacer lo mismo con Power Query? Si. Ahora usted puede.
Ver video
- Un espectador descarga datos de un sistema donde cada elemento está separado por Alt + Enter
- Bill: ¿Por qué haces esto? Visor: Así es como heredo los datos. Yo quiero mantenerlo así.
- Bill: ¿Qué quieres hacer con el 40% de los valores que no están en la tabla? Visor: Sin respuesta
- Bill: Hay una forma complicada de resolver esto si tiene las últimas herramientas de Power Query.
- En su lugar, una macro de VBA para resolverlo: la macro debería funcionar hasta Excel 2007
- En lugar de hacer BUSCARV, haga una serie de Buscar y reemplazar con VBA
Transcripción del video
Aprenda Excel de Podcast Episodio 2151.
Realmente no sé cómo llamar a este. Si estoy tratando de atraer a las personas que usan DAX, diría ConcatenateX en Power Query, o solo las personas que usan Excel normal pero Office 365, diría TEXTJOIN en Power Query, o, para ser completamente honesto, es un conjunto súper complejo de pasos en Power Query para habilitar una solución súper loca en Excel.
Oye. Bienvenido de nuevo al netcast. Soy Bill Jelen. Bueno, ayer en el episodio 2150, describí el problema. Alguien envió este archivo donde su sistema está descargando los artículos que son un pedido con avances de línea entre ellos. En otras palabras, ALT + ENTER, y mira, WRAP TEXT está activado, y quieren hacer una VLOOKUP en este LOOKUPTABLE para cada uno de esos elementos. Yo soy como que ¿Por qué estás haciendo esto? Pero cubrí eso ayer. Intentemos descubrir cómo hacer esto.
De hecho, dije, bueno, Power Query sería la mejor manera de hacer esto, pero me quedé perplejo sobre cómo hacer la última parte. Dije, ¿está bien si cada elemento termina en su propia fila? No, tienen que volver a esta secuencia original. Estoy como, eso es horrible, pero, en mi cuenta de Twitter la semana pasada, Tim Rodman, 27 de septiembre: "Finalmente leyendo este libro", supongo que es PowerPivot Alchemy, "y ya obtuvo su deseo de ConcatenateX". " Estaba siendo un listillo cuando hice esto, pidiendo QUIZÁS ROMANX, pero probablemente realmente quería ConcatenateX, por lo que Tim me advirtió que ahora puedo hacer eso en Power BI.
Entonces, salí con mis amigos, Rob Collie en Power Pivot Pro y Miguel Escobar, y, ya sabes, ambos son autores de grandes libros. Tengo ambos libros, pero esta característica es demasiado nueva, no en ninguno de los libros. Dije, oye, ¿saben cómo hacer esto? Y Miguel gana el premio porque Miguel se levantó temprano esta mañana o anoche tarde (no estoy seguro de cuál) y envió el código.
Muy bien, aquí está el plan en Power Query y este es muy complicado. Nunca escribo un plan en Power Query. Solo voy a hacer todas las cosas. Comenzaré con los datos originales, agregaré una columna de ÍNDICE para que podamos mantener juntos los elementos de un pedido, DIVIDIR COLUMNA a FILAS usando un SALTO DE LÍNEA. Esta es la segunda o tercera vez en el podcast que utilizo esta nueva función. Cuan genial es eso. Tenía una segunda columna INDICE para poder ordenar los elementos en la secuencia original y luego GUARDAR COMO CONEXIÓN.
Luego, llegaremos a la tabla LOOKUP, la convertiremos en una tabla, consulta desde la tabla, GUARDAR COMO CONEXIÓN, esa iba a ser la parte más fácil allí mismo, y luego fusionaremos esta consulta y esta consulta basada en el elemento número, todos los elementos de la tabla de la izquierda, esta es la tabla de la izquierda, coincidiendo desde la derecha, reemplace los nulos con el número de elemento. Todavía estamos en el aire sobre lo que queremos hacer cuando algo no se encuentra por alguna razón. Hice esta pregunta, pero la persona que envió el archivo no responde, así que lo reemplazaré con el número de artículo. Con suerte, lo correcto es agregar más elementos al LOOKUPTABLE para que no haya ningún no encontrado, pero aquí estamos, y luego vamos a ordenar por ÍNDICE1 e ÍNDICE2, de esa manera,las cosas están de nuevo en la secuencia correcta y luego esta fue la parte que no pude averiguar cómo hacer.
Vamos a agrupar por INDEX1 haciendo el equivalente de TEXTJOIN o ConcatenateX con el carácter 10 como separador, como agregador y, por supuesto, esta es la parte que es la parte difícil, pero es la parte que es realmente nueva aquí en este conjunto de pasos. Entonces, si comprende lo que hace TEXTJOIN o puede conceptualizar lo que habría hecho ConcatenateX, básicamente lo estamos haciendo usando este tipo de paso. Entonces, está bien. Entonces, intentémoslo.
Entonces, vamos a comenzar aquí. Aquí están nuestros datos originales, tiene un encabezado. Entonces, voy a FORMATEAR COMO TABLA, CONTROL + T, MI TABLA TIENE ENCABEZADOS, sí, y luego usaremos Power Query. Ahora, estoy en Excel 2016 Office 365, por lo que está aquí en la parte izquierda de la pestaña DATOS. Si solo está en Excel 2016, no en Office 365, está en el medio: OBTENER Y TRANSFORMAR. Si está en Excel 2010 o 2013, tendrá su propia pestaña aquí llamada Power Query, y si no tiene esa pestaña, tendrá que descargarla. Si está en una Mac o Android o cualquiera de las otras versiones falsas de Excel, lo siento, no hay Power Query para usted. Obtén una versión de Excel para Windows y pruébalo.
Muy bien, entonces, vamos a hacer una Power Query DESDE UNA TABLA, de acuerdo, y lo primero que voy a hacer es AGREGAR una COLUMNA DE ÍNDICE y empezar desde 1. Muy bien , entonces, esto es esencialmente orden 1, orden 2, orden 3, orden 4. Luego elegiremos esta columna y, en la pestaña TRANSFORMAR, vamos a DIVIDIR COLUMNA, POR DELIMITADOR, y pudieron detectar que es un LineFeed es el delimitador. Me encanta que Power Query esté detectando esto. Ahora, ¿por qué Excel, texto a columnas, sí, texto a columnas no determina cuál es el delimitador? Y cada vez que ocurra lo vamos a DIVIDIR EN FILAS, Y USANDO CARÁCTER ESPECIAL. Muy bien, todo eso es bueno.
Ahora mira lo que pasa aquí. Tenemos 999 filas, pero ahora tenemos muchas más que eso. Entonces, cada artículo en ese número de orden ahora es su propia fila. Ahora, la persona que hizo esta pregunta no quiere que sea su propia fila, pero vamos a tener que hacer que sea su propia fila para poder hacer la unión. Voy a agregar una nueva columna INDICE aquí. AÑADIR COLUMNA, COLUMNA DE ÍNDICE, DESDE 1, y así tenemos… estos son esencialmente los números de orden y luego estos son la secuencia dentro del orden porque he determinado que, más adelante, estos estarán en algún otro orden. No sé a qué orden cambiarán, pero aquí estamos.
Muy bien, entonces, INICIO, no el botón CERRAR Y CARGAR, sino el menú desplegable CERRAR Y CARGAR, y CERRAR Y CARGAR A. No sé por qué tardan 10 segundos en mostrar este cuadro de diálogo la primera vez. SOLO CREAREMOS CONEXIÓN. Haga clic en Aceptar. Hermosa. Así que eso es TABLE1, TABLE1.
Ahora, vamos a ir a nuestro LOOKUPTABLE. LOOKUPTABLE será fácil de procesar. Vamos a formatear esto como una tabla. CONTROL + T. Haga clic en Aceptar. DATA o POWER QUERY si está en una versión anterior, FROM TABLE. Esto se llamará TABLE2. Llamémoslo LOOKUPTABLE. Perfecto. CERRAR Y CARGAR, CERRAR Y CARGAR A, SOLO CREAR CONEXIÓN.
Bien. Ahora, tenemos nuestros dos bits aquí y quiero fusionar esos dos. Entonces, vamos a ir a un nuevo lugar y luego DATOS, OBTENER DATOS, COMBINAR CONSULTAS, vamos a hacer una FUSIÓN, y la tabla de la izquierda será TABLA1, esos son nuestros datos originales. - y vamos a utilizar este número de ARTÍCULO y vamos a casarlo con el LOOKUPTABLE y ese número de ARTÍCULO. Es realmente no intuitivo, tiene que hacer clic en los ELEMENTOS en ambos casos para definir cuál es la clave, y una combinación EXTERIOR, TODO DESDE EL PRIMERO, COINCIDIR CON EL SEGUNDO, y, vea, hay un 40% de estos que faltan en el TABLA DE BÚSQUEDA. Todos estos son datos falsos, pero a los datos originales también les faltaba un 40% del LOOKUPTABLE. Realmente un poco frustrante. Bien. Entonces, aquí está nuestro número de ARTÍCULO, nuestros 2 campos de ÍNDICE y luego nuestro LOOKUPTABLE aquí. YO'Voy a EXPANDIR eso y pedir la DESCRIPCIÓN. Muy bien, ves que tenemos un montón de nulos aquí.
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.
Ahora, oye, este es el punto en el que normalmente te pido que vayas a comprar mi libro pero, hoy, te pido que vayas a comprar el libro de Miguel. Miguel Escobar y Ken Puls escribieron este excelente libro sobre M es para (DATA) MONKEY, el mejor libro que existe sobre Power Query. Ve a ver eso.
Muy bien, concluya: hoy es un episodio muy largo; tenemos un visor, descarga datos de un sistema donde cada elemento está separado por ALT + ENTER y estamos tratando de hacer una BUSCARV para cada elemento individual; construyó una solución hoy utilizando Power Query que incluye la herramienta de columna estructurada de extraer como; pero eso solo funciona en una lista, no en una tabla, así que tuve que usar la función TABLE.COLUMN para convertir la tabla en una lista.
Este hola. Quiero agradecerles por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2151.xlsm
