miércoles, 1 de abril de 2020

Conectar Power Query con OneDrive

En estos días tan aciagos que nos ha tocado vivir es muy habitual que las empresas favorezcan el teletrabajo, por lo que el uso de herramientas para compartir documentos en la nube se están haciendo extensivo.

Hoy hablaré de cómo crear una Consulta con Power Query que recupere información de un fichero guardado en OneDrive, y que este fichero funcione en cualquier equipo.


Obviamente necesitamos una cuenta de Office.

Supongamos tenemos un fichero de trabajo guardado en nuestra cuenta de OneDrive, fichero que contiene una tabla de datos...

Conectar Power Query con OneDrive



No son importantes los datos, pero sí la ruta o el enlace de este fichero!!.
Para conocer con exactitud esta ruta entraremos en el fichero, iremos a la ficha Archivo > Información > Copiar ruta de acceso

Conectar Power Query con OneDrive


En mi caso, tras 'copiar ruta de acceso' esto es lo que obtengo:
https://ircalcerrada-my.sharepoint.com/personal/iromero_ircalcerrada_onmicrosoft_com/Documents/DATOS/ejemOneDrive.xlsx?web=1

Muy importante!!, para los pasos siguientes debemos eliminar la parte final '?web=1', esto es, solo nos quedaremos con:
https://ircalcerrada-my.sharepoint.com/personal/iromero_ircalcerrada_onmicrosoft_com/Documents/DATOS/ejemOneDrive.xlsx

es decir, hasta la extensión del fichero de trabajo.


Podemos cerrar este fichero con los datos.

Abriremos el fichero final de trabajo donde crearemos nuestra Consulta de Power Query.

Navegaremos hasta la ficha Datos > grupo Obtener y transformar > desplegable Obtener datos > opción Configuración del origen de datos...

Conectar Power Query con OneDrive



En la ventana de Configuración buscaremos la ruta con la que comienza la ubicación de nuestro fichero de datos origen:
https://ircalcerrada-my.sharepoint.com/personal/iromero_ircalcerrada_onmicrosoft_com/Documents/DATOS/ejemOneDrive.xlsx
en mi ejemplo buscaré:
https://ircalcerrada-my.sharepoint.com/

Conectar Power Query con OneDrive



Estos orígenes de datos se cargan automáticamente al intentar realizar conexiones sobre ellos... si no te aparece en primer término, trata de conectar con Power Query desde la web, y vuelve a la opción anterior.

Volviendo a la ventana de Configuración de orígenes de datos con el origen seleccionado presionaremos el botón de 'Editar permisos...', lo que abrirá una nueva ventana donde gestionar las credenciales de acceso y el nivel de Privacidad.

Conectar Power Query con OneDrive


Entraremos a Editar las credenciales.

Conectar Power Query con OneDrive


Aquí optaremos por un sistema de acceso u otro según el caso (a probar).
Desde mi punto de vista, si nos movemos en entornos de trabajo, lo más seguro es emplear el credencial (usuario) de la Cuenta de organización.
En otros casos basta con Seleccionar la opción de Anónimo.. o empleando el perfil de Windows, etcétera.
Tras Guardar los credenciales, cerramos estas ventanas.


Solo nos queda entrar en la ficha Datos > grupo Obtener y transformar > desplegable Obtener datos > Desde Otras fuentes > Desde la Web, abriéndose una ventana que nos solicita una URL... campo donde escribiremos nuestra ruta:
https://ircalcerrada-my.sharepoint.com/personal/iromero_ircalcerrada_onmicrosoft_com/Documents/DATOS/ejemOneDrive.xlsx

Conectar Power Query con OneDrive



Gestionamos la recuperación de datos normalmente:

Conectar Power Query con OneDrive



Cargamos y cerramos para obtener los datos... y listo. Nuestra conexión está preparada... cualquier actualización sobre el fichero guardado en OneDrive lo visualizaremos con una Actualización de datos.

jueves, 26 de marzo de 2020

ORDENARPOR en descendente por acumulados

Revisando acciones realizadas con Power Query me encontré un caso que PQ resolvía de manera muy simple: ordenar países en descendente según sus ventas acumuladas:

ORDENARPOR en descendente por acumulados


No es el caso de este post, pero los pasos en el editor de Power Query serían agrupar por País, y luego ordenar el resultado por el campo ventas.

El asunto es que pensé si era posible obtener lo mismo empleando las nuevas funciones desbordadas... y esto es lo que conseguí.


En H3 escribimos:
=ORDENARPOR(UNICOS(Tabla1[País]);SUMAR.SI(Tabla1[País];UNICOS(Tabla1[País]);Tabla1[Ventas]);-1)

lo cual desborda el listado de países ordenados por las ventas acumuladas en descendente (de mayor a menor)!!.

Recordemos la sintaxis de ORDENARPOR:
=ORDENARPOR(matriz; por_matriz1; [orden1];...)
es decir, ordenamos los elementos de la matriz del primer argumento, según los elementos de la segunda matriz (segundo argumento), en el orden establecido con el tercer argumento...


Con nuestra fórmula.
1er argumento: matriz de elementos/países únicos: UNICOS(Tabla1[País])
2do argumento: matriz sobre la que ordenar la anterior, i.e., las ventas acumuladas por país: SUMAR.SI(Tabla1[País];UNICOS(Tabla1[País]);Tabla1[Ventas])
3er argumento: el tipo de ordenación, -1 para descendente.

Lo interesante del ejercicio es ver cómo SUMAR.SI desborda los acumulados de cada país... Podemos visualizarlo si este SUMAR.SI lo insertamos en K3

ORDENARPOR en descendente por acumulados



Para 'rizar el rizo' podemos ver esos acumulados ordenados de mayor a menor si aplicamos ORDENAR:
=ORDENAR(SUMAR.SI($B$3:$B$14;UNICOS(Tabla1[País]);$C$3:$C$14);1;-1)

ORDENARPOR en descendente por acumulados



Así queda demostrado que con las funciones UNICOS, ORDENARPOR y la clásica SUMAR.SI obtenemos el listado de países ordenados de mayor a menor según las ventas acumuladas.

Adicionalmente hemos conseguido ver esos acumulados (aunque hay formas más simples de lograrlo).

martes, 24 de marzo de 2020

Gráfico desbordado

Aplicaremos hoy las fórmulas desbordadas a un gráfico estándar, convirtiendo a este en un gráfico desbordado, y por tanto dinámico...
Partimos de una sencilla tabla con datos de países y ventas (rango B2:C12):

Gráfico desbordado


El objetivo es tener un gráfico de columnas que muestre los datos acumulados de las ventas de cada país que exista en nuestra tabla...

Para ello en E3 insertamos la función:
=UNICOS(Tabla1[País])
la cual lista los países de manera única...

En la celda de al lado, en F3, añadimos un SUMAR.SI para acumular Ventas... pero le indicamos con # que se 'desborde' junto a UNICOS:
=SUMAR.SI(Tabla1[País];E3#;Tabla1[Ventas])
fíijate en el segundo argumento:
E3#

Si construimos un gráfico sobre el rango actual E2:F7, y posteriormente aumentase el número de países únicos listados, nuestro gráfico no incorporaría estos nuevos elementos.
Para solucionar este inconveniente usaremos los Nombres definidos 'desbordados'.

Entonces creamos un nuevo nombre definido que use el # para referirnos a nuestros rangos E2:E7 y F2:F7.
Nos quedarán estos dos nombres definidos:
ndPais =Hoja1!$F$4#
ndVentas =Hoja1!$G$4#


Gráfico desbordado


y

Gráfico desbordado



Fíjemosnos en el uso del # para referirnos nuevamente a los rangos desbordados...

Creamos ahora nuestro gráfico de columnas, con el diseño y formato deseado, empleando el rango actual existente E2:F7.
Con el gráfico generado accederemos a la Selección de datos para editar los rangos y poder añadir nuestros Nombres definidos (ndPais y ndVentas):

Gráfico desbordado



Entraremos alternativamente en la Edición de etiquetas y a Modificar la serie.

Al Editar las etiquetas del eje horizontal, nos aseguramos que el rango seleccionado queda escrito así:
=Hoja1!ndPais

esto es, el nombre de la hoja, exclamación !, y el nombre definido

Gráfico desbordado


De igual forma Modificamos la serie de Ventas, quedando:
=Hoja1!ndVentas

Gráfico desbordado



Listo!.

Si aumentásemos los registros en la tabla original, estos nuevos países se mostrarían automáticamente en nuestro gráfico...

Gráfico desbordado