miércoles, 3 de diciembre de 2014

Obtener datos externos - Búsqueda En Línea

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


Get External Data, es el primer grupo de opciones  de la pestaña Power Query y nos permite obtener datos de diferentes origenes de datos.

La opción Online Search, primera de este grupo, nos permite hacer búsqueda de datos tanto públicos como de  nuestra organización.

 
Al hacer clic en esta opción, se abrirá la pestaña Search de la cinta de opciones. La ventana se divide en 4 regiones:
  Menú Search
  Barra de Fórmulas
  Panel de búsqueda
  Panel de resultados




Menú Search

El menú Search esta formado por 3 grupos de opciones: Refine, Options y Close

 

Grupo Refine

Nos permite filtrar nuestra búsqueda a partir de distintos criterios:
  • Name (limita los resultados a tablas y consultas que tengan un nombre determinado)
  • Description (limita los resultados a tablas y consultas que tengan la descripción especificada)
  • From (solo muestra resultados que provengan de personas o páginas web especificas)
  • Data Source (muestra resultados que provengan de determinada fuente de datos )
  • This week (muestra resultados de acuerdo a la fecha de actualización)
  • Column Names (muestra resultados que contengan nombres de columnas especificados)
  • Certified (muestra solo resultados que estén certificados)

Veamos un ejemplo en el que filtraremos por el nombre tabla o consulta (world cup) y por el nombre de columna (winners).

Seleccionaremos la opción de menú Name. En el panel de búsqueda se añade el criterio name.

 

 
Escribiremos world cup como nombre del tema a buscar


 
A continuación seleccionaremos en el menú la opción Column Names


En el panel de búsqueda escribiremos winners en el nombre de la columna y a continuación haremos la búsqueda.
Como resultado obtendremos 250 fuentes de datos que contienen los criterios seleccionados.

 


Si no hubieramos fijado estos criterios de búsqueda, las fuentes de datos encontradas serían 9074 como se muestra debajo, en otra búsqueda.



Grupo Options

Permite seleccionar la fuente de datos a partir de búsquedas recientes. 

Esta opción nos mostrará una lista con las últimas fuentes de datos que hemos utlizado, podemos escoger una de ellas.

 

Grupo Close

Permite cerrar la ventana de búsqueda.


Panel de búsqueda

Nos permite hacer la búsqueda de datos públicos y corporativos por temas o fuentes de datos.
 

Para descubrir datos corporativos, debe poseer una cuenta de Power BI que le permita acceder a los datos de su organización.

Para descubrir datos públicos solo debe especificar la fuente o el tema sobre el que desea realizar la búsqueda. En nuestro ejemplo hacemos una búsqueda de por tema: world cup

 


Como resultado de la búsqueda se mostrarán el total y una lista páginada de las fuentes de datos encontradas. En el ejemplo se han encontrado un total de 9074 fuentes públicas que hacen referencia al tema world cup.

 

Situando el cursor sobre cualquiera de las fuentes de datos podemos ver los datos contenidos y las propiedades de la fuente.

 
Marcados en color amarillo aparecerán los terminos utilizados en la búsqueda.
Debajo podemos ver algunas propiedades de los datos como: el nombre de la fuente (Wikipedia), la fecha de actualización de los datos (30 de mayo de 2014), una tabla con los datos, el número y nombre de columnas de la tabla y de los origenes de los datos.(http://en.wikipedia.org/wiki/FIFA_World_Cup).
El botón Load permite cargar los datos a una hoja Excel (Load) o a Power Pivot (Load To...) 
El botón Edit permite transformar los datos antes de cargarlos. La transformación se realiza en el Editor de Consultas, que veremos en la próxima entrada.




Si seleccionamos la opción Load se mostrará una ventana que nos irá indicando el estado de la carga de los datos en Excel. Esta ventana se cerrará de forma automática cuando el proceso haya terminado.



Seguidamente se abrirá la pestaña Diseño de la cinta de opciones y se mostrará una nueva hoja Excel con una tabla que contendrá los datos.

 

Menú Diseño

Las opciones del menú Diseño nos van a permitir modificar la tabla de resultados

 

El grupo Propiedades

Nos permite modificar el nombre y el tamaño de la tabla.

Si queremos modificar el nombre de la tabla Excel seleccionaremos la caja de texto que contiene el nombre de la tabla y lo sustituiremos con el nombre deseado. Recuerde tener en cuenta las restricciones para los identificadores.

Para redefinir el tamaño de la tabla utilizaremos la opción Cambiar tamaño de la tabla.

 

 
Se abrirá una ventana que nos permitirá seleccionar el nuevo rango de datos de la tabla.

 

El grupo Herramientas

Nos permite realizar transformaciones a los datos de la tabla.



Opción Resumir con tabla dinámica
Permite ordenar y resumir datos complejos usando las tablas dinámicas de Excel.



 
Debemos definir los datos a utilizar (tabla o rango de celdas)  y la hoja del libro en la que mostraremos los resultados.
 

 
Una vez hecho esto, se muestra la hoja Excel con la tabla dinámica y los campos de la tabla dinámica (las columnas de la consulta). Debemos seleccionar los campos que agregaremos al informe y arrastrarlos hasta el área correspondiente.



Si seleccionamos la columna Winners para el área Filas y Runners-up para el área Valores construiremos una tabla dinámica de paises ganadores de copas del mundo y la cantidad de veces que lo han hecho.




Opción
Quitar duplicados
Elimina las filas duplicadas en una hoja.

Podemos especificar en que columnas buscar los datos que se repiten.



Opción Insertar segmentación de datos
Permite filtrar datos de manera interactiva
 
Para ello debemos seleccionar las columnas que utilizarán para segmentar los datos
 
Una vez seleccionadas las columnas, damos aceptar y se mostrará una ventana con los valores que utilizaremos para filtrar .

 
Escogiendo uno de los valores se mostrarán en la tabla solo los que se corresponden con la selección.


 

El grupo Datos externos de tabla

Permite configurar, actualizar y exportar los datos.

Opción Exportar
Permite exportar los datos a una lista de Sharepoint o a un diagrama dinámico de Visio.




Opción Actualizar
Permite actualizar los datos de la consulta.



Opción Propiedades de conexión
Nos permite configurar la conexión a las fuentes de datos.
 

Opción Propiedades del rango de datos
Permite ver y modificar las propiedades de los datos externos.


Opción Desvincular
Desconecta la tabla del servidor. Esta tabla no se mantendrá actualizada.


Grupo Opciones de estilo de tabla

Permite modificar elementos del estilo de la tabla tales como:
  • añadir o eliminar filas de encabezado.
  • añadir o eliminar filas de totales
  • añadir o eliminar filtros a las columnas


Grupo Estilos de Tabla

Permite cambiar el estilo de la tabla por uno de los estilos predefinidos.




Panel Workbook Queries

Aparece a la derecha de la hoja Excel. Nos muestra la lista de consultas de nuestro libro.


Situando el mouse sobre cualquiera de las consultas del libro nos mostrará en una ventana los detalles de la consulta


Tendremos la posibilidad de cargar, editar o eliminar la consulta.




El menú contextual de la consulta

Haciendo clic en el botón derecho del mouse sobre alguna de las consultas se mostrará el menú contextual que conteniene las opciones anteriores.




Con este menú podemos:

  • Editar la consulta
  • Eliminar la consulta
  • Actualizar los datos de la consulta
  • Compartir la consulta
  • Duplicar la consulta
  • Referenciar la consulta
  • Combinar consultas (mezclar y anexar consultas)
  • Enviar al catalogo de datos de nuestra organización
  • Mover la consulta de lugar
  • Mostrar la selección
  • Ver las propiedades de la consulta ( nombre y descripción)


Actualizar los datos de la consulta

Para actualizar los datos, además de utilizar el menú contexctual,  podemos utilizar el botón  que aparece a la derecha de la consulta en el Panel de Consultas del Libro.



lunes, 24 de noviembre de 2014

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ísticaLimitació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