Editor Top
Puede ingresar datos en una hoja de Microsoft Excel o ejecutar un poco de código M en Microsoft Excel Power Query cuando necesite crear una tabla.
Actualización de datos en un Microsoft Excel Workbook es común, pero se encontrará con casos en los que puede reemplazar la entrada manual ejecutando un poco de código M en Power Query. Esta tarea es mejor cuando se crean tablas estáticas. Por ejemplo, puede hacer referencia a una tabla de porcentajes de comisión para calcular los montos de la comisión. Esos valores rara vez cambiarán. Usar código en Power Query es una buena alternativa al exportar esos datos a otra aplicación.
En este tutorial, le mostraré cómo generar y completar una tabla de comisiones estática en Power Query usando código M. El conjunto de datos incluirá un valor de referencia de ventas y una tasa de porcentaje correspondiente. Una vez que la tabla está en Excel, puede hacer referencia a los valores para calcular los montos de las comisiones.
VER: Google Workspace frente a Microsoft 365: un análisis en paralelo con lista de verificación (TechRepublic Premium)
Inicialmente, escribir el código M no es más rápido que ingresar los valores manualmente. Una vez que tenga la consulta en Power Query, puede modificar el código M según sea necesario y regenerar rápidamente todo el conjunto de datos en cualquier momento o incluso llamarlo desde otra aplicación.
Estoy usando microsoft 365 en un sistema Windows 10 de 64 bits, pero puede usar versiones anteriores a Excel 2010.
Puede descargar el archivo de demostración de Microsoft Excel para este tutorial.
Cómo abrir una consulta en blanco en Power Query
Debido a que estamos creando y completando el conjunto de datos en Power Query, debemos abrir una consulta en blanco. Para hacerlo, haga clic en la pestaña Datos y luego elija Iniciar Power Query Editor en el menú desplegable Obtener datos en el grupo Obtener y transformar datos.
Para agregar una consulta en blanco, haga clic con el botón derecho en el panel de consulta (a la izquierda) y seleccione Nueva consulta. En el submenú resultante, elija Otras fuentes. Luego, en el siguiente submenú, elija Consulta en blanco como se muestra en Figura A.
Figura A
Power Query abrirá la barra de fórmulas y nombrará la consulta Consulta1, que puede cambiar a Tasas de comisión. Simplemente haga doble clic en el nombre e ingrese el nuevo nombre desde el teclado.
Cómo generar una tabla usando #table en Power Query
Pocas de las características están disponibles porque no hay datos para manipular. En su lugar, usaremos el código M para generar el conjunto de datos. En la pestaña Inicio, haga clic en Editor de consultas avanzado en el grupo Consulta para abrir una ventana en blanco. Si lo desea, haga clic en el menú desplegable Opciones de visualización y elija Mostrar números de línea en el menú desplegable. Esto no es necesario, pero me referiré a los números de línea más adelante.
Ingrese el código a continuación como se muestra en Figura B. Si comete algún error, el editor de sintaxis en la parte inferior mostrará información. Como puede ver, no hay errores de sintaxis, así que haga clic en Listo para generar el conjunto de datos que se muestra en Figura C.
#table
(
type table
[
#"Benchmark" = Int64.Type,
#"Rate" = Number.Type
],
{
{0,00},
{800,.002},
{10000,.003},
{12000,.0035},
{15000,.004},
{20000,.055}
}
)Figura B
Figura C
El código es una versión simple de una función M #table; una revisión exhaustiva de #table está mucho más allá del alcance de este artículo. Las líneas 1 a 3 ejecutan la función de tabla. Las líneas 4 a 7 definen las columnas por nombre, punto de referencia y tasa, y tipo de datos. Las líneas 8 a 15 especifican los valores para ambas columnas. La primera columna genera los valores de referencia y la segunda genera las tasas correspondientes. La línea 16 completa la función cerrando el primer paréntesis de la línea 2.
Cómo cargar los datos en Excel
Después de generar el nuevo conjunto de datos, puede volver a cargarlo en Excel. Para hacerlo, haga clic en la opción Cerrar y cargar en el grupo Cerrar en la pestaña Inicio. Figura D muestra el nuevo conjunto de datos en uso. Como puede ver, las tarifas por debajo de $20,000 son bajas, pero una vez que una venta alcanza ese punto de referencia, las comisiones son mucho más altas.
Figura D
Es cierto que, en un ejemplo tan simple, usar Power Query parece una exageración. Inicialmente, no parece estar ahorrando tiempo o esfuerzo. Sin embargo, imagine que está trabajando con un conjunto de datos mucho más grande y la conveniencia se vuelve más clara.
Una vez que tenga la consulta original en Power Query, puede modificar rápidamente el código M y regenerar el conjunto de datos en cualquier momento mediante la creación de una función para pasar nuevos valores a través de parámetros. Esta tarea es simple, pero puede ser parte de un proceso mucho más grande.
