Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
Hemos analizado algunas transformaciones de Power Query en esta serie , pero ahora vamos mucho más allá. En esta publicación, analizamos las transformaciones simples de Power Query disponibles en los menús principales.
Desafortunadamente, no podemos cubrir todo en esta publicación; hay demasiadas opciones disponibles. Además, las transformaciones más complejas de Power Query merecen más atención y se incluirán en futuras publicaciones de esta serie. Sin embargo, al final de esto, tendrá cientos de transformaciones sencillas de Power Query en su kit de herramientas.
Tabla de contenido
- Pestaña Inicio
- Administrar columnas
- Reducir filas
- Clasificar
- Otras secciones de la pestaña Inicio
- Transformar
- Mesa
- Cualquier columna
- Columna de texto
- Columna numérica
- Columna de tiempo de datos
- Añadir columna
- Transformar frente a agregar pestañas de columna
- General
- Filtración
- Otras transformaciones de Power Query
- Haga clic derecho en encabezado de columna
- Icono de tabla
- Cambiar tipo de datos
- Haga clic derecho en valor individual
- Envolver
Microsoft hizo que los menús de Power Query fueran contextuales. Dependiendo del elemento seleccionado, las opciones del menú aparecen, desaparecen o se desactivan en consecuencia. Por ejemplo, si selecciona una columna con un tipo de datos de texto, las transformaciones relacionadas con la fecha y el número no están disponibles.
Una parte confusa de los menús es que la misma acción aparece en varios lugares. Esto se debe a que los diseñadores de la interfaz de usuario intentaron agrupar elementos similares. Por lo tanto, dado que algunas transformaciones se pueden categorizar en varios lugares, los botones se incluyen en todas esas ubicaciones. Por ejemplo, Usar la primera fila como encabezados está disponible en las secciones Transformación de inicio y Tabla de transformación de la cinta.
Mientras trabaja con Power Query, es probable que encuentre otros menús que no se tratan en esta publicación. Pero esto debería proporcionarle un buen comienzo.
Como Power Query se actualiza continuamente, es posible que las capturas de pantalla parezcan ligeramente diferentes a su versión de Excel. Y es posible que tengas íconos que yo no tengo, y viceversa.
Sin más, comencemos a ver lo que tenemos a nuestra disposición.
Pestaña Inicio
Primero, veamos la pestaña Inicio .
La pestaña Inicio contiene configuraciones de consultas y aplicaciones (por ejemplo, Actualizar vista previa), junto con transformaciones comunes. En esta publicación solo estamos analizando transformaciones; por lo tanto, solo cubriremos las secciones Administrar columna , Reducir filas , Ordenar , Transformar y Combinar del menú Inicio.
Administrar columnas
La sección Administrar columnas contiene dos íconos, cada uno de los cuales incluye dos opciones adicionales.
Elegir columnas : selecciona las columnas que se conservarán en la consulta.
Ir a columna : selecciona una columna específica. En realidad, esto no es una transformación, ya que selecciona una columna en la ventana de vista previa sin agregar ningún paso de transformación a la consulta. Se podría lograr lo mismo haciendo clic con el mouse en el encabezado de una columna.
Eliminar columnas : elimina las columnas seleccionadas.
Eliminar otras columnas : elimina todas las columnas excepto las seleccionadas.
Si ha estado leyendo detenidamente, es posible que se pregunte cuál es la diferencia entre Elegir columnas y Eliminar otras columnas . Ambos comandos mantienen las columnas seleccionadas, entonces, ¿cuál es la diferencia? En resumen, ¡nada! No hay diferencia; El código M es exactamente el mismo.
Sin embargo, desde la perspectiva del proceso de pensamiento del usuario, existe una diferencia. Mira estas dos frases:
- "Estas son las columnas que quiero conservar".
- "Quiero eliminar todas las columnas excepto estas".
Tienen un enfoque sutilmente diferente, pero conducen al mismo resultado en Power Query. Entonces, puedo apreciar por qué existen ambos incluso si proporcionan el mismo resultado. Esto también ilustra cuántos menús de Power Query funcionan; se basan en lo que un usuario podría estar intentando lograr.
Quitar o Quitar Otros: ¿importa?
Ahora que te tengo pensando un poco. Aquí tienes una pregunta… ¿las siguientes son iguales?
- Seleccione la primera columna y haga clic en Eliminar otras columnas
- Seleccione todas las columnas excepto la primera y haga clic en Eliminar columnas
¿Qué opinas? ¿Son lo mismo?
La respuesta es… No, no son iguales. Déjame explicarte por qué.
Supongamos que tenemos una tabla con tres columnas denominadas Columna1, Columna2 y Columna3.
Si seleccionamos Columna1 y eliminamos las otras columnas. El Código M se ve así:
= Table.SelectColumns(Source,{"Column1"})Sin embargo, si seleccionamos la Columna 2 y la Columna 3 y las eliminamos (es decir, dejando la Columna 1 restante), el Código M se ve así:
= Table.RemoveColumns(Source,{"Column2", "Column3"})Esta no es solo una línea de código diferente que logra el mismo resultado. Por ejemplo, supongamos que se agrega una nueva columna a los datos de origen. La nueva columna podría llamarse Columna4. Cuando actualizamos la consulta como de costumbre. ¿Qué va a pasar?
- Eliminar otras columnas garantiza que la columna 1 sea la única columna restante (es decir, la columna 4 también se ha eliminado).
- Quitar columnas elimina la Columna 2 y la Columna 3, lo que significa que quedan la Columna 1 y la Columna 4.
Este concepto es importante y se encuentra repetidamente en las opciones de Power Query. A medida que aplicamos pasos que tienen las versiones Quitar y Quitar otros, debemos pensar en lo que sucederá si alguien agrega más columnas; esto garantiza que elija la mejor opción para su escenario.
Reducir filas
Reducir filas elimina filas de datos manteniendo o eliminando filas que cumplen criterios específicos.
Los primeros tres elementos de ambos menús conducen a otra ventana, que nos permite establecer el número de filas a mantener o eliminar.
- Mantener las filas superiores
- Mantener las filas inferiores
- Mantener rango de filas
- Eliminar filas superiores
- Eliminar filas inferiores
- Eliminar filas alternativas
Como ejemplo, la ventana para Mantener rango de filas tiene este aspecto:
O Eliminar filas alternativas se ve así:
Los elementos restantes del menú no conducen a otro cuadro de diálogo.
- Mantener duplicados
- Mantener errores
- Eliminar duplicados
- Eliminar filas en blanco
- Eliminar errores
Para estas transformaciones, los pasos se agregan automáticamente sin necesidad de más aportaciones del usuario.
Clasificar
La transformación Ordenar es similar a la función de clasificación de Excel, pero hay una diferencia sutil en cómo se aplica el orden de clasificación.
Supongamos que tenemos algunos datos básicos (ver captura de pantalla a continuación):
Si ordenamos por Columna1 y luego ordenamos por separado por Columna2, ¿qué debería pasar? En Excel, si aplicáramos dos tipos diferentes, se ordenarían por Columna 2, ya que esa fue la última columna ordenada. Pero no es así con Power Query.
Si recuerdas, Power Query reduce los pasos para ser lo más eficiente posible. Ordenar por Columna 1 y luego por Columna 2 se aplica como un solo paso y el resultado se verá así.
Observe que el resultado se ordena primero por Columna1 y luego por Columna2. El orden de clasificación se puede ver mediante los números pequeños junto a las flechas (resaltados en la captura de pantalla anterior)
Por lo tanto, la ordenación de Power Query es como la funcionalidad que encontramos en la ventana Ordenar de Excel.
Si hay al menos un paso entre la primera y la segunda clasificación, Power Query no los combinará, ya que se consideran pasos separados.
Otras secciones de la pestaña Inicio
La sección Transformar de la pestaña Inicio contiene botones para transformaciones que también se encuentran dentro de la pestaña Transformar. Esta publicación ya es lo suficientemente larga, por lo que no duplicaremos estas descripciones. En su lugar, puede encontrar todos los detalles en la sección siguiente.
Dentro de la sección Combinar , hay dos transformaciones importantes; fusionar y agregar.
- Las consultas de combinación son una transformación avanzada para unir datos de diferentes tablas en una sola vista. Obtenga más información sobre esta transformación aquí: https://exceloffthegrid.com/power-query-lookup-values-using-merge/
- Agregar consultas es otra transformación avanzada para apilar múltiples consultas del mismo diseño en una sola vista. Obtenga más información aquí: https://exceloffthegrid.com/power-query-combine-append-queries/
Transformar
A continuación, dirijamos nuestra atención a la pestaña Transformar .
Las secciones que estamos analizando incluyen Tabla , Cualquier columna , Columna de texto , Columna numérica y Columna de fecha y hora .
La sección titulada Columna estructurada se relaciona con tipos de datos personalizados. No los cubriremos en esta publicación; por lo tanto, para obtener más información, consulte esto: https://www.myonlinetraininghub.com/power-query-custom-data-types .
Mesa
La sección Tabla aplica transformaciones a toda la tabla, no solo a las columnas seleccionadas.
Agrupar por es un método para resumir los datos de varias maneras. Al hacer clic en el icono se abre el cuadro de diálogo Agrupar por.
Desde este cuadro de diálogo, podemos seleccionar las columnas para resumir y los cálculos de agregación para realizar en otras columnas.
Group By es una transformación muy poderosa. Consulte esta publicación para ver ejemplos más avanzados de esta transformación: https://exceloffthegrid.com/power-query-group-by/
Usar la primera fila como encabezados convierte la primera fila de datos en el encabezado de la columna. A menudo usamos esto al importar datos de un rango con nombre, o en cualquier momento en que Power Query no pueda adivinar cuáles deberían ser los encabezados de las columnas. Dependiendo de nuestra configuración predeterminada, Power Query puede aplicar automáticamente este paso al importar datos.
Usar encabezados como primera fila degrada el encabezado para convertirse en la primera fila de datos.
Transponer funciona de manera similar a la función de Excel; cambia las columnas por filas y viceversa.
Reverse Rows coloca las filas en el orden opuesto.
Count Rows desglosa hasta un único número que proporciona el recuento del número de filas de la consulta.
Cualquier columna
La sección Cualquier columna contiene muchas transformaciones útiles.
Tipo de datos : utilice esta opción para cambiar el tipo de datos de la columna seleccionada. Tiene el mismo efecto que hacer clic en el icono de tipo de datos dentro del encabezado de la columna.
Detectar tipo de datos : utilice esta opción para que Power Query adivine el tipo de datos analizando los datos de la columna. Dependiendo de su configuración predeterminada, este paso puede aplicarse automáticamente al importar datos. Recuerde, Power Query no siempre lo hará bien, por lo que suele ser mejor seleccionar explícitamente el tipo de datos usando la opción anterior.
Cambiar nombre pone el texto del encabezado de la columna seleccionada en modo de edición. Esto es lo mismo que hacer doble clic en el encabezado de la columna.
Reemplazar valores es muy útil; Funciona como la funcionalidad Buscar y reemplazar de Excel. Al hacer clic en el icono se abre la ventana Reemplazar valores. En la captura de pantalla siguiente, la sección Opciones avanzadas se ha ampliado.
Reemplazar errores funciona igual que Reemplazar valores. Sin embargo, sólo se aplica a los datos que contienen errores.
Rellenar hacia arriba/abajo rellena cada celda nula de la columna con el valor superior para Rellenar hacia abajo o inferior para Rellenar hacia arriba.
La columna dinámica girará los datos según la columna seleccionada.
Las columnas sin pivote se pueden utilizar para convertir información en una tabla de archivo plano. Puede encontrar más información sobre esta transformación aquí: https://exceloffthegrid.com/power-query-unpivot-data/
Mover se utiliza para mover columnas hacia la izquierda o hacia la derecha en la pantalla. Esto tiene el mismo efecto que arrastrar la columna con el mouse.
Convertir a lista convierte las columnas seleccionadas en una lista de valores; esto es similar a profundizar en una tabla. Las listas son un método útil para transformaciones más avanzadas.
Columna de texto
Las funciones para transformar texto están contenidas en la sección Columna de texto .
Dividir columna se utiliza para separar una parte de una cadena de texto de otra cadena de texto. La división crea columnas adicionales para cada división de la cadena.
Hay muchas opciones para dividir texto. Los tres elementos principales abren una nueva ventana.
- Por delimitador
- Por puesto
- Por número de caracteres
Como ejemplo, la ventana Dividir columna por delimitador tiene este aspecto. En la captura de pantalla, la sección de opciones avanzadas se amplía para mostrar aún más transformaciones.
Los cuatro elementos inferiores del submenú Dividir columna no tienen una ventana de configuración adicional.
- De mayúsculas a minúsculas
- De minúsculas a mayúsculas
- De dígito a no dígito
- De no dígito a dígito
Estas cuatro transformaciones se aplican directamente a las columnas seleccionadas sin más opciones.
El formato tiene opciones para:
- Cambiar la apariencia del texto (p. ej., mayúsculas, minúsculas)
- Recortar y limpiar para eliminar espacios y caracteres no imprimibles
- Agregar prefijo y Agregar sufijo agregan una cadena fija al inicio o al final de un texto existente.
Todas estas son transformaciones útiles para limpiar datos antes de realizar otras transformaciones.
Fusionar columnas requiere que se seleccionen dos o más columnas. Al hacer clic en el icono se abre la ventana Fusionar columnas, que contiene la configuración adicional.
El menú desplegable del separador proporciona algunas opciones predeterminadas (dos puntos, coma, punto y coma del signo igual, espacio y tabulador), junto con la posibilidad de seleccionar un separador personalizado.
Extraer contiene opciones para conservar partes de una cadena de texto y descartar otras. Las opciones incluyen:
- Longitud (similar a la función LEN en Excel)
- Primeros caracteres (similar a la función IZQUIERDA en Excel)
- Últimos caracteres (similar a la función DERECHA en Excel)
- Rango
- Texto antes del delimitador
- Texto después del delimitador
- Texto entre delimitadores
Parse proporciona filtrado de texto avanzado para formatos de archivos XML y JSON.
Columna numérica
No importa en qué industria trabaje, estoy seguro de que hay transformaciones en la sección Columna numérica que le serán útiles. Sin embargo, dada la variedad de transformaciones, es poco probable que las utilice todas.
Estadísticas profundiza en los valores para proporcionar información estadística sobre una columna (por ejemplo, suma, mínimo, máximo, etc.).
Standard realiza cálculos sobre los números. Por ejemplo, al seleccionar Agregar se abre la ventana Agregar. Al ingresar un valor y hacer clic en Aceptar, se agrega ese valor a cada número de la columna.
El redondeo proporciona opciones para redondear hacia arriba, hacia abajo o hasta un número específico de decimales. Tenga en cuenta que Power Query utiliza el redondeo bancario como método de redondeo predeterminado .
La información verifica si un valor es impar, par, positivo, negativo o cero.
Las transformaciones científicas y de trigonometría son útiles para las matemáticas más avanzadas.
Columna de tiempo de datos
La sección Columna de fecha y hora contiene numerosas transformaciones para abordar fechas, horas y duraciones. En Excel, es complicado trabajar con fechas, pero en Power Query, ahora tenemos muchas herramientas para convertir nuestros datos en algo más utilizable.
Date contiene muchas opciones para transformar fechas. Todas las opciones se muestran en la captura de pantalla anterior.
Algunas de mis transformaciones favoritas de esta sección son:
- Inicio de año (Fecha)
- Fin de año (Fecha)
- Fin de mes (Mes)
- Nombre del mes (Mes)
- Semana del año (Semana)
- Día de la semana (Día)
Básicamente, es posible generar todas las categorizaciones imaginables a partir de cualquier fecha.
Hora contiene las opciones para obtener las horas, minutos, segundos, etc., de una columna formateada como hora o FechaHora.
Duración contiene opciones para convertir duraciones en días, años, minutos, segundos, etc.
Añadir columna
Transformar frente a agregar pestañas de columna
Sólo tómate unos segundos para comparar las cintas Transformar y Agregar columna ; Hay muchos íconos que suenan similares aquí. Entonces, ¿cuál es la diferencia entre ellos?
Al seleccionar un icono de la pestaña Agregar columna (lo ha adivinado), se agregará una columna. Los iconos de la pestaña Transformar transforman las columnas seleccionadas sin agregar nuevas columnas.
No duplicaremos las descripciones; Para saber qué hace un ícono específico, lea la descripción equivalente en la sección Transformar anterior.
Agregar columna tiene algunos botones únicos que no se encuentran en Transformar; miremos estos.
General
La sección General proporciona funciones más avanzadas.
La columna de ejemplos utiliza la lógica de Power Query para identificar patrones de datos y aplicarlos a las filas restantes. El siguiente ejemplo demuestra que al proporcionar el resultado deseado para la primera fila, Power Query adivina el patrón y lo aplica a las filas restantes.
En la parte superior se muestra la función que utilizó Power Query para transformar el texto. Si los valores no son los que desea, siga proporcionando ejemplos para que Power Query funcione.
La columna personalizada nos permite utilizar fórmulas de código M en los datos. Cubriremos algunas versiones básicas de estos en esta publicación: https://exceloffthegrid.com/power-query-formulas/ .
Invocar función personalizada nos permite ejecutar nuestras propias funciones personalizadas de Power Query. Esta es una técnica avanzada. Mire esta publicación para obtener más detalles: https://exceloffthegrid.com/power-query-custom-functions/ .
Columna de índice agrega una columna con un número de índice. Esto también es útil para técnicas más avanzadas.
Columna duplicada crea una versión duplicada de la columna seleccionada.
La columna condicional nos permite usar la lógica para generar valores que cumplan con criterios específicos. Piense en ello como la versión básica de Power Query de la función SI de Excel.
Filtración
Uno de los tipos de transformaciones más comunes es filtrar una columna.
El menú de filtro es contextual; por lo tanto, los elementos del menú que se muestran dependen del tipo de datos de la columna. Por ejemplo, la siguiente captura de pantalla muestra el menú de una columna de texto.
Las opciones de filtro son similares pero no iguales a las que se encuentran en Excel. Por ejemplo, en Excel existe un filtro para “Top 10…”, para el cual no existe un equivalente en Power Query. Esto no se debe a que Power Query carezca de funcionalidad, sino que existen diferentes formas de lograr el mismo resultado.
Otras transformaciones de Power Query
También se pueden encontrar transformaciones comunes en otros menús.
Haga clic derecho en encabezado de columna
Al hacer clic derecho en el encabezado de la columna se muestra un menú contextual. Mire el ejemplo siguiente: este es el menú de una columna de texto.
Todas estas transformaciones están disponibles en la cinta principal, pero el menú contextual proporciona una forma más rápida de acceder a muchas transformaciones comunes.
Icono de tabla
En la parte superior izquierda de la tabla hay un pequeño icono de tabla. Haga clic en ese icono para revelar las transformaciones de tablas más populares.
Todas estas transformaciones están disponibles en el menú principal.
Cambiar tipo de datos
Al hacer clic en el icono a la izquierda del encabezado de la columna, se muestra el menú para cambiar el tipo de datos de la columna.
Haga clic derecho en valor individual
Finalmente, al hacer clic derecho en cualquier celda dentro de la ventana de vista previa también se accede a un menú contextual.
El siguiente ejemplo muestra las opciones para una columna de texto.
Envolver
Vaya, eso fue mucho para asimilar.
Hemos visto que hay cientos de transformaciones diferentes de Power Query disponibles. Pero no tenemos que recordarlos todos; solo necesitamos saber de qué es capaz Power Query.
Si lo necesitas, puedes volver a esta publicación nuevamente y ver las opciones.
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta