Aprendamos Power Query para Excel – 8 – Consulta desde una base de datos SQL Server
Ya hemos visto la capacidad de Power Query para conectarnos a archivos de texto y archivos de Excel, y eso nos funciona perfectamente cuando tenemos los archivos en nuestro poder. Pero qué sucede cuando la información está alojada en un Servidor de la empresa y deseamos acceder directamente a dichos datos.
Sé que no siempre se nos dará acceso directamente a los Servidores de la empresa, pero no olvides que la Inteligencia de negocios apunta a que cualquier ejecutivo pueda tener un “autoservicio de información”, es decir que de alguna manera u otra se nos brinde acceso a la información de nuestro negocio y con las herramientas adecuadas ser capaces de extraer, transformar y cargar datos, además de tener más poder de análisis y toma de decisiones.
Montando un entorno de pruebas
Para poder hacer este video monté un Servidor SQL en una máquina virtual para poder mostrarles cómo conectarnos desde Power Query. Les cuento que usé el software VMware Workstation 12 Player para instalar Windows Server y SQL Server.
Figura 1.SQL Server sobre una máquina virtual.
Instalé la base de datos de pruebas AdventureWorks, la cual la puedes descargar desde aquí.
Datos de acceso de SQL Server
Para poder conectarnos al Servidor de SQL debemos saber primero el nombre del Servidor o la IP del mismo. Dentro de la máquina virtual ejecutamos la aplicación CMD y ejecutamos el comando IPCONFIG, el cual nos dirá la IP del Servidor de Windows. El usuario y contraseña de SQL serán las misma que asignaron al momento de la instalación. En caso de que no tengas un ambiente de pruebas como el anterior, te sugiero solicites a tu departamento de Sistemas los siguientes datos:
-
IP o nombre del Servidor.
-
Usuario asignado de SQL.
-
Contraseña de SQL.
Figura 2. Con el comando IPCONFIG obtenemos la IP del Servidor.
Extraer datos desde SQL Server usando Power Query
Una vez que tenemos los datos de acceso del Servidor, ya sea porque montamos un ambiente de pruebas o se nos otorgaron las credenciales, nos dirigimos a Excel:
-
Nos vamos al pestaña Datos.
-
En la sección Obtener y transformar elegimos Nueva consulta.
-
Elegimos Desde una base de datos > Desde una base de datos SQL Server.
-
Ingresamos la IP o nombre el servidor.
-
De manera opcional nos solicita la base de datos. Ingresamos AdventureWorksDW.
-
Aceptar.
Figura 3. Capturamos los datos de acceso al Servidor SQL.
Ahora se nos pedirán las credenciales del Servidor SQL:
-
Elegimos la sección Base de datos.
-
Ingresamos el usuario.
-
Ingresamos la contraseña.
-
Seleccionamos el nivel, ya sea solo la IP o la IP y base de datos. Elegimos la segunda opción.
-
Aceptar.
-
Elegimos la Tabla DimEmployee y presionamos el botón Editar.
Figura 4. Ingresamos las credenciales de acceso.
Transformando los datos
Una vez dentro de Power Query, ventana que ya debemos estar familiarizados, haremos las siguientes transformaciones:
-
Elegimos desde la columna EmployeeKey hasta SalesTerritoryKey, damos clic derecho Quitar columnas.
-
Como la Tabla tiene muchas columnas, solo dejaremos las columnas FirstName, LastName, Title, HireDate, BirthDate, MaritalStatus, Gender, DepartmentName.
-
Le cambiamos el nombre a las columnas Dando clic derecho > Cambiar nombre.
Figura 5. Eliminamos columnas y cambiamos el nombre a las restantes.
Seguimos con las transformaciones:
-
Con la techa [Shift] elegimos las columnas Nombre y Apellido.
-
En la pestaña Transformar elegimos Combinar columnas.
-
En Separados elegimos Espacio y como nombre nuevo de la columna Nombre completo.
-
Ahora obtendremos la antigüedad en años del empleado.
-
Elegimos la columna Contratación. En la pestaña Transformar elegimos Fecha > Antigüedad.
-
Ahora damos clic derecho sobre la misma columna y elegimos Transformar > Total de años.
-
Finalmente damos clic derecho Cambiar tipo > Número entero.
-
Repetimos los 3 pasos anteriores, pero en la columna Fecha de nacimiento para obtener la edad del empleado.
-
En la columna Estado civil vemos los valores M = Married y S = Single. Vamos a reemplazarlos con M = Casado y S = Soltero.
-
Una vez elegida la columna Estado civil elegimos la pestaña Agregar columna > columna condicional.
-
Nuestra condición será Si es igual a M entonces Casado, Si es igual a S entonces Soltero, Sino Otro.
Figura 6. Agregamos una Columna condicional para reemplazar los valores según el contenido de la columna.
-
Hacemos lo mismo con la columna Género, pero ahora definimos la condición como Si es igual a M es Masculino, Si es igual a F entonces Femenino.
-
Ahora eliminamos las columnas originales de Estado civil y Género.
Cargar datos a Excel
Ahora que nuestros datos están transformados nos vamos a la pestaña Archivo > Cerrar y cargar.
Insertamos una Tabla dinámica. En la columna Insertar elegimos Tabla dinámica. Nuestro cálculo será el número de hombres y mujeres por departamento.
Elegida la Tabla dinámica nos vamos a la pestaña Analizar > Gráfico dinámico. Elegimos un gráfico de barras y vemos el resultado.
Figura 7. Nuestro análisis final será una gráfica de Mujeres y hombres por departamento.
Ver video Consulta desde una base de datos SQL Server
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
Descarga el ejemplo
A continuación te enlisto los recursos que puedes descargar si quieres montar tu entorno de prueba:
Máquinas virtuales: VMware Workstation Player.
SQL Server gratuito: SQL Server Express.
Bases de datos de ejemplo: Base de datos de ejemplo AdventureWorks.
Power Query: Power Query para Excel 2013 y anteriores.
