Power Query y formato de código postal

Power Query es una herramienta increíble y me encanta aprender sobre ella. En el Editor de Power Query, al hacer clic en los íconos de comando, Excel en realidad está escribiendo código M detrás de escena. El código M tiene muchas más funciones de las que están disponibles en la cinta. Entonces, lo que debes tener en cuenta es que si estás tratando de lograr algo y no ves un ícono de comando, investiga las funciones porque es muy probable que encuentres algo útil. En esta publicación, ilustraré esto agregando ceros a la izquierda de un código postal.

Objetivo

Antes de llegar demasiado lejos, confirmemos nuestro objetivo aquí. Digamos que estamos usando Power Query para importar y limpiar algunos datos. Podemos lograr todo lo que necesitamos haciendo clic en los íconos de comando y casi hemos terminado… excepto por una pequeña cosa. Parece que no podemos encontrar un comando que formatee los códigos postales con ceros a la izquierda.

Buscamos en la cinta del Editor de consultas, hacemos clic en todos los íconos, hacemos clic derecho en todos los lugares que se nos ocurren y nada. Ahora bien, este es el punto de esta publicación. No todas las funciones de PQ están disponibles en la cinta. Por lo tanto, tendremos que arremangarnos y profundizar en las funciones M. ¿Listo? ¡Yo también!

Detalles

Para realizar esta tarea, realizaremos estos pasos básicos:

• Obtener los datos en PQ
• Cremalleras con cero a la izquierda
• Enviar resultados a Excel

Hagámoslo.

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.

Obtener los datos en PQ

Primero, necesitamos ingresar los datos en el editor de Power Query. Esto se hace seleccionando cualquier celda de la tabla y haciendo clic en Datos De tabla/rango . Luego, Excel muestra el Editor de Power Query, como se muestra a continuación.

Con nuestros datos en PQ, es hora de dar el siguiente paso.

Cremalleras con cero a la izquierda

Ahora, nos gustaría que nuestra columna Zip muestre un cero a la izquierda cuando sea necesario. Por ejemplo, en lugar de 2914 queremos 02914. Como ocurre con casi cualquier cosa, existen varias formas de realizar esta tarea.

Si eres como yo, tu primer instinto es empezar a explorar los iconos de comando. Al hacer clic en ellos, buscando el comando Formato, Rellenar, Reemplazar valores, Tipo de datos, Extraer, Columna de ejemplos, Estándar y más, parece que no podemos encontrar nada que funcione.

Una opción sería hacer una columna condicional, donde intentamos determinar la longitud del zip y concatenar un cero inicial si la longitud es 4. Otra posibilidad es enviar los resultados a Excel tal como están y luego manejar el formato en Excel con un formato personalizado 00000. Y otra posibilidad más es utilizar una función M integrada diseñada exactamente para este propósito. Probemos esa opción.

Primero, necesitamos cambiar el tipo de datos de la columna Zip de un número a un texto. Para hacer esto, seleccionamos la columna Zip y luego hacemos clic en Transformar Tipo de datos Texto .

En segundo lugar, debemos crear una nueva columna personalizada haciendo clic en Agregar columna Columna personalizada . Esto abre el cuadro de diálogo personalizado, como se muestra a continuación.

Ahora, en este punto, no tenemos idea de qué función M puede ayudar, o si existe alguna. Entonces, necesitamos investigar un poco. Hacemos clic en el enlace Más información sobre las fórmulas de Power Query en el cuadro de diálogo Columna personalizada y aparece la página de ayuda, como se muestra a continuación.

Esta página proporciona una descripción general básica, pero hay un enlace clave si se desplaza un poco hacia abajo llamado Categorías de fórmulas de Power Query, como se muestra a continuación.

Haga clic en este vínculo y encontrará la página de referencia de la función Power Query M , que se muestra a continuación.

Muestra las categorías de funciones M y sería una buena página para marcar como referencia rápida. En este caso, buscamos una función que opere en nuestro código postal de texto, por lo que hacemos clic en el enlace Funciones de texto . Podemos desplazarnos hacia abajo en la página, visualizando cada función y leyendo una breve descripción. A medida que nos desplazamos hacia abajo, vemos la función Text.PadStart , que, según su descripción, devuelve un valor de texto rellenado al principio. Eso suena como lo que queremos hacer, así que hacemos clic en el enlace de la función.

Ahora vemos los detalles de la función y podemos aprender sobre sus argumentos. que en este caso son text , length y pad . Una vez que tengamos una comprensión básica de la función y los argumentos, debemos intentarlo. Entonces, regresamos al cuadro de diálogo Columna personalizada, escribimos la función y definimos los argumentos. Es importante tener en cuenta que las funciones distinguen entre mayúsculas y minúsculas en Power Query. Esto es diferente de la función de Excel, que no distingue entre mayúsculas y minúsculas.

Entonces, en el cuadro de diálogo Columna personalizada, ingresamos el nombre de nueva columna que deseamos, ZipCode. Luego, comenzamos a escribir la fórmula escribiendo =Text.PadStart( y nos aseguramos de usar exactamente las letras mayúsculas y minúsculas de la página de detalles de la función.

Nuestro primer argumento es texto , que es la columna del código postal. Entonces, hacemos doble clic en Zip en el cuadro de lista Columnas disponibles y vemos que Excel ingresa [Zip] entre corchetes. Luego escribimos una coma y estamos listos para el siguiente argumento.

El siguiente argumento es longitud , por lo que ingresamos 5 para forzar que todos los códigos postales tengan 5 caracteres. Introducimos una coma y estamos listos para nuestro último argumento.

El argumento final es pad y dado que nuestro código postal es una cadena de texto, debemos ingresar un valor de texto entre comillas. Entonces ingresamos “0” y cerramos la función de la siguiente manera:

=Texto.PadStart([Zip],5,"0")

El cuadro de diálogo Columna personalizada actualizado se muestra a continuación.

En la parte inferior del cuadro de diálogo, vemos una marca verde que indica que no se han detectado errores de sintaxis, por lo que hacemos clic en Aceptar.

Sí… funciona, como se muestra a continuación.

Nota: ¡Gracias a Mynda que escribió sobre esta función y mostró cómo encerrar el argumento de texto entre comillas! Si desea obtener más información sobre Power Query de Mynda, consulte su curso Power Query para Excel … que está disponible para obtener crédito CPE si lo necesita.

Con nuestra nueva columna ZipCode, ahora podemos eliminar la columna Zip original seleccionándola y luego haciendo clic en el comando Eliminar columnas .

Nota del código M

Ahora, aquí hay una nota interesante. Básicamente, los íconos de comando en los que hacemos clic en la cinta del Editor de Power Query crean código M. Por ejemplo, haga clic en Ver Editor avanzado para ver el código M generado, como se muestra a continuación.

Y una opción realmente útil si está intentando aprender sobre el código M es alternar la visualización de la barra de fórmulas marcando la casilla Ver Barra de fórmulas . Esto le permite ver el código M generado mientras trabaja e incluso puede realizar ediciones allí.

En cualquier caso, nuestra columna de código postal se ve bien, por lo que es hora de enviar nuestros resultados a Excel.

Enviar resultados a Excel

Haga clic en Cerrar y cargar y bam, los resultados aparecen en nuestra hoja de trabajo como se muestra a continuación.

¡Y eso, amigo mío, es cómo descubrir funciones M que realmente pueden ayudarte a aprovechar esta increíble herramienta!

Si tiene otras funciones M que le resulten útiles, compártalas publicando un comentario a continuación.

Archivo de muestra

• Zip.xlsx