Introducción a Microsoft Power Query para Excel

Para ver una versión actualizada de esta entrada visita el blog de mi nuevo sitio www.dataXbi.com

Qué es Microsoft Power Query?

Es un complemento de Power BI para Excel que permite la Extracción, Transformación y Publicación de los datos, así como tener un mayor control sobre ellos. Detecta datos tanto públicos como corporativos y se conecta con ellos fácilmente. Tiene dos ediciones, una estándar que puede  ser instalada individualmente y otra que requiere una suscripción de Power BI para Office 365.

Qué características tiene?

Edición estándar

• Búsqueda de datos públicos
• Importar datos de diversos orígenes de datos
• Filtrar, ordenar y combinar datos
• Combinar datos de múltiples fuentes de datos

Edición suscripción de Power BI para Office 365

• Introducción a la Experiencia de administración de datos en Power BI
• Compartir consultas
• Administrar consultas compartidas
• Buscar y usar una consulta compartida

Cómo instalarlo?

Microsoft Power Query para Excel se puede descargar desde el Centro de Descarga de Microsoft. Recuerde utilizar la versión con el mismo idioma y arquitectura que la de su Office.

Para conocer la versión  de su Office ejecute cualquiera de las aplicaciones del paquete (Word, Excel, ...) y vaya a la pestaña Archivo.

Elija la opción Cuenta del menú que está a la izquierda de la ventana y a continuación oprima el botón Acerca de …Se mostrará una ventana similar a esta

En la primera línea de texto, al final, podrá ver la arquitectura de la instalación de su Office, en mi caso 32 bits.

Una vez descargado el instalador, abra la carpeta que lo contiene

Chequee que no tiene abierto el Excel  y ejecute el instalador.

Siga cada paso del proceso.

Cuando finalice la instalación, abra Excel y cree un libro en blanco. Debe aparecer una nueva pestaña en la cinta de opciones, Power Query.

La cinta de opciones de Microsoft Power Query

 La cinta de opciones Power Query nos da acceso a las herramientss de este complemento. Las opciones dentro de la cinta se agrupan de acuerdo a su funcionalidad.

 Grupo Get Extern Data

Este sección nos permitirá extraer los datos de los distintos origenes

 Opciones:

• Online Search: Permite importar datos de orígenes de datos públicos.
• From Web: Permite importar datos desde una página web.
• From File: Permite importar datos de archivos: Excel, CVS, TXT, XML, Carpeta
• From Database: Permite importar datos desde una base de datos: SQL Server, Access, Oracle, MySQL, PostgreSQL, etc
• From other sources: Permite importar datos desde: Facebook, OData, etc

Grupo Excel Data

Este sección nos permitirá extraer los datos a partir de tablas de Excel
 

Opción From Table: Permite crear una nueva consulta vinculada a una tabla Excel.

 

Grupo Combine

Esta sección nos permite obtener datos de más de un origen a la vez.

Opciones:

• Merge: Permite crear una nueva consulta a partir de 2 consultas existentes.
• Append: Permite crea una nueva consulta a partir de dos consultas. La nueva consulta contendrá todas las filas de la primera consulta seguidas de todas las filas de la segunda consulta.

Grupo Manage

Esta sección nos permite manejar los distintos origenes de datos de nuestro libro Excel, así como las consultas realizadas a esos origenes.

Opción Workbook Queries: Muestra y oculta el panel de consultas del libro. Permite administrar dichas consultas.

Grupo Workbook Settings

Én esta sección podremos modificar las propiedades de nuestro libro.

 Opciones

• Locale: Permite modificar la configuración regional usada.
• Fast Combine: Permite configurar si se combinan datos de diferentes niveles de privacidad. Por defecto no lo permite.

Grupo Machine Settings

Aquí podremos configurar las propiedades de Power Query y de cada uno de los origenes de datos.

Opciones:
Datasource Settings: Configurar los orígenes de datos

Update: Actualizar la versión de Power Query
Options: Configurar opciones de uso de Power Query

 Grupo Power BI

Desde esta sección podremos acceder a nuestra organización para ver y manejar el catalogo de datos de nuestra organización.

Opciones:

• My Data Catalog Query: Lista de consultas compartidas al catálogo de Power BI
• Signin: Conectarse con su organización para ver las consultas compartidas y hacer búsqueda de datos

 Grupo Help

Desde esta sección accederemos a la ayuda en linea de Power Query y podremos enviar comentarios acerca de su funcionamiento y sugerencias de como mejorarlo.

Opciones:

• Seend Feelback: Enviar comentarios de lo que te agrada y/o disgusta de Power Query
• Help: Acceder al contenido de la ayuda en línea de Power Query
• About: Conocer más acerca de Power Query

Acceder a la cinta de opciones con el teclado

Presionando la tecla ALT podemos ver las teclas de acceso rápido correspondientes a cada pestaña de la cinta de opciones  y a las opciones de la barra de herramientas de acceso rápido.

Si oprimimos Y4 se abrirá la pestaña Power Query donde veremos las  teclas asociadas a las opciones de esta pestaña.

Si ahora oprimimos las teclas Y8 se mostrará el menú de la opción From File

Orígenes de datos

Existe una gran variedad de orígenes de datos desde los que puede importar datos a Excel usando Microsoft Power Query. Desde un mismo libro se puede acceder a distintos orígenes de datos y combinarlos.

Microsoft Power Query para Excel guarda una credencial del origen de datos (o identidad de inicio de sesión) de las distintas conexiones de orígenes de datos que se usan y, también, de los niveles de privacidad de cada origen de datos.

Requisitos previos de los orígenes de datos

Origen de datos	Proveedor	Versión mínima del proveedor	Versión mínima del origen de datos	Objetos de origen de datos compatibles	Descarga
SQL Server	ADO.NET (integrado en .Net Framework)	Net Framework 3.5 (solo)	SQL Server 2005 y posteriores	Tablas o vistas Funciones escalares Funciones de tabla	Incluido en .NET Framework 3.5 o posterior
Access	Motor de base de datos de Microsoft Access (ACE)	ACE 2010 SP1	Sin restricciones	Tablas o vistas	Vínculo
Excel (solo archivos .xls) 1	Motor de base de datos de Microsoft Access (ACE)	ACE 2010 SP1	Sin restricciones	Tablas Hojas	Vínculo
Oracle 2	ODP.NET	ODAC 11.2 Versión 5 (11.2.0.3.20)	9.x y posteriores	Tablas o vistas	Vínculo
	System.Data.OracleClient (integrado en .Net Framework)	.NET Framework 3.5	9.x y posteriores	Tablas o vistas	Incluido en .NET Framework 3.5 o posterior
IBM DB2	Cliente ADO.NET de IBM (parte del paquete de controladores de servidor de datos de IBM)	10.1	9.1 y posteriores	Tablas o vistas	Vínculo
MySQL	Conector o red	6.6.5	5.1	Tablas o vistas Funciones escalares	Vínculo
PostgreSQL	Proveedor de ADO.NET de NPGSQL	2.0.12	7.x y posteriores	Tablas o vistas	Vínculo
Teradata	Proveedor de datos .NET para Teradata	14 y posteriores	12 y posteriores	Tablas o vistas	Vínculo
Sybase IQ	Ianywhere.Data.SQLAnywhere para .NET 3.5	16 y posteriores	16 y posteriores	Tablas o vistas	Vínculo

Fuente de los datos: sitio de Office

Configuración de orígenes de datos

Desde el grupo Machine Setting de la cinta de opciones podemos configurar las credenciales de los orígenes de datos y la privacidad de los mismos.

Administrar credenciales de orígenes de datos

1. En el grupo Configuración de la máquina, haga clic en Configuración de origen de datos.
   
   
2. Seleccione un origen de datos en la lista Configuración de los orígenes de datos.
   
   
3. Podemos editar o eliminar el origen de datos seleccionado utilizando las opcines Edit o Delete del menú contextual.
   
4. Si seleccionamos editar se mostrará una ventana con los detalles del origen de datos: url, credencial y privacidad.
   
5. Oprimiendo el botón Edit podemos modificar el url y la credencial del origen de datos.
   

Los orígenes de datos pueden tener un nivel de privacidad: público, organizacional o privado.

Los orígenes de datos privados

Contienen información confidencial y su visibilidad puede restringirse a los usuarios autorizados. Está completamente aislado de otros orígenes de datos.

Ejemplos de orígenes de datos privados

• Datos de Facebook

Los orígenes de datos organizacionales

Limita su visibilidad a un grupo de personas de confianza. Está aislado de todos los orígenes de datos públicos pero permanece visible para el resto de orígenes de datos organizacionales.

Ejemplos de orígenes de datos organizacionales

• Un documento en una intranet con permisos habilitados para un grupo de confianza.

Los orígenes de datos públicos

Mantiene visibles los datos que contiene para todos los usuarios. Solo se pueden marcar como Público los datos de libros, de orígenes de datos de Internet o de archivos.

Ejemplos de orígenes de datos públicos

• Datos de una página de Wikipedia
• Datos de un archivo local con datos que se han copiado de una página web pública

Ejemplo de origen de datos 

En este ejemplo seleccionaremos una fuente de datos externa: Facebook

Usaré mi usuario de Facebook y me conectaré a mi lista de amigos.

Como resultado se muestra el Editor de Consultas con una vista previa de los resultados. En el editor podremos transformar los datos hasta obtener el resultado deseado.

Para ver el nivel de privacidad de dichos datos. Seleccionaremos en la cinta de opciones de Power Query, dentro del grupo Machine Settings la opción Data source Settings

Se mostrará la ventana Data Source Settings donde seleccionamos el origen de datos correspondiente a Facebook y oprimiendo el botón derecho sobre el origen de datos mostramos el menú contextual

Seleccionamos la opción Edit y se abrirá una ventana con los detalles del origen de datos. Aquí podemos ver y modificar el nivel de privacidad para este origen de datos.

Especificaciones y límites de Power Query

Para Excel 2013, Excel 2010

Característica	Limitación
Longitud de nombre de consulta.	80 caracteres.
Caracteres no válidos en un nombre de consulta.	Comillas dobles (“), puntos (.) y espacios en blanco iniciales o finales.
Número de celdas de la vista previa de los datos en el Editor de consultas.	3.000 celdas.
Elementos del panel de navegación que se muestran por nivel: bases de datos por servidor y tablas por base de datos.	Primeros 1.000 elementos en orden alfabético. Si desea agregar elementos no visibles de forma manual, modifique la fórmula de este paso.
Tamaño de los datos que procesa el motor.	Se limita en función de la memoria virtual disponible (para versiones de 64 bits) o aproximadamente a 1 GB
Número de columnas por tabla.	16.384.
Tamaño máximo de texto en una celda de vista previa.	1 M de caracteres.
Tamaño máximo de texto que se rellena en Excel o en el modelo de datos.	No se limita desde Power Query.
Tamaño máximo del conjunto de datos al evaluar una consulta.	256 MB.
Número máximo de filas que se rellenan en una hoja de cálculo.	1.048.576.
Límite flexible de caché persistente. Un límite flexible se puede ser exceder durante períodos determinados.	4 GB.
Entradas individuales en la caché.	1 GB.
Las dependencias y las consultas comprimidas tal como se encuentran almacenadas en la cadena de conexión.	64.000 caracteres.

Fuente de los datos: sitio de Office