Desde la descarga de documentos con Power Shell hasta su devolución en forma de columnas apiladas usando el Power Query, te cuento los secretos que te permitirán afrontar con éxito esta tarea, considerando esta alternativa.
Semanas atrás me solicitaron armar un reporte que contenga el stock de reportes que gestiona el equipo de trabajo dentro de una organización muy grande (más de 400 reportes en total). A continuación, les contaré cómo lo resolví utilizando Power Shell, Power BI y Power Query.
El primer desafío era cómo acceder a la gran cantidad (hasta ese momento desconocida por mi) de datos relacionados a reportes, datasets, espacios de trabajos y más. Hace algunos años que mi premisa es: tengo que encontrar la manera de hacerlo a escala o automatizado, sin tener que armar un Excel manualmente (lo cual implicaría armar una tabla de Excel completándola a mano, mirando cada espacio de trabajo los reportes contenidos, con los errores que conlleva hacerlo manual, pero por sobre todo el tiempo). Por tal motivo, consideré dos opciones: consulta vía API o descarga Power Shell. Me incliné por la segunda, porque me resultó más simple y rápido, y el desafío era tener una primera visión de la situción, algo así como un MPV (Producto mínimo viable en español); siempre supe que si quería automatizar, el próximo paso sería iterar y usar la API Rest de Power BI.
Básicamente lo que busqué fue descargar ficheros cvs para luego procesarlos con Power Query. Como el objetivo era tener un estado de situación a ese momento y no tenemos, de momento, la necesidad que el reporte se actualice constantemente, trabajar off line descargando manualmente ficheros csv fue una opción adecuada. Ahora les contaré cómo hice para descargar los ficheros con Power Shell:
Lo primero fue abrir Windows Power Shell en modo administrador.
Cuando hable de escribir comandos, mira siempre lo que está después de “PS C:\WINDOWS\system32>”.
Instalar (en caso de no tener instalado. No hace falta si se instaló antes) el módulo para trabajar entre Power Shell y Power BI. Escribe este comando:
Install-Module -Name MicrosoftPowerBIMgmt 
Y una vez dado al intro debemos poner “Y” y le damos a intro (Esto puede tardar unos minutos):
Luego es necesario conectarse a la cuenta de Office 365 del Tenant desde donde descargarás datos. Escribe el siguiente comando:
Connect-PowerBIServiceAccount 
Se abrirá la típica ventana de registro en la cuenta de Office:
Luego, si el login fue exitoso mostrará el siguiente mensaje:
Ahora, obtendremos el fichero de datos de reportes con el siguiente script:
Get-PowerBIReport -Scope Organization | Out-file C:\ReportList.csv 
❗Un comentario importante: puedes indicar otra unidad donde descargar y también puedes cambiar la extensión del fichero.
Luego encontraré el fichero en la ubicación que le indique:
Ahora, tocará comenzar a transformar. Para ello, creo un fichero de Power BI desktop y abro Power Query (asumo que estás familiarizada en como ingresar, de lo contrario les recomiendo suscribirse a Power Platform University para realizar los cursos).
El paso a paso con Power Query
Primer paso: Importo el fichero .csv
Mira cómo devuelve los datos: ¡columnas apiladas! Este es el segundo reto del artículo: transformarlo para obtener una tabla con columnas y cada uno de los atributos apilados aparezcan en una columna independiente. Es decir, quiero que Id, Name, WebUrl, EmbedUrl, sin nombre y Dataset aparezcan en columnas, es decir, normalizarla.
Segundo paso: elimino filas vacías.
Tercer paso: creo una columna índice que comience en 0:
Un comentario importante para el próximo paso, recuerden la siguiente imagen:
Cuarto paso: agregamos una columna Módulo
En Excel sería como utilizar la fórmula Residuo. Entonces, primero seleccionamos la columna “índice” y luego agregamos la columna:
La función nos solicitará un valor y aquí en donde quiero que recuerden la imagen del paso anterior. Verán que el id siempre viene después de la secuencia de 6, es decir, que cada nueva línea de id comienza después de 6. Por lo tanto, el valor a indicar es 6.
El resultado es el siguiente:
Verán que el valor que le asigna al Id es siempre 0 (cero).
Quinto paso: dinamizamos la columna Módulo
Seleccionamos la columna módulo y transformamos:
Se nos abrirá una ventana en donde elegiremos la primera columna (“Column1”) y en opciones avanzadas seleccionamos “No Agregar”.
¡Hemos logrado otro hito! 🙌 Creamos las columnas que queríamos. Si ven el detalle, cada columna tiene el nombre asignado en el paso de “Columna Módulo”, es decir, cada valor con cero está en la columna 0 (“Id”), cada valor con 1 está en la columna (“Name”) y así sucesivamente.
Sexto paso: rellenar hacia arriba las columnas 1, 2, 3, 4 y 5.
Seleccionamos desde la columna “1” hasta la columna “5” y rellenamos:
La “columna 0” es la de “Id” y esa NO la tenemos que rellenar.
Séptimo paso: filtra la columna “0” (Representa los “Id”) quitando los valores nulos.
Octavo paso: cambiamos nombres
Noveno paso: Seleccionamos las columnas con las queremos quedarnos.
¡Lo logramos!, tenemos la tabla como la queríamos, ya tenemos la consulta para trabajar con ella y seguir armando el modelo. Lo que les pasará si descargan más información de los reportes (con Power Shell), como área de trabajo (lo encontrarán como SpaceWork), conjunto de Datos (Datasets), etcétera, es que deberán hacer las mismas transformaciones.
Finalmente, cerrar y aplicar, y hemos terminado. La inspiración de las ETL de Power Query proviene de un video de Sergio Alejandro Campos.
En mi caso teníamos más de 170 reportes que estamos gestionando y mapearlos manualmente no era una opción para mi (hubiese sido muy ineficiente y poco motivante). Power Shell es fácil, los comandos que les he indicado son de lectura, no romperán nada (si usan esos). Les dejo el link de GitHub sobre las galerías de Power Shell para Power BI: https://github.com/DevScope/powerbi-powershell-modules
La historia de hoy es el fruto de tener un desafío e intentar resolverlo. Para ello tuve que probar e investigar un poco. Yo suelo utilizar recursos de internet, no es que yo sea un gurú. Espero que les sea de utilidad.
Espero sea de utilidad y si alquien quiere dar sus opiniones y/o sumar data, genial.
Saludos 🤟
Matías
ℹ️ Originalmente, he creado este post para el blog de la empresa Climbea. Les dejo el enlace. Cómo descargar información con Power Shell y transformarla en columnas apiladas con Power Query (powerplatform.university)