Como crear listas

En este ejemplo, vamos a ver cómo podemos crear listas.

En una hoja en blanco, vamos a la pestaña de datos, dentro de obtener y transformar datos, desplegamos obtener datos, desplegamos de otras fuentes, y, seleccionamos consulta en blanco.

Hacemos clic con botón alternativo de ratón sobre la consulta, y, seleccionamos editor avanzado.

Borramos las comillas dobles después de la variable origen.

Para crear una lista debemos de usar las llaves ({}), pues después del signo igual, abrimos unas llaves, ponemos, por ejemplo, los números 1, 2, y 3, separados por coma.

Cerramos las llaves.

Hacemos clic en listo.

Ya tenemos nuestra lista.

Sabemos que es una lista por el siguiente icono.

Si queremos crear una lista, por ejemplo, desde el numero 1 hasta el numero 20, no tenemos que escribir todos los números, ponemos el primer número, dos puntos (..) y el último número, esto creara una lista desde el primer número hasta el último número, volvamos al editor, y, ponemos desde el numero 1 al número 20.

Tenemos nuestra lista.

Si quiero saber el número de elementos que compone una lista, puedo usar la función List.Count, que solo tiene un argumento que es lista, y, nos devuelve un número.

Es este caso, devuelve 20.

Si queremos obtener el primer valor de la lista, usamos la función List.First, esta función tiene dos argumentos, siendo uno de ellos opcional.

List.First(list as list, optional defaultValue as any) as any

El argumento list, es la lista con la que vamos a trabajar, y, el argumento defaultValue, es el valor predeterminado si la lista está vacía, este argumento es opcional.

Volvemos al editor avanzado, y, vamos a usar dicha función.

Vemos que nos devuelve el primer valor.

Podemos crear una lista con diferentes tipos de datos, volvamos al editor avanzado, y, borramos la función anterior, volvemos a las llaves, después, ponemos los números 1, 2, y, 3, ponemos una coma, y, por ejemplo, ponemos "a", "b", y, "c" entre comillas dobles.

Hacemos clic en listo, y, vemos una lista con los valores 1, 2, 3, a, b, y, c.

Dentro de una lista, podemos poner números, textos, fechas, horas…

Vamos a hablar del operador ?, si volvemos al editor avanzado, vemos el código.

Dentro de IN, le estamos diciendo que nos devuelva la variable Origen, la cual nos devuelve toda la lista de elementos, pero también, podemos indicar el elemento que queremos que nos devuelva, para ello, después de origen, abrimos unas llaves, y, entre las llaves, indicamos que elemento queremos que nos devuelva, recordar, que en Power Query, se empieza a contar desde cero, por ejemplo, voy a indicar que nos devuelva el elemento 3, es lo que se conoce como índice posicional.

Hacemos clic en listo, y, vemos que nos devuelve la letra “a”.

Pero, que ocurre si ponemos un elemento que no esta dentro de la lista, por ejemplo, vamos a poner el elemento 7.

Hacemos clic en listo, y, vemos que nos devuelve un error.

Volvemos al editor avanzado, después de las llaves ponemos el operador ?.

Hacemos clic en listo, y, ¿Qué ha ocurrido?, pues que ahora aparece NULL, y, no un error.

Cuando accedemos a una columna en una tabla, lo que nos retorna es una lista, por eso, las listas son tan importantes.

Las listas quedan asociadas a las llaves ({}), y, los registros quedan asociados a los corchetes ([]).

Ahora, nos vamos a traer un modelo donde tenemos una fecha de inicio, y, una fecha de fin, pero no están ordenadas, queremos crear una lista donde aparezcan las fechas para cada mes, bien, vamos a traernos el archivo, para ello, cerramos y cargamos como una conexión, para ello, dentro de Power Query, vamos a la pestaña de inicio, dentro del grupo nueva consulta, desplegamos nuevo origen, desplegamos archivo, y, seleccionamos libro de Excel.

Seleccionamos el modelo, y, hacemos clic en abrir.

Para saber los días que tiene cada mes, debemos de contar dichos días por mes, vamos a crear una columna personalizada, donde vamos a usar la función List.Dates, que nos va a devolver una serie de valores fechas de un tamaño, indicándole un paso.

Sintaxis:

List.Dates(start as date, count as number, step as duration) as list

El primer argumento es start, que es la fecha o fechas para utilizar, el siguiente argumento es count, que va a definir el tamaño, y, el ultimo argumento es step, que es el paso, en este caso, son los días que hay en cada mes, esta función devuelve una lista.

Vamos a crear una columna personalizada, para ello, vamos a la pestaña agregar columna, y, hacemos clic en columna personalizada.

Se abre la ventana de columna personalizada, dejo el nombre predeterminado, en la ventana de formula, ponemos la función y abrimos un paréntesis.

Como argumento start, en la ventana de columnas disponibles, hacemos doble clic en la columna inicio.

Como como argumento count, restamos la fecha de fin menos la fecha de inicio.

Coma, ahora viene el argumento step (paso), ponemos 1, quiere decir, que, desde la fecha de inicio, debe de ir un día mas hasta llegar al argumento count.

Pero ¿Cómo le indicamos 1 día?

Tenemos una función llamada #duration, que nos permite indicar días, horas, minutos, y, segundos a adelantar si es positivo, o, retrasar si el número es negativo.

Como tenemos que sumar 1 día, ponemos la siguiente expresión.

Aceptamos y nos devuelve un error.

¿A que es debido este error?

Vamos a crear una columna personalizada, donde restamos la columna fin menos la columna inicio.

Aceptamos, y, tenemos una nueva columna con la diferencia de días, horas, minutos y segundos.

En este formato no podemos ni sumar ni restar días, necesitamos el formato en número, para ello, tenemos una función llamada Number.From, que devolverá un valor numérico a partir de un valor dado.

Sintaxis

Number.From(value as any, optional culture as nullable text) as nullable number

El primer argumento es value, que es el valor con el que vamos a trabajar, el segundo argumento es opcional, y, es culture, que indica el país en el que estamos.

Solo necesitamos el primer argumento, volvemos a la columna personalizada, que creamos antes, para ello, vamos a pasos aplicados, en el paso personalizada agregada1, hacemos clic en la rueda dentada.

Después del signo igual ponemos la función Number.From, al final de la expresión, cerramos el paréntesis de la función.

Aceptamos, y, vemos como ahora aparece solo la diferencia de días.

Pues es lo que tenemos que hacer en la primera expresión para poder sumar un día, pero antes, vamos a borrar el ultimo paso, para ello, hacemos clic en la X a la izquierda del paso personalizada agregada1.

Hacemos clic en la rueda dentada del paso personalizada agregada.

Ponemos Number.From en la resta.

Aceptamos, y, vemos una columna con listas.

Seleccionamos una lista y vemos los valores de la lista en la parte inferior.

He seleccionado la fecha 03/05/2017, en la parte inferior, vemos las fechas que corresponden a mayo, pero si vemos la ultima fecha, no es el día 20, sino el día 12.

Para arreglar este problema, sumamos uno a la resta.

Ahora, si tenemos los días correctos.

Hacemos clic en el icono de expandir, y, seleccionamos expandir en nuevas filas.

Ya tenemos nuestro ejercicio resuelto, crear una columna con la fecha de inicio de la columna inicio, hasta la fecha de la columna fin, y, entre media los días entre ambas fechas.

Hacemos clic con botón alternativo de ratón sobre la columna personalizado, y, seleccionamos quitar otras columnas.

La ponemos como fecha.

Le cambio el nombre a fechas.

Cerramos y cargamos como una conexión.

En la ventana consultas y conexiones, hacemos clic con botón alternativo de ratón sobre la consulta fechas y seleccionamos cargar en.

Se abre la ventana importar datos, marcamos tabla, y, la ponemos al lado del modelo.

Aceptamos, y, ya tenemos nuestra columna de fechas.

Veamos otro ejemplo, tenemos el modelo que suelo usar habitualmente, pero tenemos tres columnas de cantidades.

Vamos a llevarlo a Power Query.

Vamos a crear una nueva columna con el valor máximo de las columnas cantidad1, 2, y 3, para ello, debemos de crear una lista, ya sabemos que para crear una lista debe de ir entre llaves, vamos a la pestaña agregar columna, y, hacemos clic en columna personalizada.

Vamos a usar la función List.Max, que nos va a devolver el valor máximo de una o varias columnas, solo tiene un argumento que es la lista con la que vamos a trabajar, pues en la ventana de fórmulas, ponemos la función y abrimos un paréntesis.

Pulsamos enter, abrimos unas llaves, de la ventana columnas disponibles seleccionamos cantidad1, ponemos coma, seleccionamos la columna cantidad2, ponemos coma, seleccionamos la columna cantidad3, cerramos las llaves, y, cerramos paréntesis.

Aceptamos, y, en la nueva columna tenemos el valor máximo de las columnas cantidad1, 2, y, 3.

Ahora tenemos el mismo modelo, pero solo dos columnas de cantidad.

Vamos a llevarlo a Power Query.

Queremos quitar las columnas cuya cantidad1 y cantidad2 sean iguales.

Vamos a crear una columna personalizada.

Vamos a usar la función List.RemoveMatchingItems, esta función quita las repeticiones de lista1 que coincida con lista2, además, tiene un argumento opcional que es equationCriteria, donde podemos especificar un valor opcional para controlar la igualdad de las pruebas.

Sintaxis

List.RemoveMatchingItems(list1 as list, list2 as list, optional equationCriteria as any) as list

En la Ventana de formula, ponemos la función y abrimos un paréntesis.

Como argumento lista1, entre llaves seleccionamos la columna cantidad1 y cerramos las llaves.

Como argumento lista2, entre llaves seleccionamos la columna cantidad2, y, cerramos las llaves.

Cerramos paréntesis.

Aceptamos.

Tenemos una columna nueva con listas.

Si hacemos clic en una lista donde los dos números son iguales, vemos en la parte inferior que no aparece nada.

Si hacemos en una lista donde los números no son iguales, aparece el valor.

Queremos quedarnos con las columnas cuya cantidad1 y cantidad2 no sean iguales, primero vamos a hacer clic en expandir, y, seleccionamos expandir en nuevas filas.

Desplegamos el filtro de la columna personalizado, y, desmarcamos nulo.

Aceptamos, y, ya lo tenemos.

Miguel Angel Franco Garcia