Power Query: referencias absolutas y relativas

En Excel podemos crear fácilmente referencias absolutas y relativas. Un simple símbolo $ aquí y allá es todo lo que se necesita. Pero Power Query es menos flexible, por lo que obtener valores de otras filas no es tan sencillo. Entonces, en esta publicación, cubrimos cómo obtener referencias absolutas y relativas de Power Query.

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0014 Power Query – Referencias absolutas y relativas.xlsx

Tener acceso

Referencias absolutas

Mire la pestaña Referencias absolutas en el archivo de ejemplo. En esta hoja de trabajo, hay cinco escenarios para comparar con una base.

Como se muestra en la captura de pantalla anterior, la fórmula en la celda C2 es:

=B2-$B$2

$B$2 es una referencia de celda absoluta. Cuando copiamos la fórmula, cada celda hace referencia consistentemente a la celda B2.

A continuación, veamos cómo logramos un resultado similar en Power Query. Una vez cargados los datos, la ventana de vista previa muestra esto:

Power Query ya ha realizado algunas transformaciones para nosotros. No es necesario cambiar ninguno de estos.

Haga clic derecho en la primera fila de la columna Beneficio y seleccione profundizar en el menú.

Mire la barra de fórmulas (haga clic en Ver – Barra de fórmulas si no está visible), contiene la sintaxis para hacer referencia a una fila y columna específicas dentro de Power Query.

El código M es:

=#"Tipo cambiado"{0}[Beneficio]

• #”Tipo de cambio” es el nombre del paso de transformación anterior
• {0} hace referencia a la primera fila de datos. En Power Query, el recuento siempre comienza en cero.
• [Beneficio] es el nombre de la columna.

Esto nos proporciona la sintaxis para obtener la referencia absoluta de cualquier elemento en la ventana de Power Query. Eliminar este paso; ya no lo necesitamos.

Ahora agregaremos una columna personalizada para replicar el cálculo de Excel (como se muestra arriba). Haga clic en Agregar columna – Columna personalizada .

En el cuadro de diálogo Columna personalizada, ingrese la siguiente fórmula.

=[Beneficio] - #"Tipo cambiado"{0}[Beneficio]

Ingrese un nuevo nombre de columna, como Vs Base (PQ) , luego haga clic en Aceptar.

Eso es todo. Mire la captura de pantalla a continuación.

Ahora tenemos una fórmula que siempre hace referencia a una fila específica de una columna específica. En la captura de pantalla anterior, Vs Base (PQ) calcula el mismo valor que calculamos en Excel en la columna Vs Base.

Referencias relativas

Mire la pestaña Referencias relativas en el archivo de ejemplo. En este ejemplo, tenemos cinco años de ganancias y un año base. El objetivo es crear una columna que muestre el aumento de las ganancias desde el año anterior.

El año anterior es una referencia relativa, ya que la celda cambia para cada fórmula (es decir, en nuestro ejemplo, el año anterior siempre está una fila arriba).

Como se muestra en la captura de pantalla anterior, la fórmula en la celda C3 es:

=B3-B2

A medida que se copia la fórmula, las celdas a las que se hace referencia en la fórmula cambian a un ritmo relativo. Como resultado, cuando se copia una celda B3 se convierte en C3, luego, cuando se copia nuevamente, se convierte en D3.

Ahora es el turno de Power Query de crear una referencia relativa. Después de cargar los datos en Power Query, la ventana de vista previa tiene este aspecto:

Agregue una columna de índice haciendo clic en Agregar columna – Columna de índice (menú desplegable) – Desde 0

Haga clic en Agregar columna – Columna personalizada para agregar una nueva columna.

En el cuadro de diálogo, ingrese esta fórmula.

=[Beneficio] - #"Índice agregado"{[Índice]-1}[Beneficio]

La columna Índice se utiliza para determinar qué fila se utiliza en la fórmula.

Asigne a la columna un nombre como Incremental (PQ) y luego haga clic en Aceptar .

La ventana de vista previa se ve así:

Como nos referimos a la fila anterior, la primera fila nunca puede tener un valor; como resultado, Power Query muestra un error.

Seleccione la nueva columna Incremental (PQ) , luego, en la cinta, haga clic en Transformar – Reemplazar valores (menú desplegable) – Reemplazar errores

En la ventana Reemplazar errores, ingrese nulo en el cuadro Valor y luego haga clic en Aceptar .

Y hemos terminado. Después de todo, no fue demasiado difícil.

En la captura de pantalla anterior, la columna Incremental (PQ) calculó el mismo valor que teníamos en Excel (que es la columna Incremental). Por lo tanto, hemos creado una referencia relativa.

Conclusión

Power Query no es tan flexible como Excel estándar. Esta es una fortaleza porque significa que la estructura de datos es más sólida. Pero, para replicar algunas fórmulas simples de Excel, necesitamos trabajar un poco más. Como hemos visto, si bien las referencias absolutas y relativas no son transformaciones estándar de Power Query, podemos escribir una fórmula simple para lograr el mismo resultado.

---