Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)

Las fórmulas son el alma de Excel; son esenciales para lograr incluso tareas básicas. Por otro lado, Power Query ha sido diseñado para que se pueda acceder a la mayoría de las transformaciones a través de una interfaz de usuario intuitiva. Pero las fórmulas de Power Query existen; Tiene un lenguaje de fórmulas con más de 700 funciones.

Cada vez que realizamos una transformación con la interfaz de usuario, se utilizan fórmulas de Power Query en segundo plano. Podemos verlos en la Barra de Fórmulas y en el Editor Avanzado.

Tabla de contenido

• ¿Necesitamos utilizar fórmulas de Power Query?
• ¿Dónde podemos escribir fórmulas de Power Query?
• Escribir una fórmula de Power Query en una columna personalizada
• Operaciones de fórmula simples
• Ejemplo de fórmula n.° 1
• Ejemplo de fórmula n.° 2
• Contexto de fila
• Tipos de datos
• Ejemplo de fórmula n.° 3
• Encontrar funciones
• Biblioteca de funciones de Power Query
• #Compartido para mostrar las funciones de Power Query
• IntelliSense
• Analizando los pasos de transformación existentes
• Consejos esenciales para la fórmula
• El código M distingue entre mayúsculas y minúsculas
• M empieza a contar en 0
• Práctica práctica práctica

¿Necesitamos utilizar fórmulas de Power Query?

Cuando comencé a usar Power Query, usé muchas fórmulas porque eso es lo que hacía en Excel. Sin embargo, una vez que entendí la interfaz, quedó claro que se pueden lograr muchas transformaciones sin escribir ninguna fórmula manualmente. Por lo tanto, te recomiendo que mires para las transformaciones disponibles en los menús estándar antes de profundizar en funciones específicas.

Las fórmulas son esenciales para abordar algunas de las situaciones más complicadas que podemos encontrar. Por lo tanto, no puedes evitarlos por completo.

Una confusión es que las funciones no son las mismas que las de Excel; están escritos utilizando código M. Al principio, es frustrante que no podamos transferir nuestro conocimiento existente de Excel a Power Query. Pero una vez que entendemos cómo se construyen las fórmulas, es fácil encontrar lo que necesita.

¿Dónde podemos escribir fórmulas de Power Query?

Cada transformación que emprendemos en Power Query es una fórmula. Cuando promocionamos encabezados, se crea una fórmula:

= Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true])

O si dividimos una columna por una coma, Power Query escribe una fórmula

= Table.SplitColumn(#"Changed Type", "Text", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Text.1", "Text.2"})

Podemos escribir estas fórmulas directamente en la barra de fórmulas o en el editor avanzado. Sin embargo, si estás empezando, estos pueden resultar abrumadores. El lugar más accesible para comenzar es el cuadro de diálogo Columna personalizada.

Cuando usamos una columna personalizada , estamos escribiendo una fórmula dentro de la función Table.AddColumn . Incluso si simplemente ingresamos 1+1 como fórmula. Power Query envuelve esto en la transformación Table.AddColumn .

= Table.AddColumn(Source, "New Column Name", each 1+1)

Como esta publicación es parte de una serie introductoria a Power Query , nos centraremos en escribir fórmulas dentro de una columna personalizada. Pero tenga en cuenta que es posible mucho más si quiere llegar más lejos.

Escribir una fórmula de Power Query en una columna personalizada

El lugar más sencillo para escribir una fórmula de Power Query es en una columna personalizada.

Cree una columna personalizada haciendo clic en Agregar columna Columna personalizada .

Se abre el cuadro de diálogo Columna personalizada.

Las áreas críticas de este cuadro de diálogo son:

1. Nuevo nombre de columna
   El texto ingresado aquí se utiliza como nombre de columna en la tabla.
2. Columnas disponibles
   Contiene una lista de columnas en la consulta que existen en el paso anterior. Al seleccionar una columna y hacer clic en el botón Insertar (o hacer doble clic en el nombre), se inserta el nombre de la columna en la fórmula. Esto es útil ya que reduce los errores tipográficos que impedir que nuestro código funcione según lo previsto.
3. Fórmula de columna personalizada
   Aquí es donde ingresamos el texto que forma la fórmula.
4. Comprobación de errores
   La comprobación de errores nos ayuda a saber si la sintaxis es correcta. Por ejemplo, comprobará si existen corchetes de apertura y cierre coincidentes. Sin embargo, no nos dirá si la fórmula da el resultado correcto, solo que la sintaxis es correcta. En realidad, es un desafío interpretar lo que significan los errores. Para obtener más información sobre los tipos de errores que podemos encontrar, consulte esta publicación: Power Query: errores comunes y cómo solucionarlos .
5. Obtenga más información sobre las fórmulas de Power Query Este enlace nos lleva a las páginas web de fórmulas de Power Query
   de Microsoft . Si se queda atascado, este es el lugar al que debe acudir.

Operaciones de fórmula simples

Las operaciones de fórmulas simples son similares a Excel.

No necesitamos ingresar el símbolo igual ( = ) al principio; automáticamente estará en el cuadro de fórmula.

Operadores numéricos

=[Column 1] + [Column 2] //Addition=[Column 1] - [Column 2] //Subtraction=[Column 1] * [Column 2] //Multiplication=[Column 1] / [Column 2] //Division=[Column 1]  [Column 2] //Concatenate

Corchetes / Paréntesis

Los paréntesis o corchetes (según cómo los llames) funcionan igual que en Excel.

=([Column 1] + [Column 2]) / [Column 3]

Operadores logicos

=[Column 1] = [Column 2] //Equal=[Column 1]  [Column 2] //Greater Than=[Column 1] = [Column 2] //Greater Than or Equal To=[Column 1]  [Column 2] //Less Than=[Column 1] = [Column 2] //Less Than or Equal To=[Column 1]  [Column 2] //Does Not Equal

Las fórmulas anteriores darán un resultado Verdadero o Falso.

Fuerza

El operador Power ( ^ ) que usamos en Excel no funciona en fórmulas de Power Query. En su lugar, tenemos que usar la fórmula Number.Power .

Ejemplo de fórmula n.° 1

La siguiente captura de pantalla muestra algunas operaciones básicas de fórmulas en acción.

Puede ver los nombres de las columnas que se utilizan; Número1 se divide por el total de Número1 + Número2 .

Ejemplo de fórmula n.° 2

La siguiente captura de pantalla muestra que las fórmulas se pueden construir a partir de valores estáticos.

Una fórmula puede incluir una combinación de columnas y valores estáticos.

Contexto de fila

A menos que haga algo de magia con fórmulas avanzadas, las fórmulas de Power Query tienen lo que se conoce como contexto de fila . Esto significa que la fórmula se aplica a cada fila una por una.

Tipos de datos

Las fórmulas de Power Query son exigentes con los tipos de datos.

Si la columna utilizada dentro de la función no es el tipo de datos correcto, la fórmula calculará un error. Entonces, si el tipo de datos no coincide con los requisitos de la fórmula, debemos encontrar una manera de convertirlo. Podemos agregar un paso antes de crear la columna personalizada o utilizar una fórmula de conversión.

Algunas de las fórmulas de conversión más comunes son:

• Text.From : para convertir cualquier cosa en texto
• Date.ToText : para convertir una fecha en texto
• Date.From : para convertir un número en una fecha
• Date.FromText : para convertir texto en una fecha
• Number.ToText : para convertir un número en texto
• Number.From : para convertir cualquier entrada a un número
• Number.FromText : para convertir texto en un número
• Logical.From : para convertir números en sus valores Verdadero o Falso
• Logical.FromText : para convertir cadenas de texto de "Verdadero" o "Falso" en valores booleanos de Verdadero o Falso
• Logical.ToText : para convertir valores booleanos Verdadero o Falso en cadenas de texto "Verdadero" o "Falso"

Ejemplo de fórmula n.° 3

La siguiente tabla contiene números y texto. Como el número de casa es numérico, debemos convertirlo a texto antes de combinarlo con el nombre de la calle en una sola cadena de dirección.

El texto de la fórmula sería:

=Text.From([House Number])  " "  [Street]

El texto en negrita resalta el uso de la función Text.From para convertir el número de casa en un tipo de datos de texto antes de combinarlo con la columna Nombre de la calle .

Encontrar funciones

Como se señaló anteriormente, no podemos usar fórmulas de Excel dentro de Power Query.

Biblioteca de funciones de Power Query

La forma más sencilla de encontrar las fórmulas disponibles es hacer clic en el enlace Más información sobre las fórmulas de Power Query .

Las funciones están agrupadas por lo que hacen. Examinaremos el grupo Número si queremos una fórmula que use o devuelva números. La mayoría de las fórmulas en esta sección comienzan con la palabra Número. A continuación se muestran algunos ejemplos:

• Number.Abs : devuelve el valor absoluto de un número
• Number.FromText : devuelve un número de una cadena de texto
• Number.IsOdd : devuelve verdadero si el valor es impar o falso si es par.

Las fórmulas numéricas específicas también pueden comenzar con otras palabras, como:

• Decimal.From : devuelve un decimal a partir de un valor dado
• Percentage.From : devuelve un porcentaje de un valor determinado

Generalmente, las fórmulas que generan una fecha comienzan con Fecha en el nombre de la fórmula (como Fecha.Año ), y las fórmulas que generan texto comienzan con Texto en el nombre de la fórmula (como Texto.Inicio ).

Piense en las funciones de Excel; ¿Cómo los aprendiste? Probablemente alguien te lo mostró, o tuviste que hacer algunas conjeturas con mucho ensayo y error. Debido a cómo se nombran las funciones de Power Query, es mucho más fácil encontrar la función que queremos.

#Compartido para mostrar las funciones de Power Query

Otra opción para descubrir la función Power Query es ingresar #shared en la barra de fórmulas.

Esto nos proporciona una lista completa de funciones. Podemos hacer clic en cualquier nombre de función para conocerla.

IntelliSense

Power Query tiene IntelliSense integrado. Esto significa que podemos comenzar a escribir el nombre de una función y aparece una lista de opciones. Por ejemplo, la siguiente captura de pantalla muestra la lista de funciones simplemente escribiendo "Tex" en el cuadro de fórmula.

Nota: Al momento de escribir este artículo, un error molesto hace que IntelliSense repita palabras cuando se ingresan fórmulas. Mira este video para más detalles sobre cómo evitarlo: https://www.youtube.com/watch?v=p3hhUZwQGVE

IntelliSense nos ayuda a descubrir funciones y las hace más fáciles de usar. Una vez que tenga el nombre de la función, escriba el corchete de apertura y Power Query mostrará los argumentos y los tipos de datos necesarios. Power Query tardó un tiempo en obtener esto. funcionalidad, pero ahora que está aquí, no podría vivir sin él.

En la captura de pantalla anterior, podemos ver que Text.Start tiene texto como primer argumento, que debe ser un tipo de datos de texto .

Analizando los pasos de transformación existentes

Las transformaciones que utilizamos a través de la interfaz estándar utilizan funciones. Por lo tanto, podemos utilizarlos como guía para ayudarnos a escribir nuestras propias fórmulas.

Digamos que queremos extraer las primeras 4 letras de una cadena de texto. Podríamos usar Agregar columna, extraer los primeros caracteres de la cinta y luego insertar 4 en el cuadro de diálogo Insertar primer carácter. Luego , podríamos echar un vistazo al código M en el Barra de fórmulas para ese paso aplicado; mostraría lo siguiente:

= Table.AddColumn(#"Changed Type", "First Characters", each Text.Start([Text Column], 4), type text)

La sección en negrita es la función Text.Start . Esta función extrae los primeros caracteres de una cadena de texto (es similar a la función IZQUIERDA de Excel).

Esto ilustra que cada vez que esté atascado, mirar el código M para ver transformaciones similares puede guiarlo hacia el tipo de función que necesita.

Consejos esenciales para la fórmula

Las diferencias entre el código M y las funciones de Excel pueden hacernos tropezar. Algunas de las diferencias clave son:

El código M distingue entre mayúsculas y minúsculas

En Excel podemos escribir funciones en mayúsculas o minúsculas; Excel entiende lo que queremos y convierte funciones a mayúsculas por nosotros. Pero Power Query distingue entre mayúsculas y minúsculas; Text.Start es el nombre de una función, pero text.start no lo es. Power Query puede realizar algunas conversiones de texto automáticas para evitar algunos errores, pero no confíe en ello.

M empieza a contar en 0

Text.PositionOf es una función que encuentra la posición de una cadena dentro de otra cadena. Si usamos el siguiente código M como ejemplo:

=Text.PositionOf("Excel Off The Grid","Excel")

Esto es similar a la función ENCONTRAR de Excel con los argumentos en el orden opuesto.

=FIND("Excel","Excel Off The Grid")

La función de Excel devuelve un valor de 1, mientras que la función Power Query devuelve un valor de 0. Esto se debe a que Power Query comienza a contar desde 0. Puede parecer extraño para la mayoría de los usuarios de Excel, pero es común en los lenguajes de programación.

Práctica práctica práctica

En esta publicación, cubrimos los conceptos básicos de las fórmulas de Power Query. Le garantizo que olvidará todo lo que ha leído a menos que practique esto durante las próximas semanas o meses. Probablemente necesitará consultar la biblioteca de funciones M para encuentra lo que necesitas en distintos momentos. No hay que avergonzarse de ello... de hecho, ese es el objetivo de que esté ahí.

Ya es suficiente hablar de mi parte; es hora de que vayas y pruebes algunos de estos por ti mismo.

Leer más publicaciones en esta serie

1. Introducción a Power Query
2. Obtener datos en Power Query: cinco fuentes de datos comunes
3. DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
4. Utilice el editor de Power Query para actualizar consultas
5. Conozca las opciones de Power Query Cerrar Carga
6. Parámetros de Power Query: 3 métodos
7. Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
8. Anexar Power Query: combine rápidamente muchas consultas en 1
9. Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
10. Listar archivos en una carpeta subcarpetas con Power Query
11. Cómo obtener datos del libro actual con Power Query
12. Cómo desvincular en Excel usando Power Query (3 formas)
13. Power Query: valor de búsqueda en otra tabla con combinación
14. Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
15. Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
16. Declaración If de Power Query: condiciones anidadas ifs múltiples
17. Cómo utilizar Power Query Group By para resumir datos
18. Cómo utilizar las funciones personalizadas de Power Query
19. Power Query: errores comunes, cómo solucionarlos
20. Power Query: consejos y trucos

---