Usos del Power Query - importar y transformar datos externos

martes, enero 28, 2014

En la nota anterior vimos cómo importar datos externos con el Power Query, más precisamente una listas de carpetas y archivos. De la misma manera podemos importar datos externos diversas fuentes como bases de datos, Web, archivos texto, archivos Excel, etc.
Power Query nos permite también transformar los datos originales antes de transferirlos a una hoja de Excel o al modelo de datos (tema que será tratado en otra nota).

Siguiendo con el ejemplo de la nota anterior la ventana del editor del Query se ve así

editor del Power Query























La ventana del editor tiene una barra de fórmulas, un área de datos (filas y columnas) y dos barras a los costados ("Navigator" y "Steps").

Como podrán apreciar, en los encabezamientos de las columnas aparece un triángulo similar al que aparece cuando aplicamos Autofiltro a una tabla en la hoja de Excel. Estos triángulos tiene la misma función en la ventana de Query

Autofiltro en la ventana del query

Esto nos permite filtrar las filas de la misma manera como lo hacemos con las tablas en Excel. Por ejemplo, si queremos que la venta del query muestre sólo las filas con archivos de tipo ".png", filtramos de acuerdo a este criterio

query con autofiltro
Una vez aplicado el filtro podemos apretar el botón "Done" en la ventana del query y sólo las filas filtradas serán transferidas a la hoja de Excel

hoja Excel con datos del Query


















Si queremos cambiar el resultado de la consulta, abrimos el editor del query usando el comando Filter & Shape

Boton FIlter & Shape
Supongamos ahora que queremos transferir sólo los archivos de tipo ".png" creados en el 2014; todo los que hacemos es aplicar un nuevo autofiltro a la columna "Date Created"

Autofiltro de fechas en el query


Haciendo un clic con el botón derecho del mouse podemos ver las distintas posiblidades para cada una de las columnas.  Por ejemplo, un clic en el encabezado de la columna "Content" nos muestra

ventana del Power Query

Como ven podemos eliminar la columna (Remove) o eliminar todas las otras columnas (Remove other columns), duplicarla, cambiar el tipo de datos, buscar y reemplazar valores (Replace values),etc. Especialmente práctica es la función "Unpivot Columns" como ya hemos mostrado en esta nota.

Supongamos que queremos eliminar todas las columnas excepto "Name", "Extension" y "Date Created". Lo que hacemos es seleccionar estas columnas (clic sobre el encabezamiento manteniendo el botón Ctrl apretado) y activar la opción "Remove other columns"

Ventana del Power Query



















Ahora vamos a explorar las posibilidades de transformación de los datos de la columna "Date Created". Con un clic del botón derecho del mouse abrimos el menú contextual

ventana del Power Query





















Debido al tipo de datos de la columna, la función "Transform" nos muestra las distintas posibilidades de transformación: fecha (Date), hora (Time), día (Day), etc. Por ejemplo, si elegimos la opción "Date", las fechas aparecerán sin las horas.

Si expandimos la barra "Steps" a la derecha de la ventana del Query, podemos ver los pasos que hemos realizado

ventana del POwer Query























Cuando apuntamos a una de las acciones aparece una X. Un clic sobre la X cancela la operación, como la acción "Deshacer" (undo, Ctrl Z) en Excel.

En la próxima nota veremos más usos de las posibilidades de transformación de datos con Power Query.

5 comments:

Miguel Arias 14 abril, 2017 04:59  

Buenas Noches Jorge, como siempre te felicito por tu pagina y para mi es una referencia segura para cuando tengo dudas y te quería comentar que estoy dando mis primeros pasos con power query pero no logro hacer una conexión con la BD SQL Server como lo hago normalmente con excel:
Datos>de otras fuentes>desde SQL Server>
Ahí coloco mi dns>usuario>contraseña y listo!

Trato de hacer lo mismo en power query en:
desde una base de datos>SQL Server>
Ahí coloco mis datos anteriores y nada da error de conexion:

"Detalles: "Microsoft SQL: El nombre principal no es correcto. No se puede generar contexto SSPI.""

espero que puedas guiarme, gracias y saludos!!

Jorge Dunkelman 14 abril, 2017 14:54  

Hmm..., no se que decir más allá de lo que informa el sistema.Fijate que en el diálogo donde se pone el username y el password hay dos opciones: Windows y Database. El default de Power Query es usar las credenciales de Windows. Te sugiero que cambies a Database ya que se me ocurre que son esas las credenciales que estás usando.

Miguel Arias 14 abril, 2017 19:30  

Buenos días Jorge, muchas gracias por responder, por lo que veo ya de arranque power query de mi parte tiene un punto menos (-1) en comparación con excel, pero como manejo volumen de data le voy a dar un voto de confianza y voy a seguir explorando esta herramienta.

También te quería comentar que logre solucionar mi problema, de una manera no elegante y como no quería, pero aquí te describo la solución por si a otros lectores les pasa lo mismo y puedan solucionarlo: (ojo si comparten este archivo con otras personas tendrán que realizar este procedimiento en cada PC):

1.- Crear la conexión a SQL en Windows:
a) ir a "Orígenes de datos ODBC"
b) seleccionar pestaña "controladores" y verificar que tengan
SQL Server (sino lo tienen pueden descargarlo de la pagina
oficial de MS)
c) seleccionar la pestaña "DSN de usuarios">agregar>doble
click "SQL Server">servidor: "colocan su DNS">seleccionar el
modo de autenticación>usuario y clave para conectarse>
siguiente>siguiente>finalizar>probar origen de datos>aceptar.

2.- Abrir excel, pestaña "power query">desde otros orígenes> desde ODBC>seleccionar la conexión creada> y de ahí en adelante es igual como si trabajáramos con una conexión desde excel.

Jorge cualquier observación házmela saber y gracias!

Saludos,

Jorge Dunkelman 15 abril, 2017 10:49  

Miguel, gracias por compartir.

¿Probaste la opción de "Database" para las credenciales como te señalaba en mi comentario?

Miguel Arias 15 abril, 2017 14:37  

Hola Jorge, si probé la opción de DataBase y tampoco funciono; También en mi búsqueda por internet de una solución leí por ahí que las conexiones con SQL solo se podían hacer dentro de una misma red, no puedo validar la veracidad de dicho comentario porque apenas estoy empezando a conocer la herramienta, pero en mi caso apunta a que es así. Si se te ocurre cualquier otra cosa avísame y muchas gracias por tu ayuda.
Saludos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP