• Author / Uploaded
• rubenperez62

• Categories
• Microsoft Excel
• Bases de datos
• Point and Click
• Servidor SQL de Microsoft
• SQL

Citation preview

www.it-ebooks.info

Para su comodidad Apress ha colocado a algunos de los materiales texto preliminar después del índice. Por favor, use los Marcadores y contenido a una Vistazo enlaces para acceder a ellos.

www.it-ebooks.info

Mapa de contenidos

Sobre el Autor �������������������������������������������������������������

Crítico �����������������������������������������������������������������

�������������������������������������������������������������������� xix Prefacio

��������������������������������������������������������������������

■ Capítulo 1: Introducción a la energía de consultas �������������������������������������������

■ Capítulo 2: Potencia de consulta de fuentes de datos �����������������������������������������

■ Capítulo 3: La transformación de datos con la energía de consultas ����������������������������������

■ Capítulo 4: destinos de datos ����������������������������������������������������

■ Capítulo 5: Introducción a M �����������������������������������������������������

■ Capítulo 6: Trabajar con consultas múltiples ���������������������������������������������

■ Capítulo 7: El poder de consulta y Poder BI para Office 365 ��������������������������������������

■ Capítulo 8: Recetas de consulta y Eléctricas ���������������������������������������������

Índice �����������������������������������������������������������������

v

www.it-ebooks.info

Capítulo 1

La introducción de la energía de consultas

Este libro es para las personas que pasan mucho tiempo trabajando con los informes y paneles de construcción de Excel. Más específicamente, este libro es para las personas que trabajan con los informes y paneles de construcción de Excel y que se aburren con copiar y pegar datos en las hojas de trabajo, aburrido de hacer clic en la misma secuencia de botones de cada mes para limpiar y dar forma a esos datos y aburrido con la fijación de la problemas asociados con fórmulas complejas, los datos sucios, y los errores que son inevitables cuando se tiene que seguir el mismo procedimiento una y otra y otra vez. La buena noticia es que el poder de consulta está aquí para no tener que estas tareas aburridas y repetitivas y le dará tiempo para concentrarse en lo más importante: el análisis de los datos y la obtención de puntos de vista de ella. Aun mejor,

Potencia para realizar consultas y el poder de BI Consulta de potencia es un complemento en Excel desarrollada por Microsoft, y su propósito es hacer que sea más fácil de cargar datos en Excel a partir de fuentes de datos externas. Es parte de la suite de BI de alimentación de herramientas y, como tal, es sólo una herramienta de muchos que se puede utilizar en el desarrollo de las llamadas “soluciones de autoservicio de Business Intelligence” en Excel. ¿Qué es “auto-servicio de inteligencia de negocios?” Le oigo preguntar. Bueno, es sólo un término de lujo para los informes y cuadros de mando que se genera como parte de su trabajo. El objetivo de la Inteligencia de Negocios (comúnmente abreviado como “BI”) solución es hacer que la información de negocios accesibles a las personas para que puedan utilizarlo para tomar decisiones informadas sobre cómo hacer su trabajo; otros términos que usted puede haber oído incluyen “sistemas de gestión de la información” y “sistemas de soporte de decisiones”. Todo el proceso se conoce como “auto-servicio”, porque las personas que quieren utilizar estos datos, los analistas, los contadores, los encargados y así sucesivamente, son también las personas que están construyendo los informes que no tienen que depender con la ayuda del departamento de TI para hacerlo. Por supuesto, esto siempre ha sido uno de los usos más populares de Excel, pero, con el poder de BI, Microsoft se ha centrado en lo que hace aún más fácil de hacer esto. Al hacer esto, Microsoft ha reafirmado la posición de Excel como herramienta de elección para cualquier persona que trabaja con datos como parte de su trabajo. Microsoft se ha centrado en lo que hace aún más fácil de hacer esto. Al hacer esto, Microsoft ha reafirmado la posición de Excel como herramienta de elección para cualquier persona que trabaja con datos como parte de su trabajo. Microsoft se ha centrado en lo que hace aún más fácil de hacer esto. Al hacer esto, Microsoft ha reafirmado la posición de Excel como herramienta de elección para cualquier persona que trabaja con datos como parte de su trabajo.

Teniendo en cuenta que la energía de consulta es sólo una parte de la suite de energía BI, es importante tener en cuenta todos los componentes de potencia de BI para que pueda poner el poder de consulta en el contexto, entender lo que hace, entender cuándo se debe utilizar y cuando otra herramienta es más apropiado.

Componentes de potencia de BI Los componentes de potencia BI se dividen en dos grupos: los complementos de Excel y los servicios en la nube que están disponibles a través de Power BI para Office 365. A continuación se presentan los complementos de Excel:

•

consulta el poder

•

Power Pivot

•

Power View

•

Mapa de potencia

1 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Y siguientes son los servicios en la nube disponibles a través de Power BI para Office 365:

•

Sitios de energía de BI

•

Q&A

•

Catálogo de potencia de datos de BI

•

aplicación de BI móvil

Ahora, vamos a ver cada uno de ellos en un poco más de detalle, en el orden en que son propensos a usar, de modo que se puede ver cuáles son sus roles.

consulta el poder Convenientemente suficiente para este libro, la energía de consulta va a ser el primer componente de la suite de BI de energía que se utiliza en la construcción de una nueva solución de BI. Eso es debido a que el primer paso para crear cualquier tipo de informe o cuadro de mandos es de hacerse con los datos de origen de la misma. Poder de consulta le permite conectarse a una amplia variedad de diferentes fuentes de datos, extraer datos de forma rápida y sencilla, y definir una serie de pasos repetibles, para limpiar el filtro, y de otra manera a transformar sus datos antes de que se carga en Excel. Poder de consulta le da la opción de cargar datos directamente en la hoja de trabajo o, si está utilizando Excel 2013, en el nuevo modelo de datos de Excel. Obviamente, esto es una cuenta muy simplificada de lo que el poder de consulta es capaz de hacer, y el resto del libro voy a entrar en muchos más detalles!

Pivote poder y el modelo de datos de Excel 2013 El modelo de datos de Excel es un motor de base de datos en memoria que se ejecuta dentro de Excel y que le permite cargar grandes cantidades de datos (mucho más grandes de lo que pueda caber en una sola hoja de trabajo que puede manejar millones, incluso decenas de millones de filas de datos) para fines de análisis. En una solución tradicional Excel BI, que puede estar acostumbrado a cargar sus datos en bruto en una hoja oculta y luego usando VLOOKUPs para mover los valores de esta hoja oculta en un informe, o el uso de tablas dinámicas a desmenuzar sus datos. El modelo de datos de Excel le da una mejor forma de hacer ambas cosas. Si carga los datos en el modelo de datos de Excel, ya no tiene que utilizar una hoja oculta para almacenar sus datos; una vez que los datos están en el modelo de datos de Excel, El modelo de datos de Excel es, de hecho, el motor de base de datos detrás del pivote de alimentación complemento, lanzado por primera vez para Excel 2010. En Excel 2010, Power pivote era una unidad autónoma: el complemento consistía en una interfaz de usuario, así como un motor de base de datos. En Excel 2013, se dividió en dos y el motor de base de datos se construyó directamente en Excel. El pivote de alimentación add-in todavía permanece pero sólo como una interfaz de usuario. Usted tiene que utilizar el pivote de alimentación complemento para poder utilizar ciertas funcionalidades, más avanzado, pero que no lo necesita, si sólo desea realizar tareas básicas. La división entre el poder de pivote y el modelo de datos de Excel en Excel 2013 ha confundido a muchas personas y, como resultado, se encuentra un montón de libros y artículos que siguen se refieren a ambos como pivote de alimentación.

Aparte de la capacidad de trabajar con grandes volúmenes de datos, carga de datos en el modelo de datos de Excel tiene muchas otras ventajas sobre el método “hoja oculta” que incluye lo siguiente:

•

Varias tablas de datos se pueden cargar en él. Después de haber cargado las tablas de datos, puede crear relaciones entre esas tablas similares a las que se puede crear en Access u otras bases de datos relacionales. Una vez que se han creado relaciones, puede crear tablas dinámicas que incluyen datos de todas estas tablas dinámicas mesas tradicionales se limitan a una sola tabla de origen.

•

Los cálculos complejos pueden ser escritos en el DAX, el lenguaje del poder de pivote. Estos incluyen cálculos de tiempo de la serie, los porcentajes de participación, y muchos otros tipos de cálculos que son difíciles de implementar en las fórmulas normales de Excel.

2 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

•

comprime el modelo de datos de Excel datos muy eficiente para cargar sus datos en él, en lugar de en la hoja de cálculo, puede reducir drásticamente el tamaño de los libros. Esto es especialmente importante si tiene intención de subir el libro a un sitio de alimentación BI porque hay límites en el tamaño de los libros que se pueden cargar, ya que pronto descubrirá.

•

Se pueden añadir características tales como jerarquías, que permiten a sus usuarios profundizar a través de sus datos siguiendo un camino predefinido, y KPI, que son cálculos especiales que ayudan a los usuarios a seguir el rendimiento del negocio.

Pivote poder y el modelo de datos de Excel están juntos un tema gigantesca y merecen un libro a sí mismos; de hecho muchos excelentes libros, documentos y publicaciones de blog que ya están ahí fuera. Si usted es serio sobre la construcción de soluciones de BI en Excel, entonces lo debes a ti mismo para aprender más acerca de este tema.

Power View Power View es una nueva característica de Excel 2013, que le permite crear atractivas, cuadros de mando interactivos como nuevas hojas de trabajo dentro de su libro. Está muy relacionada con, pero no debe confundirse con una aplicación independiente que también se llama Power View, sino que sólo está disponible en SharePoint. Power View en Excel toma los datos almacenados en una hoja de trabajo o desde el modelo de datos de Excel como punto de partida, y luego, usando arrastrar y soltar, que le permite crear tablas, gráficos y mapas que pueden ser fácilmente filtrados y en rodajas. Incluso si ya puede construir cuadros de mando e informes en una hoja de trabajo regular con las tablas dinámicas o gráficos dinámicos, Power View le da más opciones para la visualización de los datos, es fácil de usar, y el producto final es más atractiva visualmente.

Mapa de potencia Mapa de potencia es un complemento de Excel que le permite superponer datos geográficos en mapas en 3-D. Por ejemplo, si se trabaja con datos al por menor, se puede utilizar Power mapa para trazar las ventas de todas sus tiendas en un mapa. En la ubicación de cada tienda, puede utilizarlo para mostrar un gráfico de barras de las ventas dentro de esa tienda. Una vez hecho esto, se puede explorar estos mapas, la panorámica y el zoom, e incluso grabar “tours” que muestran los datos de muchos lugares diferentes. Al igual que Power View, su principal fortaleza es su factor “wow”-picada baja sobre una ciudad y ver a cientos de pequeños gráficos que aparecen en un barrio en el que sabes lo convierte en un espectáculo muy impresionante.

SharePoint y Poder BI para Office 365 Después de haber cargado los datos en Excel utilizando la energía de consulta, creado tablas dinámicas y reportes usando la energía de pivote y el modelo de datos de Excel, y creó cuadros de mando y visualizaciones que utilizan electricidad Vistas y gráfica de la potencia, el siguiente paso es probable que desee hacer es compartir los frutos de su trabajo con sus colegas. No hay nada que le para de enviar por correo electrónico el libro a ellos, pero hay una manera mejor: SharePoint.

La versión en las instalaciones de SharePoint es utilizado por muchas organizaciones para gestionar documentos. SharePoint Enterprise Edition incluye Excel Services, que permite a los libros de Excel para ser vistos y editados en un navegador. Servicios de Excel no soporta toda la funcionalidad de escritorio de Excel, pero tiene muy buen soporte para las características de BI de Excel: tablas dinámicas de Excel, Cubo Fórmulas y electricidad Vistas técnica puede ser todo en un navegador. Esto hace SharePoint y Excel Services una gran manera de hacer que sus informes a disposición de sus compañeros de trabajo.

Un número creciente de organizaciones apuntan ahora a SharePoint Online, versión alojada en la nube de Microsoft de SharePoint, que está disponible como parte de Office 365. Si su organización se ha trasladado a la oficina 365 y utiliza SharePoint Online, puede publicar sus libros a un SharePoint biblioteca de documentos en línea, compartirlos con otras personas, e interactuar con ellos en un navegador a través de Servicios de Excel. Para los escenarios simples, esto puede ser suficiente para sus informes y cuadros de mando.

3 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Si usted compra un BI de energía adicional para la oficina 365 de suscripción, se obtiene varias características muy útiles, además de lo que está disponible con una suscripción regular Office 365:

•

Mientras SharePoint Online le permite cargar documentos de hasta 2 GB de tamaño, si usted quiere ser capaz de ver sus libros de trabajo de BI utilizando Servicios de Excel en un explorador, está limitado a los libros de tan sólo 10 MB. El poder de BI para Office 365 le permite aumentar este límite mediante la distinción entre el tamaño de los datos en el modelo de datos de Excel (que puede ser cualquier cosa hasta 250MB en el momento de la escritura) y el resto del libro (que todavía se limita a 10 MB).

•

Los datos contenidos en el modelo de datos de Excel de libros se pueden actualizar de forma programada, incluso si las fuentes de datos que no son en sí mismos en la nube. Instalación del componente de gestión de datos de puerta de enlace en las instalaciones permite que la energía de BI para Office 365 para conectar el libro en la nube con SQL de su organización bases de datos Oracle y servidor.

•

El catálogo de la energía de BI de datos permite a los administradores para proporcionar acceso a una variedad de fuentes de datos públicas y corporativas a través de un sitio de alimentación de BI mediante la exposición de ellos como fuentes de datos OData.

•

Usuarios de consultas de energía también pueden compartir su trabajo con sus colegas a través del Catálogo de potencia de BI de datos. (Esto se explicará con más detalle en el capítulo 7.)

•

sitios de alimentación de BI proporcionan una forma más inteligente, más empresarial para organizar sus informes y cuadros de mando de una biblioteca de documentos de SharePoint en línea regular.

•

sitios de alimentación de BI también proporcionan paneles de gestión que le permiten un seguimiento de los libros de trabajo de sus colegas han estado utilizando y cuándo.

•

Q & A es una característica de potencia de BI para Office 365 que permite a los usuarios consultar los datos contenidos en el modelo de datos de Excel de libros mediante consultas del idioma inglés y luego ver los resultados de estas consultas se muestran en un informe de Power View. Mientras que Q y A sólo habla Inglés en el momento, otros idiomas se pueden soportar en el futuro.

BI móvil El último componente de la suite de BI de energía es la aplicación móvil de BI. Si has subido un libro a un sitio de alimentación de BI, usted tiene la capacidad de ver partes de él-tablas, gráficos, tablas dinámicas, los rangos con nombre, gráficos dinámicos y poder ver paneles-a través de la aplicación móvil de BI. Se trata de una aplicación de Windows 8.1 diseñado para permitir a los usuarios de las tabletas de Windows para acceder a los datos de BI mientras están lejos de sus escritorios. aplicaciones similares para otras plataformas móviles como iOS se dará a conocer en el futuro.

Resumen del flujo de trabajo En resumen, por tanto, el flujo de trabajo de energía BI es:

1.

cargar datos de fuentes externas de datos en Excel usando la energía de la consulta

2. los datos del modelo en el modelo de datos de Excel y añadir cálculos utilizando la energía de pivote

3.

crear informes y cuadros de mando en estos datos utilizando tablas dinámicas, Excel Fórmulas Cubo, Vista de alimentación, y la gráfica de la potencia

4.

publicar su libro en un sitio de alimentación de BI en SharePoint Online que otros usuarios puedan acceder a los informes en ella

Figura 1-1 muestra el flujo de trabajo en más detalle.

4 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta Sobresalir Almacenamiento de datos

Informes

Archivo de texto Excel modelo de

Cubo Fórmulas

datos / Power

Pivote

Base de datos

dinámicas / Excel

consulta el poder

informes de tablas

de energía Ver Hoja de cálculo Mapa de alimentación Base de datos

Libro de trabajo creador

Publicar

Servicios de Excel

Poder Bl Sitio

Actualizar los datos

dinámica Informes de tabla Power View Reports

Q&A

Bl móvil

otros usuarios

SharePoint Online

La Figura 1-1. El flujo de trabajo de energía BI

Potencia para realizar consultas y Licencias de potencia BI El modelo de licencia para la alimentación de consulta y BI de energía para Office 365 no es sencillo y requiere una explicación. Para Excel 2013 usuarios, Consulta de energía tiene licencia exactamente de la misma manera que la energía de pivote y Ver Power (aunque no gráfica de la potencia, que sólo está disponible si tiene una Oficina 365 de suscripción), y está disponible como de forma gratuita si usted ha comprado uno de los siguientes:

•

la versión independiente de Excel 2013

•

una licencia de Office 2013 Professional Plus para el escritorio

•

una Oficina 365 de suscripción que le da acceso a la Office 2013 Professional Plus herramientas de escritorio. Hay un gran número de SKU de suscripción, pero la Oficina 365 ProPlus, E3 y E4 SKUs todos incluyen la versión correcta de Excel.

5 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Si tiene Excel 2010, la única edición que soporta alimentación de consulta es Office Professional Service Pack Plus 1. Vista de alimentación y la potencia de MAP no están disponibles para Excel 2010, pero el poder de pivote está disponible para todas las SKU de Excel 2010.

La posibilidad de ver los libros de Excel almacenados en la versión en las instalaciones de SharePoint en un navegador web sólo está disponible en SharePoint Enterprise Edition y la funcionalidad que funciona mejor si tiene SharePoint 2013. Además, tendrá que tener poder de pivote para SharePoint instalada. Los servicios basados ​en la nube de energía incluidos en BI para Office 365 (Power Sitios de BI, Q & A, el Catálogo de alimentación de BI de datos, y la aplicación Mobile BI), están cubiertos por un modelo de suscripción por separado. Un requisito previo para la utilización de estos servicios es una suscripción de 365 Office que incluye SharePoint Online; este caso es necesario adquirir una suscripción adicional para utilizar los servicios mismos.

detalles de la licencia completos, así como enlaces para instalar los diversos componentes de la fuente de BI, se puede encontrar en

http://www.microsoft.com/powerbi . Ninguno del Poder BI complementos de Excel están disponibles en las versiones de Apple Mac o Windows RT de Excel.

Instalación de energía de consultas Poder de consulta debe ser descargado e instalado por separado después de Excel se ha instalado; Si no ve la pestaña de consulta de la cinta después de haberlo instalado, es posible que tenga que activar el complemento manualmente. Se requiere Windows Vista o mayor, si va a instalar en una PC de escritorio o Windows 2008 o mayor si va a instalar en un servidor. También requiere .NET 3.5 Service Pack 1 e Internet Explorer 9 o superior. Hay versiones de 32 bits y 64 bits de potencia de consulta disponibles. Si tiene la versión de 32 bits de Excel instalado, debe instalar la versión de 32 bits de potencia de consulta; si tiene la versión de 64 bits de Excel, debe instalar la versión de 64 bits de potencia de consulta. La versión de 64 bits se recomienda si se va a trabajar con grandes cantidades de datos. Las nuevas versiones de potencia de consulta se liberan de forma regular y estos pueden incluir funcionalidad nueva significativo. La versión de actualización de la ficha de alimentación de consulta en Excel se encenderá cuando una nueva versión está disponible para descargar.

Conceptos de consulta poder Ahora que usted ha visto que el poder de consulta encaja en la suite de BI de energía, es el momento de comenzar a mirar con más detalle. Un buen lugar para comenzar es mediante la definición de algunos de los conceptos y la terminología que se encontrará cuando se trabaja con la energía de consulta. Por suerte, no hay mucho para definir la terminología. (Si le resulta más fácil aprender viendo en lugar de leer, es posible que prefieren deslizarse sobre esta sección y luego pasar a la siguiente, donde encontrará los mismos conceptos descritos por medio de un ejemplo práctico.) El concepto más importante en la alimentación de consulta es el de una consulta. ( Desafortunadamente, esto significa que hay que hablar de “consultas de consulta de energía”.) Una consulta es un trabajo que importa los datos de una o más fuentes de datos, opcionalmente hace algo a los datos como filtro, o agregado, y luego lo carga en Sobresalir. Las consultas se almacenan dentro de un libro de Excel, y un libro de trabajo pueden contener varias consultas. Las consultas a sí mismos pueden ser utilizados como fuentes de datos para otras consultas; dos consultas también se pueden combinar entre sí más bien como una tabla se puede unir a otro en SQL, y una consulta se pueden añadir en el extremo del otro. Una consulta se compone de uno o más pasos que están dispuestos en un orden específico. Un paso puede conectarse a una fuente de datos para recuperar los datos (generalmente el primer paso en una consulta hace esto, pero los pasos subsiguientes puede hacer esto, también), o puede tomar los datos devueltos por una etapa precedente y aplicar algún tipo de transformación a eso. El último paso en una consulta devuelve el resultado de la consulta en su conjunto. Los pasos están definidos en la lengua propia de alimentación de consulta expresión, “M”, que lleva por desgracia ninguna semejanza con el lenguaje de fórmulas de Excel o VBA y es, de hecho, un lenguaje funcional como F #. Cuando se crea un nuevo paso en la interfaz de usuario de energía de consulta, la energía de consulta generará el código M necesaria para ese paso de forma automática. Si usted es un programador de confianza, puede editar el código detrás de cada paso sí mismo o crear nuevos pasos escribiendo su propio código M. Sin embargo, en la mayoría de los casos, esto no es necesario.

Aunque los pasos de alimentación de consulta pueden conectarse a fuentes de datos externas, no existe el concepto de una conexión a una fuente de datos como un objeto independiente, y Poder de consulta no puede utilizar conexiones definidas en otros lugares en Excel (que es decir, las conexiones que se pueden encontrar en la pestaña de datos en la cinta). Sin embargo, el resultado de una consulta de alimentación de consulta se convierte en una conexión tradicional de Excel y se puede utilizar como cualquier otro tipo de conexión. Esto permite que la energía de consulta para ser capaz de resultados de salida a una tabla en una hoja de cálculo, por ejemplo.

6 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Consulta Tutorial poder Esta sección ilustra los conceptos descritos anteriormente caminando a través de la creación y edición de una consulta de consulta simple poder. También actúa como una breve visita guiada de la interfaz de usuario de energía de consulta. A medida que comience a utilizar la energía de consulta, usted encontrará que todo tipo de menús adicionales, cristales, y las pestañas aparecen dependiendo de lo que está haciendo. En los capítulos siguientes, a medida que aprende más sobre la funcionalidad de alimentación de consulta, se convertirá en más familiarizados con los detalles de la interfaz de usuario.

Creación de una consulta simple El punto de partida de todo su trabajo de consulta de energía será el tabulador de consulta en menú de cinta de Excel, que se muestra en la figura 1-2 .

La Figura 1-2. La ficha de alimentación de consultas

En el lado izquierdo de la ficha Alimentación de consulta, en la sección Obtener datos externos, se puede ver las opciones para importar datos de diferentes tipos de fuentes de datos, mientras que en la sección de datos de Excel se puede ver un botón para importar datos de una tabla de Excel en el libro actual. En otras partes de la ficha Alimentación de consulta, se puede ver opciones para combinar datos de diferentes consultas, así como varias otras opciones administrativas que se tratarán más adelante en este libro. Haga clic en el botón Desde archivo. En el menú desplegable, haga clic en la opción Desde CSV como se muestra en la figura 1-3 .

La Figura 1-3. Desde el botón CSV

7 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Un cuadro de diálogo aparecerá. Debe seleccionar el archivo 01_01_SimpleSales.csv a partir de los archivos de ejemplo de este capítulo. Al hacer esto, aparecerá la ventana del Editor de consultas de energía de consulta como se muestra en la figura 1-4 . Las cinco áreas principales de la ventana del Editor de consultas, marcados en la figura 1-4 , son los siguientes:

1.

La barra de herramientas del Editor de consultas. Se trata de un menú de cinta que contiene todas las opciones para la creación de nuevos pasos en su consulta.

2. La barra de fórmulas. Aquí es donde se puede editar la expresión M para cada paso en la consulta. 3.

El panel del navegador. Este panel plegable le permite navegar a través de ciertos tipos de fuentes de datos, tales como archivos XML para que pueda encontrar los datos particulares que busca.

4.

El panel de resultados. Aquí es donde se puede ver los datos devueltos por el paso seleccionado.

5. El panel de Configuración de la consulta. Este panel muestra el nombre de la consulta que se está editando, una

descripción, una lista de todos los pasos de la consulta en la sección Pasos Aplicadas, y dos casillas de verificación que controlan la que se enviará la salida de la consulta en la sección Configuración de carga.

La Figura 1-4. La ventana del Editor de consultas

8 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

En este punto, se habrá creado una nueva consulta, denominada Consulta1, que consta de tres pasos ya se han creado para usted que se enumeran en la sección Pasos Aplicadas en el lado derecho de la pantalla en el panel Configuración de la consulta. La salida de la tercera etapa, llamada ChangedType, será visible en el panel de resultados. Si hace clic en cualquiera de los otros pasos anteriores, podrás ver el resultado de la consulta después de ese paso ha sido ejecutado en su lugar. (Esto hace que sea fácil de depurar una consulta de alimentación de consulta, ya que le permite ver cómo cada paso cambia los datos.) Ahora haga clic en el botón Cerrar Aplicar y en la esquina superior izquierda del editor y la ventana se cerrará. Dado que la carga a la hoja de casilla de verificación (que se encuentra en Configuración de carga que haya en la esquina inferior derecha de la pantalla) fue seleccionada, se creará una nueva hoja de trabajo y la salida de esta consulta se mostrará en una nueva tabla de Excel ubicado en la celda A1 de la nueva hoja de cálculo, como se muestra en la figura 1-5 .

Figura 1-5. salida de la consulta

¡Felicidades! Ha creado su primera consulta Consulta de energía.

Edición de una consulta existente Las consultas se pueden editarse después de que se haya creado. Antes de poder hacer esto, sin embargo, es necesario volver a la pestaña de consulta de la cinta y haga clic en el botón de libro en la sección de gestionar las consultas, por lo que se puede ver una lista de todas las consultas presentes en este libro. Al hacer esto, el panel de libro consultas aparecerá en la parte derecha de la pantalla, como se muestra en la figura 1-6 . La consulta que acaba de crear, Consulta1, será visible en este panel.

9 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Figura 1-6. El panel de libro consultas Ahora, mueva el cursor del ratón sobre el panel de libro consultas y Consulta1. Al hacer esto, aparecerá un cuadro colgante que muestra una vista previa de los datos, cuando la consulta se actualizó por última, y ​donde la consulta se va a cargar. También en el cuadro colgante es una serie de opciones de menú se puede hacer clic, además de elipses que, al hacer clic, revelan aún más opciones de menú. (Ver figura 1-7 .) Las opciones de menú en este cuadro colgante también están disponibles en un menú desplegable si hace clic en la consulta en el panel de libro Consultas.

10 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Figura 1-7. El menú flotante de libro consultas Haga clic en la opción Editar en el cuadro colgante y la ventana del Editor de consultas volverá a abrir, mirando al igual que lo hace en la figura 1-4 . A continuación, haga clic en las filas Quitar el menú desplegable, que se encuentra en el centro izquierda de la ficha Inicio de la barra de herramientas del Editor de consultas en la sección Reducir, y haga clic en Conservar filas superiores. Esto añadirá un nuevo paso en el extremo de la consulta que filtra todo menos un número determinado de filas en la parte superior de la consulta. Antes de que pueda hacer esto, sin embargo, usted necesitará decirle a la energía de consulta el número de filas que desea mantener. Al hacer clic en el botón, la energía de consulta se abrirá un cuadro de diálogo, como se muestra en la figura 1-8 , Que le pide el número de filas de mantener. Introduzca el valor 2 mantener sólo los mejores dos filas y haga clic en OK.

11 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Figura 1-8. El Top cuadro de diálogo Filas Mantenga

Una vez que haya hecho clic en Aceptar, verá los datos han cambiado. Antes, había tres meses cotizada en enero, febrero y marzo. Ahora, sólo hay dos de enero y febrero, como se muestra en la figura 1-9 . Un nuevo paso también se ha añadido a la consulta llamado KeptFirstRows y el código M para este paso es visible en la barra de fórmulas.

Figura 1-9. La salida de la etapa de KeptFirstRows Por último, haga clic en el botón Cerrar y Aplicar, y verá la nueva salida de la consulta aparece en la tabla de Excel en la hoja de trabajo, como se muestra en la figura 1-10 .

12 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Figura 1-10. El resultado de la consulta editado

¿Por qué utilizar la energía de consulta? Si usted es un usuario experimentado de Excel, usted puede estar pensando que ya se puede hacer todo lo que potencia la consulta hace mediante el uso de la funcionalidad presente en la ficha de datos en combinación con las fórmulas de Excel y tal vez algunos de VBA, sin tener que aprender una nueva herramienta. Eso puede ser verdad hasta cierto punto, pero hay algunos argumentos muy fuertes en favor del uso de energía de consultas sobre estas técnicas bien conocidas y de confianza. En esta sección se hará estos argumentos con cierto detalle.

Más rápidos ciclos de lanzamiento Microsoft Excel es una de las aplicaciones más utilizadas. Como resultado, todos los cambios que Microsoft hace a Excel tiene que ser probado a fondo; un fallo de seguridad podría afectar a millones de usuarios de todo el mundo y causar la interrupción no contada en sus organizaciones. Esto significa que Microsoft sólo libera una nueva versión de Excel, en promedio, cada tres años, un ciclo de liberación muy lento para los estándares modernos y una que hace que sea difícil conseguir una nueva funcionalidad a los usuarios rápidamente. La nueva, opción de instalación de streaming para todos los productos de Office, que está disponible para los suscriptores de Office 365, va a resolver este problema y permitir finalmente Microsoft para impulsar cambios en Excel a los usuarios tan pronto como estén disponibles; Sin embargo, pasarán varios años antes de que la mayoría de las organizaciones cambian a lo largo de utilizar este tipo de instalación.

Mientras tanto, por lo que la funcionalidad de BI de energía Excel disponible a través de los complementos de Excel en lugar de integrarlo directamente en Excel Microsoft da mucha flexibilidad que no tendría de otra manera. Las nuevas versiones de potencia de consulta aparecieron en casi todos los meses en la segunda mitad de 2013. A pesar de que el ritmo se ha desacelerado desde entonces, comunicados siguen apareciendo con regularidad. Esto permite que el equipo de alimentación de consulta para liberar nuevas características, corregir errores y responder a los comentarios de los usuarios mucho más rápido que si estuvieran atadas al ciclo de lanzamiento de Office.

Sobre la base de la forma de alimentación de pivote comenzó su vida como un complemento totalmente independiente para Excel 2010 y luego tuvo su motor integrado de forma nativa en Excel 2013, es una suposición razonable que hacen que en la funcionalidad de consulta de energía futura será integrado en Excel en la misma camino. Por el momento, hay una gran cantidad de superposición entre lo que puede lograr en la ficha de datos en Excel y con poder de consulta; en el futuro, la energía de consulta podría ser el sustituto de la ficha Datos.

Por lo tanto, si usted es el tipo de persona que le gusta tener en sus manos una nueva funcionalidad tan pronto como sea posible o si ya se han topado con las limitaciones de la funcionalidad existente de Excel, Power consulta es para usted. Microsoft está realizando una gran inversión en la energía de BI en su conjunto y de alimentación de consulta en particular. Incluso si usted encuentra que la energía de consulta no puede hacer algo que tiene hoy en día, podría darse el caso de que va a ser capaz de hacerlo en un tiempo muy corto.

13 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Conectividad a nuevas fuentes de datos El ejemplo más obvio de donde decenas de alimentación de consulta más funcionalidad nativa de Excel, y donde la brecha se ensancha con cada versión del Poder de consulta, es el número de fuentes de datos que soporta. Consulta de energía es compatible con casi todas las fuentes de datos que se puede acceder a través de la ficha de datos y añade muchos otros nuevos como Facebook, Active Directory, Exchange, SharePoint, alimenta OData y HDInsight. También añade soporte para las conexiones a los servicios Web. Todas estas fuentes de datos serán examinados en detalle en el capítulo 2.

Es cierto que, en el momento de la escritura, hay algunas fuentes de datos que Power consultas no admite todavía: SQL Server Analysis Services, ODBC, y algunas fuentes de datos OLEDB más allá de las principales bases de datos relacionales. Con suerte, estas brechas se abordarán en una versión futura.

Mejora de la conectividad a los actuales Fuentes de Datos Donde el poder de consulta es compatible con una fuente de datos que ya está soportado nativamente por la ficha de datos de Excel, es a menudo el caso de que mejora la funcionalidad nativa. Un ejemplo es la capacidad para raspar los datos de una página web; Poder de consulta es capaz de extraer datos de páginas web mucho más que la funcionalidad nativa es capaz de hacerlo. Otro ejemplo es la forma en que la energía de consulta puede combinar datos de una carpeta que contiene múltiples csv archivos en un único conjunto de resultados con el mínimo esfuerzo.

Automatizar las cargas de datos Poder de consulta le permite reemplazar los procesos manuales para carga de datos en Excel con una serie de pasos automatizados, repetibles que se pueden ejecutar con el clic de un botón. Esto no sólo ahorra tiempo, sino que también es mucho menos propenso a errores. En comparación con VBA, Power consulta es probable que ofrecen un mejor rendimiento y también el mantenimiento mucho más fácil para la automatización de cargas de datos, dado que tantas tareas requieren ningún código en absoluto.

Crear transformaciones y cálculos con facilidad Sobre la base del punto anterior, como se vio en el paso a paso, la energía de consulta hace que sea muy fácil de filtrar, agregar, clasificar, pivote y UNPIVOT, así como para agregar cálculos, con sólo pulsar un botón. Por supuesto, no todo se puede hacer a través de la interfaz de usuario, pero el equipo de desarrollo de energía de consultas ha hecho un excelente trabajo de identificación e implementación de todos los escenarios comunes que deben ser tratados al cargar datos en Excel. Algunos escenarios más complejos requieren que escribir código M; algunos escenarios todavía se manejan mejor usando las fórmulas de Excel o usando DAX una vez que los datos se han cargado en el modelo de datos de Excel. Sin embargo, se le gratamente sorprendido por lo fácil de consultas de energía es utilizar y cuánto aumenta su productividad incluso si usted ya es un genio en las fórmulas de Excel o VBA.

Hacer el trabajo en el origen de datos, no en el escritorio Un aspecto de la forma de alimentación de consulta funciona de que no es evidente pero muy importante es la forma en que siempre va a tratar de empujar el procesamiento de nuevo a la fuente de datos. Por ejemplo, si está utilizando una fuente de datos de SQL Server y la consulta contiene varios pasos, la energía de consulta se traducirá todos los pasos en la consulta en una sola instrucción SELECT SQL, siempre que sea posible. Si la tabla de origen es muy grande, será mucho más rápido para cualquier agregación o filtrado que tendrá lugar en el servidor SQL Server que para el conjunto de la mesa para ser descargado a Excel y el trabajo a realizar allí. Tenga en cuenta que esto no va a ser posible con algunos tipos de fuentes de datos (como archivos de texto) o todas las transformaciones que Power consulta es capaz de hacer. Una discusión completa de este comportamiento, llamado “plegamiento de consulta,” se da en el capítulo 3.

14 www.it-ebooks.info

Capítulo 1 ■ La introducción de la energía de consulta

Las consultas de Acciones entre libros y Usuarios Finalmente, la integración de la energía de BI con el poder Sitios BI significa que se puede reutilizar consultas en varios libros e incluso compartirlos con otros usuarios. libros de Excel tienen una mala reputación por ser los silos de código olvidado y datos obsoletos. Potencia para realizar consultas en cierta medida a tratar de solucionar este problema, al menos en lo que va de carga de datos. Si se piensa en todos los informes de Excel y cuadros de mando que existen en una organización, es probable que un gran número de ellos utilizará los datos de las mismas fuentes de datos y la necesidad de hacer las mismas cosas a que los datos antes de que pueda ser de alguna utilidad . Si una sola consulta, compartido puede ser utilizado para hacer esto en lugar de varias consultas duplicadas (con todo el riesgo de errores o diferentes implementaciones de lógica de negocio que van con eso), a continuación, los informes de Excel serán más rápidas de desarrollar y más consistentes entre sí.

Resumen A estas alturas, usted debe entender lo que hace la energía de consulta y qué papel juega en la suite de BI de energía. También debe estar familiarizado con la interfaz de usuario de energía de consulta y algunos conceptos básicos como las consultas y los pasos. Estos temas sólo han sido tratados en un nivel alto por lo que, de ahora en adelante, este libro se concentrará en los detalles. En el siguiente capítulo, aprenderá acerca de todas las fuentes de datos compatibles con fuentes de consulta y cómo se puede acceder, así como temas relacionados tales como la autenticación y la configuración regional del usuario.

15 www.it-ebooks.info

Capitulo 2

Potencia consultar orígenes de datos

Una de las mejores cosas de la energía de consulta es la amplia gama de fuentes de datos que soporta. Además de los tipos obvios de la fuente de datos, tales como bases de datos relacionales y archivos de texto, es compatible con las fuentes más exóticas como Facebook, Active Directory y OData, que son cada vez más importante en escenarios de autoservicio de BI. En este capítulo se ocupará de que lo más importante primer paso al crear una consulta de hacer una conexión a una fuente de datos y la extracción de los datos en bruto que desea trabajar con Power consulta. Además, proporcionará una visita guiada por todas las fuentes de datos soportadas por el poder de consulta, y también discutirá temas relacionados, tales como la autenticación.

Consulta de bases de datos relacionales bases de datos relacionales son el tipo más común de la fuente de datos para la alimentación de consulta. Los siguientes tipos de bases de datos relacionales son compatibles con la energía de consulta: SQL Server, la base de datos de Windows Azure SQL, Access, Oracle, DB2, MySQL, PostgreSQL, Sybase y Teradata. Desafortunadamente, no hay manera de conectarse directamente a una base de datos relacional que no está en esta lista y, en el momento de la escritura, no hay soporte para las conexiones a otras fuentes de datos ODBC OLEDB o. Afortunadamente (al menos para los propósitos de este libro), la interfaz de usuario para la conexión a cada una de estas fuentes de datos es casi idéntico, por lo que SQL Server se utilizará como el ejemplo principal y luego se enumeran después cualquier diferencia de otras bases de datos.

Conexión a SQL Server Con el fin de crear una nueva consulta consulta de alimentación que se conecta a y extrae datos de SQL Server, primero debe ir a la pestaña de consulta de la cinta de Excel, haga clic en el botón De la base de datos y seleccione la base de datos de SQL Server (como se muestra en Figura 2-1 ).

17 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La Figura 2-1. Desde el botón de base de datos

Una vez que haya realizado esta tarea, un cuadro de diálogo aparecerá que le permite introducir el nombre del servidor y la instancia que desea conectarse y (opcionalmente) el nombre de la base de datos. También puede introducir su propia consulta SQL en este punto si ya ha escrito uno. El cuadro de diálogo se muestra en la figura 2-2 .

18 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La Figura 2-2. El diálogo de conexión de base de datos Microsoft SQL

El cuadro de texto Sentencia de SQL puede tomar cualquiera de una sentencia Select de SQL o una llamada a un procedimiento almacenado. En este último caso, la sintaxis que se utiliza es exactamente el mismo que tendrá que utilizar en SQL Server Management Studio, por ejemplo:

EXEC [sys] [sp_who].; Si esta es la primera vez que se conecta a la instancia especificada de SQL Server, la energía de consulta se abrirá un nuevo cuadro de diálogo preguntando cómo la conexión debe estar autenticado. Conexiones a SQL Server pueden utilizar la autenticación de Windows, lo que significa que sus propias credenciales de Windows serán utilizados para autenticar la conexión y nada más hay que introducir o, si su base de datos está en modo mixto, se puede utilizar la autenticación de SQL Server y entran en una nombre de usuario y contraseña. Cuando se abra el cuadro de diálogo, verá la pestaña de Windows, que se muestra en la figura 2-3 ; usted tendrá que hacer clic en la pestaña de base de datos si está utilizando autenticación de SQL Server, como se muestra en la figura 2-4 . En ambos casos, hay una casilla de verificación para utilizar una conexión cifrada que está activada de forma predeterminada. Si va a conectar a una instancia de SQL Server que no ha sido configurado para aceptar conexiones cifradas, que tendrá que desactive esta casilla. Las credenciales se guardan después de que los haya introducido y utilizado de forma automática para todas las futuras conexiones a la misma base de datos.

19 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La Figura 2-3. El diálogo de autenticación de SQL Server para la autenticación de Windows

La Figura 2-4. El diálogo de autenticación de SQL Server para la autenticación de SQL Server

Si introduce su propia consulta o llamar a un procedimiento almacenado, una vez que se hace clic en OK en el cuadro de diálogo original de la conexión de base de datos Microsoft SQL, la consulta o procedimiento almacenado se llevará a cabo y la ventana del Editor de consultas se abrirán para mostrar los resultados.

■

Nota Si la consulta devuelve un gran número de filas y usted tiene la intención de aplicar transformaciones adicionales (tales como filtros o apartaderos de grupo) en las etapas

subsiguientes dentro de la consulta, que puede ser mejor no usar su propia consulta en absoluto. usted puede obtener un mejor rendimiento que conecta directamente a las tablas subyacentes en su base de datos y el uso de consultas de poder por sí solo para implementar la lógica de la consulta. la razón de este consejo será explicado en detalle en el capítulo 3 en el apartado de consultas plegable.

20

www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Si, por el contrario, no se introduce un nombre de base de datos o salir de la instrucción SQL cuadro de texto en blanco, algunas funciones extra para navegar a través de una base de datos relacional está activado. Lo primero que va a pasar es un nuevo panel llamado el panel de navegación aparece en la parte derecha de la pantalla que le permite seleccionar una o más tablas, vistas, funciones escalares o funciones con valores de tabla en su base de datos, como se muestra en Figura 2-5 .

La Figura 2-5. El panel del navegador

Inicialmente, el panel del navegador sólo le permitirá seleccionar un objeto, pero, si marca la casilla de seleccionar varios elementos, que le permitirá seleccionar varios objetos. Selección de varios objetos dará lugar a varias consultas de consulta de energía que se está creando, una para cada objeto que se ha seleccionado; el botón Editar desaparecerá y nuevas casillas de verificación que le preguntará si desea cargar los datos al modelo y datos de Excel / o directamente a una hoja de cálculo cuando se hace clic en Cargar. Si no marca la casilla seleccionar varios elementos, puede abrir el Editor de consultas haciendo clic en el botón Editar o carga de datos directamente a la hoja de cálculo haciendo clic en el botón Cargar después de haber hecho su selección.

■ Nota Si selecciona una función en el panel del navegador, se importa como una función de consulta de potencia (un tema que se trata en el capítulo 5).

21

www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Navegando a través de una base de datos en el Editor de consultas Si ha importado una tabla de consulta en el poder mediante el panel del navegador, a continuación, algunas funciones extra es activada en el Editor de consultas para navegar a lo largo de las relaciones de su base de datos. Para ilustrar esto, voy a utilizar las siguientes tablas de la base de datos de ejemplo Adventure Works DW de Microsoft: DimDate y FactInternetSales. Estas tablas se muestran en la figura 2-6 . Hay tres relaciones de clave externa definidas entre las dos tablas: la OrderDateKey, columnas DueDateKey y ShipDateKey en FactInternetSales se unen a la columna de DateKey en DimDate.

La Figura 2-6. Adventure Works DW relaciones entre tablas Después de haber importado simplemente la tabla DimDate al poder de consulta utilizando el panel del navegador, verá una serie de columnas que contienen la palabra “mesa” si se desplaza hacia el lado derecho de la tabla una vez que están en el panel Editor de consultas, como muestra en la figura 2-7 . Estas columnas representan los datos de todas las tablas que tienen una relación de clave externa con la tabla DimDate en la base de datos.

22 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-7. columnas de relación en la tabla DimDate Hay dos maneras de navegar de la mesa DimDate a la mesa FactInternetSales. La primera es hacer clic en la palabra “mesa” en cualquiera de las filas en la columna de la FactInternetSales (DateKey). Esto devolverá una tabla que contiene las filas de la columna de FactInternetSales que están relacionados con la fila que ha hecho clic, siguiendo la relación de DateKey en DimDate a DueDateKey en FactInternetSales. Así, por ejemplo, si ha hecho clic en la fila correspondiente a la fecha 21 de de julio de S t 2001 en la tabla DimDate, Power consulta devolvería todas las filas en FactInternetSales que tienen un valor DueDateKey para esa fecha en particular. Además, una vez que haya hecho esto, un nuevo panel aparecerá en el lado izquierdo del Editor de consultas. Este panel (que se muestra en la figura 2-8 ) También se llama el panel del navegador, pero no es el mismo que el panel del navegador vimos anteriormente. (Me referiré a este panel como el panel Editor de consultas Navigator a partir de ahora para evitar confusiones.) Se le muestra la tabla que comenzó en la mesa y de haberse desplazado a. Para volver a donde vienes, sólo tiene que hacer clic en el cuadro correspondiente en el panel Editor de consultas Navigator.

La Figura 2-8. El panel Editor de consultas de navegación

La segunda manera de navegar desde DimDate a FactInternetSales es hacer clic en el icono en la cabecera de la columna que se ve como dos flechas que apuntan hacia la izquierda y la derecha, como se muestra en la figura 2-9 .

Figura 2-9. El icono Expandir

23 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Al hacer clic en este icono, verá dos opciones, que se muestra en la figura 2-10 : Expandir, que llevará a cabo una combinación interna entre las dos tablas, y el agregado, lo que añadirá nuevas columnas a la tabla que contiene los valores agregados de la tabla de destino.

Figura 2-10. El diálogo Expandir / balasto con la opción seleccionada Expandir De manera predeterminada, se selecciona la opción Expandir. Si todas las columnas en la tabla de destino (FactInternetSales, en este caso) se comprueban, obtendrá una tabla que contiene todas las columnas de ambas DimDate y FactInternetSales cuando se hace clic en OK. Debido a que la energía de consulta está realizando una combinación interna, la tabla resultante tendrá tantas filas como hay en la tabla de destino que tienen un equivalente por filas en la tabla de origen. Lo mismo también se puede lograr mediante la selección de la columna en el panel de resultados y haga clic en el botón Expandir en la ficha Transformación de la barra de herramientas del Editor de consultas.

Si selecciona la opción agregada (como se muestra en la figura 2-11 ), Sólo verá las columnas numéricas de la tabla de destino, pero verá cada columna numérica aparece varias veces, una para cada posible función de agregado que se puede aplicar a la misma. Cada caja que compruebe dará lugar a una nueva columna agrega a la tabla de origen que contiene un valor agregado a partir de la tabla de destino.

24 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-11. El diálogo Expandir / balasto con la opción agregado seleccionado

También es posible navegar hacia “arriba” una relación, así como “abajo” una relación. Cuando existe, para una relación adecuada ejemplo, si comenzó en la tabla FactInternetSales y quería buscar un valor de la tabla DimDate-verá una columna de la tabla que contiene el valor “Valor”, como se muestra en la figura 2-12 .

Figura 2-12. columnas de relación en la tabla FactInternetSales Al hacer clic en “Valor” en una celda devolverá un registro de objeto-básicamente la hilera de la tabla de consulta que une a la fila en la tabla que acaba de hacer clic, pero no girados. Un ejemplo de un registro se muestra en la figura 2-13 ; se puede convertir fácilmente en una mesa haciendo clic en el botón de tabla en la barra de herramientas superior.

25 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-13. Un objeto de registro

Como alternativa, puede hacer clic en el icono Expandir en la parte superior de la columna. Esto realizará una unión entre las dos tablas de la misma manera que lo hizo cuando se navega por una relación. La única diferencia aquí es que no hay ninguna opción de valores agregados, por lo que sólo puede expandirse y añadir nuevas columnas que contienen valores de búsqueda de la tabla de destino.

Conexión con otras bases de datos Como se mencionó anteriormente, la experiencia de conexión con otros tipos de bases de datos relacionales con Power consulta es sobre todo igual que lo es con SQL Server, aunque algunas de estas otras bases de datos no son compatibles con toda la funcionalidad descrita anteriormente. Por ejemplo, mientras que la mayoría de las bases de datos le permiten conectarse a tablas o vistas, la mayoría no admite la conexión a las funciones. Otra consideración es que antes de poder conectarse a estas bases de datos, es necesario asegurarse de que tiene los componentes de cliente correctos instalados en su PC. Los detalles completos de lo que se admite para cada base de datos relacional, los números de versión mínimos de los componentes de cliente requiere, y enlaces de descarga para esos componentes se pueden encontrar aquí:

http://tinyurl.com/PQRelDataSource

La extracción de datos desde archivos Después de bases de datos relacionales, formatos de archivos de datos, tales como CSV es probable que sea el tipo de uso más frecuente de la fuente de datos para la alimentación de consulta. Esta sección cubre todos los muchos formatos de archivo diferentes, compatibles con fuentes de consulta como CSV, TXT, Excel, XML, y JSON.

26 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Trabajar con archivos CSV El valor separados por comas ( CSV) familia de formato de archivo de texto es ampliamente utilizado para el intercambio de datos debido a su simplicidad. Hay una gran cantidad de diversidad en cómo se implementa el formato, pero, en general, cualquier archivo de texto que contiene datos dispuestos en los registros (por lo general, donde cada línea representa un solo registro), donde cada registro se divide en campos por un único delimitador de caracteres, y donde cada registro tiene los mismos campos, puede ser referido como un archivo CSV. La mayoría de los archivos CSV utilizan la extensión de archivo “.CSV” pero eso no es siempre el caso, y algunos pueden utilizar la extensión “.txt”.

El archivo 01 de enero de Sales.CSV en la carpeta de demostración para este capítulo es un ejemplo sencillo de una CSV expediente. Sus contenidos se pueden ver en la figura 2-14 .

Figura 2-14. Un típico archivo CSV

Para importar estos datos utilizando la energía de consulta, es necesario hacer clic en el botón De archivo de la ficha de alimentación consulta de la cinta y luego seleccione De CSV como se muestra en la figura 2-15 .

Figura 2-15. El De CSV y de los botones de texto

27 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Una vez hecho esto, verá un cuadro de diálogo solicitando la ubicación del archivo CSV. Cuando haya entrado en el lugar y se hace clic en OK, se importan los contenidos del archivo. Poder de consulta tiene una gran cantidad de inteligencia incorporada que detecta la cual delimitador está utilizando, ya sea la primera línea del archivo contiene encabezados de columna, y así sucesivamente, que generalmente funciona muy bien. De hecho, cada vez que se intenta abrir un archivo de texto que parece que podría ser una CSV archivo, incluso cuando se utiliza el botón De Texto en la cinta, la energía de consulta utilizará esta información para convertir los datos en ella en una tabla. los CSV archivo en la figura 2-14 aparecerá en el Editor de consultas, como se muestra en la figura 2-16 .

Figura 2-16. El archivo CSV se muestra en la figura 2-14 en el Editor de consultas

■

Nota el poder de consultas no utilizará una schema.ini presentar para ayudar a entender el formato de una CSV archivo, incluso si uno está presente.

Trabajar con archivos de texto Si desea importar datos de un archivo de texto que no está formateado como una CSV archivo y que realmente hace contener texto (tal como la que se muestra en la figura 2-17 ), Se puede utilizar el botón De Texto, visible en la figura 2-15 .

Figura 2-17. Un archivo de texto típico

archivos de texto como este mesas con convertirse en una columna y una fila para cada línea una vez que hayan sido importados en la energía de consulta. Figura 2-18 muestra lo que el archivo de texto en la figura 2-17 parece que en el Editor de consultas.

28

www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-18. El archivo de texto se muestra en la figura 2-17 en el Editor de consultas

Trabajar con archivos XML Importación de una XML archivo es un poco más compleja que la importación de datos desde una CSV presentar porque, a diferencia CSV archivos, los datos de una XML archivo no es por lo general de tabla en la estructura. Afortunadamente, una vez que esté en la ventana del Editor de consultas, la energía de consulta le permite navegar a través de la estructura de una XML presentar para encontrar los datos que necesita de la misma manera que se puede navegar por la estructura de una base de datos SQL Server.

Para importar un archivo XML primero tiene que hacer clic en el botón De Archivo y luego en Desde XML. Una vez más un diálogo para abrir archivos aparecerá y se puede seleccionar la ubicación del archivo XML. Figura 2-19 muestra el contenido de un archivo XML simple.

Figura 2-19. Un típico archivo XML Una vez que el archivo se ha importado en la energía de consulta, la figura 2-20 muestra lo que el Editor de consultas se verá así.

Figura 2-20. El contenido de un archivo XML en el Editor de consultas

29 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Hay dos maneras de llegar al nivel de los datos de los empleados, dependiendo de si desea ver solo los datos de los empleados o si desea ver los datos de la empresa, también. La primera forma es haciendo clic en el valor “Tabla” en la columna “empleados”; que volverá otra tabla con una columna y una fila, que a su vez contiene el valor “Tabla”, hacer clic que le dará la tabla mostrada en la figura 2-21 , Que contiene sólo los datos de los empleados.

Figura 2-21. datos de los empleados perforados hacia abajo desde un archivo XML

Como alternativa, puede hacer clic en el icono de expandirse en la columna de “empleados” de la tabla que se muestra en la figura 2-20 , escoger

el valor por defecto “Expandir” opción, y haga clic en OK. Si lo hace de nuevo, el resultado final será como se muestra en la figura 2-22 , Donde se pueden ver los datos de nivel de empresa se incluye junto con los datos de los empleados.

Figura 2-22. datos de los empleados se expandió desde un archivo XML

Trabajar con archivos JSON Aunque no hay ninguna opción en la interfaz de usuario para la importación de datos desde JSON archivos, Power consulta tiene soporte completo para ellos. Es frecuente encontrar JSON archivos si está utilizando servicios web como fuentes de datos. Figura 2-23 muestra el contenido de un sencillo JSON expediente.

Figura 2-23. Un archivo JSON sencillo

Para importar datos de una JSON archivo, haga clic en el botón De Texto en el tabulador de consulta de la cinta, cambiar la extensión del archivo desplegable “Todos los archivos”, seleccione la ubicación del archivo y haga clic en OK. Mientras que la XML archivo en la sección anterior fue tratado como una tabla cuando se cargó, en este caso, verá un objeto de registro cuando el Editor de consultas se abre, como se muestra en la figura 2-24 .

30 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-24. El contenido de un archivo JSON en el Editor de consultas

En este caso, la única opción para alcanzar el nivel de los datos de los empleados es hacer clic en el valor “Lista” en el “empleados” fila, lo que pone de manifiesto dos enlaces “Record”; clic en uno de estos enlaces “Record” devolverá los datos para un empleado, como se muestra en la figura 2-25 . Observe cómo, en este caso, el panel Editor de consultas navegador realiza un seguimiento de dónde ha perforado en el archivo.

Figura 2-25. datos de los empleados de un archivo JSON

Trabajar con archivos Excel Poder de consulta puede extraer datos de archivos que no sea el que está trabajando actualmente con Excel. Cuando se hace esto, se trata a cada hoja de trabajo en el archivo de Excel como una tabla externa; También puede extraer los datos de las tablas y rangos con nombre, pero no las células específicas. Figura 2-26 muestra una hoja de trabajo de un simple libro de Excel que contiene algunos datos de ventas (hay una segunda hoja de cálculo en el mismo libro con una tabla que contiene datos estructurados de forma idéntica dentro de un mes).

Figura 2-26. Los datos de ventas en una hoja de cálculo de Excel

Para importar datos de este libro mediante la energía de consulta, es necesario hacer clic en el botón De Archivo y luego en el botón A partir de Excel en el tabulador de consulta de la cinta, seleccione la ubicación del archivo de Excel y haga clic en OK. Al hacer esto, el panel del navegador aparecerá en el lado derecho de la hoja de trabajo actual y se puede seleccionar qué hojas de cálculo, tablas o rangos con nombre que desea cargar los datos de en el libro de destino, como se muestra en la figura 2-27 .

31 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-27. El panel del navegador que muestra las hojas de cálculo en el libro de destino Al igual que cuando va a importar datos de SQL Server, la selección de un solo elemento se encenderá los botones de edición y de la carga. Al hacer clic en el botón Editar se abrirá el Editor de consultas, y haciendo clic en el botón Cargar cargará los datos en una nueva hoja de cálculo en el libro actual. Si marca la casilla “Seleccionar varios elementos” y selecciona más de un elemento, el botón Editar desaparece pero las nuevas casillas de verificación que aparecen dando la opción de importar datos a una hoja de cálculo o en el modelo de datos de Excel cuando se hace clic en Cargar.

Trabajar con carpetas y archivos múltiples Poder de consulta puede tratar el sistema de archivos de Windows como una fuente de datos, y si bien esto es en sí mismo útil, sino que también expone una de las características más prácticas de alimentación de consulta: la capacidad de combinar datos de múltiples archivos de texto, de idéntica estructura.

Trabajando con los datos del sistema de archivos de Windows Para cargar datos desde el sistema de archivos de Windows, haga clic en el botón Desde archivo en el tabulador de consulta de la cinta y haga clic en Desde carpeta. Al hacer esto se abrirá un cuadro de diálogo solicitando una ruta de carpeta, como se muestra en la figura 2-28 .

Figura 2-28. La elección de una carpeta para la opción Desde Carpeta

Al hacer clic en Aceptar se abrirá el Editor de consultas, y verá una tabla que contiene una fila para cada archivo en la carpeta que haya especificado y en todas las subcarpetas dentro de esa carpeta (aunque sí las subcarpetas no se muestran), similar al que se ve en la figura 2-29 .

32 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-29. El contenido de una carpeta que se muestran en el Editor de consultas

Al hacer clic en el icono, que funciona exactamente de la misma manera como el icono Expandir en otras fuentes de datos Expandir en la columna Atributos de esta tabla le permitirá añadir columnas adicionales que contienen atributos de archivo de la tabla.

La combinación de datos desde archivos de texto múltiples En figura 2-29 , Se puede ver que cada fila de la columna “Contenido” contiene el valor “binario”. Al hacer clic en “binario” para una fila importará el archivo que representa esa fila. Además, hay un icono al lado del nombre de la columna en la columna “contenido” que se ve como dos flechas que apuntan hacia abajo, hacia una línea horizontal. Este es el icono Combine, que se muestra en la figura 2-30 . Al hacer clic en este icono, Power consultas intentará combinar todos los archivos binarios listados en la tabla en un archivo binario único que contiene todos los datos de todos los archivos. Así, por ejemplo, si usted tiene una tabla que enumera una serie de idéntica estructura CSV archivos, haga clic en el icono Combinar importará todos los datos de todas las CSV archivos muestran. Esta técnica no funciona para todos los formatos de archivo, sin embargo, (archivos de Excel no se pueden combinar de esta manera, por ejemplo) y que sólo funcionará bien si cada uno de los archivos compartidos con el mismo formato. Eso significa que si usted está tratando de combinar múltiples CSV archivos, todos ellos deben contener los mismos campos en el mismo orden; si no lo hacen, el combinado tendrá éxito, pero los datos devueltos serán muy difíciles de trabajar.

Figura 2-30. El icono Combine Los archivos que se muestran en la figura 2-29 son CSV archivos similares al que se muestra en la figura 2-16 . Al hacer clic en el icono Combinar

en este caso significa que la energía de consulta combinará los archivos, tratar el resultado como una sola CSV presentar, y la salida de la tabla mostrada en la figura 2-31 , Que contiene todas las filas de todos los archivos que se muestran.

Figura 2-31. La salida de la operación Combinar

33 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Trabajo con datos del actual libro de Excel Poder de consulta es capaz de utilizar los datos de las tablas en el actual libro de Excel como fuente de datos para una consulta. Los datos tienen que ser formateada como una mesa, sin embargo-Power de consulta no puede leer los datos de las celdas individuales o rangos con nombre.

Figura 2-32 muestra una hoja de cálculo de Excel con una tabla de datos en el mismo. La tabla tiene el nombre “SalesTable.” (Usted puede encontrar y cambiar el nombre de una tabla haciendo clic dentro de la tabla e ir a la pestaña Diseño en la cinta de Excel.)

Figura 2-32. Una tabla de Excel

Si se selecciona una celda dentro de la tabla y haga clic en el botón De la tabla en la pestaña de consulta de la cinta, el Editor de consultas se abrirá y todos los datos de la tabla será importado, como se muestra en la figura 2-33 . El nombre de la consulta será la misma que la tabla que ha importado.

34 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-33. El contenido de una tabla de Excel en el Editor de consultas

■ Nota

Si hace clic en una celda de la hoja de trabajo actual que no es parte de una mesa y luego clic en el botón De la tabla, consulta el

poder le preguntará si desea que la selección actual a tener el formato de una tabla en Excel; Si hace clic en Aceptar, se creará una nueva tabla y que se importarán los datos de ella. En Excel sí hay varias maneras de dar formato a un rango de celdas como una mesa, por ejemplo, mediante la selección de las células y luego clic en el botón de la tabla en la ficha Insertar de la cinta. Es mejor utilizar estas opciones y crear la tabla manualmente en lugar de dejar que la consulta poder crear la tabla para usted ya que al hacerlo le da la oportunidad de dar un nombre significativo antes de crear la consulta.

Trabajo con datos de la Web Hay un número de diferentes formas de acceder a los datos de la web utilizando la energía de consulta: Se puede raspar datos de las páginas web, puede llamar a los servicios web, e incluso se puede utilizar la propia funcionalidad de búsqueda de datos incorporada en la tela de la energía de consulta.

Raspado de datos de páginas Web Durante mucho tiempo, Excel ha permitido que raspar los datos de las tablas HTML en los sitios web. Poder de consulta se extiende esta funcionalidad, ya que permite tratar el HTML devuelto por una página web como una fuente de datos. Esto significa que la energía de consulta es capaz de raspar los datos de un número mucho mayor de páginas web que la función de Excel nativo aunque, en el momento de la escritura, la energía de consulta tiene una interfaz mucho menos fácil de usar para esto.

Figura 2-34 muestra parte de una página web, http://sqlbits.com/information/Pricing.aspx , que contiene datos formateados como una tabla.

35

www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-34. Parte de una página web que contiene una tabla de datos Para importar estos datos en potencia consulta, haga clic en el botón De Web en el tabulador de consulta de la cinta, introduzca la URL de la página web en el cuadro de diálogo que aparece, y haga clic en OK. Una vez hecho esto, el panel del navegador aparecerá en la parte derecha de la pantalla, como se muestra en la figura 2-35 .

36 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-35. El panel del navegador para una página Web

En este caso, el panel del navegador mostrará las tablas probables que puede que desee importar (en este caso, hay una, se muestra como “tabla 0”), así como todo el código fuente HTML de la página, que está catalogado como “ , aparece el documento.”Si se mueve el puntero del ratón sobre una de las mesas se muestra un cuadro colgante mostrar una vista previa de los datos de esa tabla. Una vez más, existe la opción para seleccionar varios elementos y, o bien editar la consulta o de carga de datos directos de la hoja de cálculo o el modelo de datos de Excel. La selección de “Mesa 0” y haciendo clic en Editar y luego se abre el Editor de consultas para mostrar los datos de esa tabla, como en la figura 2-36 .

37 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-36. Los datos de una página Web en el Editor de consultas

es necesaria una limpieza adicional y filtrado para obtener los datos en una forma utilizable. Vamos a cubrir las técnicas necesarias para hacer esto en el capítulo 3. Si en lugar de hacer clic en “Documento” en el panel Navegador, el Editor de consultas se abrirá y usted será capaz de navegar a través del código HTML en más o menos la misma manera se puede navegar a través de una XML presentar, como se vio anteriormente en este capítulo. Encontrar los datos que necesita en el HTML de todos, pero las páginas web más simples es extremadamente difícil. El mejor enfoque es hacer clic en el botón Expandir en cualquier columna que tiene uno hasta que pueda ver los datos que necesita. Sólo entonces se debe iniciar la filtración de las filas y columnas que no necesita.

Llamar a un servicio Web Muchos sitios web hacen que los datos disponibles a través de servicios web. Poder de consulta puede realizar consultas en los servicios web RESTful (es decir, los servicios web que se pueden consultar utilizando sólo la información que pasa a través de una URL) utilizando el botón Desde Web. En lugar de introducir la dirección URL de una página web, sólo tiene que introducir la dirección URL del servicio web. Las respuestas de los servicios web RESTful normalmente devuelven datos en XML o JSON formato, y el Poder de la consulta se reconoce el formato utilizado de forma automática. Cuando se consultan servicios web utilizando el botón Desde Web a través de la interfaz de usuario, fuente de consulta sólo es capaz de emitir peticiones GET; es posible emitir peticiones POST y utilizando un código personalizado.

En la mayoría de los casos, tendrá que escribir su propio código M para trabajar con servicios web correctamente. Más detalles sobre cómo hacer esto se pueden encontrar en el capítulo 5. También hay un ejemplo práctico de cómo llamar a un servicio web en el Capítulo 8.

Hallazgo de datos utilizando la energía de consulta en línea Búsqueda El botón Buscar en línea en el tabulador de consulta de la cinta es en realidad la puerta de entrada para acceder a dos fuentes de datos diferentes. En primer lugar, le permitirá consultar un número de fuentes de datos públicas curada por Microsoft, y en segundo lugar, si usted tiene una renta básica de energía para Office 365 de suscripción, que le permitirá buscar datos que usted u otras personas han compartido a través del Poder Catálogo de BI de datos.

Búsqueda de datos pública Al hacer clic en el botón de búsqueda en línea se traducirá en el panel de búsqueda en línea que aparece en la parte derecha de la pantalla dentro de Excel, como se muestra en la figura 2-37 .

38 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-37. El panel de búsqueda en línea

Introducción de un término de búsqueda en el cuadro de texto le proporcione resultados de todas las fuentes de datos públicas que la energía de consultas conoce, como se muestra en la figura 2-38 . Una breve lista de algunas de las fuentes públicas de datos que se pueden consultar se puede encontrar aquí: http://tinyurl.com/PQPublicData .

Figura 2-38. Resultados de la búsqueda en línea

39 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Se cierne sobre uno de los resultados mostrará un panel de mosca de salida que contiene una vista previa de los datos en el resultado de la búsqueda, que contiene una serie de opciones seleccionables:

•

Carga creará una consulta y cargar los datos.

•

Editar se abrirá el Editor de consultas.

•

Términos legales se abrirá un cuadro de diálogo que muestra las condiciones de uso de estos datos.

•

Al hacer clic en uno de los nombres de columna en la sección Columnas destacará la columna en la vista previa de la tabla anterior.

•

Al hacer clic en el enlace en la sección Fuentes de datos se abrirá la página web que es la fuente de los datos.

La búsqueda de consultas compartidos y otros datos de la organización Si usted tiene una potencia de BI para Office 365 de suscripción, también puede buscar las consultas y otras fuentes de datos de la organización que usted o sus colegas han compartido a través del Catálogo de alimentación de BI de datos utilizando la funcionalidad de búsqueda en línea. Los detalles sobre cómo se pueden compartir consultas se dan en el Capítulo 7. Antes de que pueda buscar, tiene que ser firmado en el uso del nombre de usuario de Office 365 y la contraseña asociada con su potencia de BI para Office 365 suscripción. Usted puede inscribirse en cualquiera haciendo clic en el enlace en el panel de búsqueda en línea mostrada en la figura 2-37 o haciendo clic en el botón Iniciar sesión en el tabulador de consulta de la cinta de Excel se muestra en la figura 2-39 .

Figura 2-39. El botón Iniciar sesión Una vez hecho esto, las consultas compartidas y otros datos de la organización aparecerán en sus resultados de búsqueda en una nueva pestaña independiente en el panel de resultados de búsqueda denominada Organización; resultados de la búsqueda de fuentes de datos públicas aparecen en una pestaña llamada pública. consultas compartidas también pueden ser identificados por su icono, puesto de manifiesto en la figura 2-40 Y por el hecho del nombre de la persona que compartió la consulta se mostrará con el nombre de la consulta en el panel de resultados (em de la persona ail se muestra en el panel de mosca de salida).

40 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-40. Una consulta compartida en los resultados de búsqueda en línea

Puede encontrar una lista de las preguntas que han compartido con otros en su organización haciendo clic en el botón compartido en el tabulador de consulta de la cinta se muestra en la figura 2-41 .

Figura 2-41. El botón compartido Al hacer clic en el botón compartido se abrirá el panel Consultas compartida en el lado derecho de la pantalla, como se muestra en la figura 2-42 . Este panel se comporta exactamente de la misma manera que el panel Resultados de la búsqueda en línea en términos de cómo importar datos.

41 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Figura 2-42. El panel Consultas compartidas

Usando la pestaña de búsqueda Al realizar una búsqueda usando el botón de búsqueda en línea, una nueva pestaña aparecerá en la cinta de Excel: la ficha Buscar, que se muestra en la figura 2-43 .

La figura 2-43. La ficha Buscar En la sección Refinar, los botones le permite buscar términos en partes específicas de los metadatos asociados a una consulta. Al hacer clic en uno de estos botones añadirá un filtro avanzado para el término de búsqueda en el formato filtername: (término de búsqueda). Por lo tanto, al hacer clic en el botón Nombre permitirá realizar búsquedas de consulta o conjuntos de datos que tienen un texto específico en sus nombres; por ejemplo, Nombre: (John Wayne) buscará la consulta o conjuntos de datos que tienen el texto “John Wayne” en su nombre, como se muestra en la figura 2-44 . filtros avanzados múltiples se pueden utilizar en una consulta, y que se pueden combinar con los términos de búsqueda regulares. filtros avanzados también pueden ser escritos manualmente sin hacer clic en los botones y también se pueden eliminar como cualquier otro texto.

42 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-44. El uso de un filtro avanzado en el nombre

La siguiente es la lista completa de los filtros avanzados:

•

nombre:() los filtros de texto en el nombre de la consulta o el conjunto de datos.

•

descripción:() los filtros de texto en la descripción de la consulta o el conjunto de datos.

•

desde:() los filtros de consulta o conjuntos de datos compartidos por personas específicas o sitios web. Para las personas, se puede suministrar una parte o la totalidad de una dirección de correo electrónico; para sitios web, se filtrará en el nombre del sitio. Por ejemplo, a partir de: (cwebb) buscará consultas compartidos por los usuarios con el texto “cwebb” en su dirección de correo electrónico y a partir de: (data.gov) buscará los conjuntos de datos de www.data.gov/ .

•

fuente de datos:() los filtros de consulta o conjuntos de datos de una fuente específica de datos subyacente. Por ejemplo, origen de datos: (Wikipedia) buscará los datos de Wikipedia y

origen de datos: (salesdatawarehouse) buscará los datos de una base de datos llamada “salesdatawarehouse.”

•

LastModifiedDate: que permite la búsqueda de consultas modificadas en un intervalo determinado. Al hacer clic en el botón de la semana se muestra en la figura 2-43 abre un cuadro desplegable con una serie de intervalos de fechas incorporados; por ejemplo, LastModifiedDate: este año volverá consultas modificadas en el año natural en curso.

•

ColumnName :() los filtros de texto que aparece en los nombres de columna.

•

iscertified: filtros compartidos consultas dependiendo de si han sido certificados por un administrador de datos o no. Por ejemplo, iscertified: Sí sólo devuelve consultas compartidas que han sido certificados. consultas certificados serán descritos con más detalle en el capítulo 7; En pocas palabras, una consulta certificado debe devolver datos más confiable que una consulta no certificadas.

Por último, al hacer clic en el botón Búsquedas recientes mostrará una lista de los últimos términos de búsqueda.

El uso de otras consultas como fuentes de datos Hay un número de diferentes formas en que las consultas de alimentación consulta se puede utilizar para alimentar datos a otras consultas en el mismo libro: mediante el uso de otra consulta como fuente de datos, mediante la duplicación de una consulta existente, y haciendo referencia a un valor individual de otra consulta .

Hacer referencia a las consultas enteras Una consulta de alimentación de consultas puede utilizar otra consulta define en otra parte en el mismo libro como fuente de datos; esto se conoce como “referencia” una consulta. Puede parecer extraño a querer hacer esto, pero en ciertas circunstancias se distribuyan las consulta de gran tamaño en varias partes más pequeñas puede hacer que sea más fácil de desarrollar y mantener. Además, si usted tiene muchas consultas que comparten la misma lógica, la separación de la lógica de que en una consulta independiente puede evitar que se vuelva a repetir el mismo código en muchos lugares.

43 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Hay dos maneras de hacer referencia a otra consulta. En primer lugar, puede hacerlo desde el panel de libro Consultas. Si no se muestra actualmente, puede hacerlo visible haciendo clic en el botón de libro en el tabulador de consulta de la cinta, como se muestra en la figura 2-45 .

Figura 2-45. El botón Libro de Trabajo El panel de libro de consultas muestra una lista de todas las consultas en el libro actual. Haga clic en una consulta como se muestra en la figura 2-46 y luego seleccionando referencia dará lugar a una nueva consulta que se está creando que utiliza la consulta que ha hecho clic en como fuente de datos.

La figura 2-46. Hacer referencia a una consulta a través del panel de libro consultas

La segunda manera de hacer referencia a una consulta es desde la ficha Consultas de la cinta de Excel. Esto es visible después de una consulta ha sido cargado en una tabla en la hoja de trabajo. Como se puede ver en la figura 2-47 , Contiene las mismas opciones que el menú del botón derecho se muestra en la figura 2-46 , Y haciendo clic en el botón de referencia tiene exactamente el mismo efecto que la opción del menú del botón derecho de Referencia no en el panel de libro Consultas.

44 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-47. La ficha Consulta

Cuando hace referencia a una consulta en otra consulta, hay que tener mucho cuidado porque el cambio de su consulta de origen podría romper cualquier tipo de duda que lo utilizan.

Duplicación de consultas También es posible crear una copia de una consulta existente, ya sea mediante el uso de la opción del menú del botón derecho duplicados en el panel de libro consultas o haciendo clic en el botón Duplicar en la ficha Consulta. Al duplicar una consulta, no hay dependencia entre la nueva consulta y la consulta original en la forma en que hay cuando hace referencia a una consulta de usted puede editar la consulta original y la nueva consulta se verá afectado.

Uso de valores individuales de las consultas Por último, también es posible tomar los valores individuales de las células en las consultas existentes y usar estos valores como el punto de partida para una nueva consulta. Esto sólo es posible dentro del Editor de consultas, y se puede hacer esto haciendo clic derecho en una celda que contiene un valor en el panel de resultados y seleccionando Agregar como Nueva consulta, como se muestra en la figura 2-48 .

La figura 2-48. La opción de agregar como Nueva consulta

Una vez hecho esto, aparecerá un cuadro de diálogo que le indica que una nueva consulta ha sido creado con éxito y cómo se llama. Al hacer clic en Aceptar, se puede encontrar esta nueva consulta al cerrar el Editor de consultas y mirando en el panel de libro Consultas.

45 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Cuando hace referencia a un valor de esta manera, la energía de consulta creará una consulta duplicada que contiene todos los pasos hasta e incluyendo la etapa que tenía abierta en el Editor de consultas cuando se hace clic en la celda, además de un paso adicional que devuelve sólo el valor en la celda que ha hecho clic. Por lo tanto, esta opción es más parecido a la opción Duplicar que la referencia de opción de que no hay dependencia de la consulta de origen, y los cambios en la consulta de origen no se romperá la consulta recién creada.

Fuentes de datos OData OData es una parte muy importante de la estrategia de BI de autoservicio de Microsoft. Una descripción completa de lo que se puede encontrar en www.odata.org/ , pero se puede resumir como “ODBC para la web.” OData es un estándar para la implementación de servicios web RESTful esa consulta o actualizar datos y, como tal, proporciona una interfaz uniforme para una amplia variedad de diferentes tipos de fuentes de datos. Parte del atractivo de OData es que hace un vistazo servicio web como una base de datos relacional: lo que llama “entidades” son básicamente las tablas de datos, las entidades han definido las relaciones entre ellos al igual que las relaciones de clave externa, y una consulta OData es muy similar a una consulta SQL, pero expresado en forma de una URL. Microsoft es uno de los principales patrocinadores de OData, pero SAP e IBM también están apoyando. Además, Microsoft está llevando a cabo con entusiasmo servicios web OData a través de una gran cantidad de sus productos como SharePoint, Windows Azure, y Dynamics CRM en línea. Microsoft también ha hecho que sea muy fácil para los desarrolladores implementar OData alimenta en sus propias aplicaciones, por ejemplo, utilizando Visual Studio interruptor de la luz. Poder de consulta tiene soporte para fuentes de datos OData genéricos, y también tiene más interfaces especializadas para determinados tipos de fuentes de datos OData como el mercado de Windows Azure.

Trabajar con genéricos OData Servicios Web El servicio de prueba disponible a partir www.odata.org/ es un buen ejemplo que se puede utilizar para ilustrar el apoyo del Poder consultas para OData. Lo primero que debe saber acerca de cualquier servicio web OData que se está trabajando es su URL raíz del servicio, que proporciona información sobre los recursos disponibles de esta fuente de datos en particular. La URL raíz de servicio del servicio de prueba es http://services.odata.org/OData/OData.svc/ .

En el tabulador de consulta de la cinta, si hace clic en el botón De Otras fuentes de datos y haga clic en Desde OData RSS, aparecerá un cuadro de diálogo preguntando por la dirección URL del feed OData que desea trabajar. Entrando en la dirección de arriba se abrirá el panel Navigator en el lado derecho de la pantalla, como se muestra en la figura 2-49 .

La figura 2-49. El panel del navegador para un servicio web OData A estas alturas, usted debe estar familiarizado con la forma en que funciona el panel del navegador. Lo que se ve aquí por este feed OData es muy similar a lo que viste cuando se conecta a una base de datos relacional. Por debajo de la URL raíz de servicio es una lista de las entidades y las operaciones de servicio, que se tratan como tablas de datos y funciones, respectivamente. La selección de la entidad

46 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

“Productos” y luego haciendo clic en el botón Editar para abrir el Editor de consultas mostrará una tabla que contiene todos los datos de esa entidad, como se muestra en la figura 2-50 .

La figura 2-50. Los datos de una entidad OData en el Editor de consultas

Al hacer clic en la tabla de registro y enlaces que son visibles en algunas columnas le permitirá navegar desde una entidad a otra de la misma manera que se puede navegar entre las tablas de una base de datos relacional.

Trabajar con datos de Excel los libros almacenados en SharePoint Al cargar un libro de Excel en SharePoint (ya sea en las instalaciones o SharePoint Online), cada mesa en ese libro se expone como una alimentación de OData que pueden ser utilizados en la energía de consulta, un gran ejemplo de la utilidad de OData en el mundo real. Figura 2-51 muestra un libro de Excel que contiene una tabla que se muestra en Excel Web App.

La figura 2-51. Un libro de Excel que contiene una tabla en la vista Excel Web App

47 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La dirección URL de una mesa en un libro de Excel almacenado en SharePoint es el siguiente formato (las tres partes de la URL que necesita reemplazar, el dominio, la ruta y el nombre del libro de Excel, y el nombre de la tabla, están en negrita):

https://mydomain.sharepoint.com/_vti_bin/ExcelRest.aspx/mydocument.xlsx/OData/TableName En este caso particular, la URL para el libro es el siguiente: https://crossjoinpowerbi.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/SalesData.xlsx/ OData / SalesTable

De clic en el botón Feed OData y entrar en la dirección URL de la tabla anterior se abrirá el Editor de consultas y mostrar los datos de la tabla, junto con algunas columnas adicionales, como se muestra en la figura 2-52 .

La figura 2-52. Los datos de un feed OData Excel en el Editor de consultas

Trabajo con datos de listas de SharePoint Además de ser capaz de extraer datos de las tablas de Excel almacenados en SharePoint, la energía de consulta también se puede extraer datos de listas de SharePoint. Esto es posible haciendo clic en el botón de otras fuentes y luego haciendo clic en el botón De la lista de SharePoint. Una vez hecho esto, aparecerá un cuadro de diálogo preguntando por la dirección URL del sitio de SharePoint. Una vez que haya entrado en una URL y se hace clic en OK, el panel del navegador aparecerá y mostrará todas las listas de SharePoint en ese sitio, como se muestra en la figura 2-53 .

48 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-53. listas de SharePoint que se muestran en el panel Navigator

Como era de esperar, la selección de una lista en el panel del navegador y hacer clic en el botón Editar se abrirá el Editor de consultas y mostrar el contenido de dicha lista en una tabla, junto con una gran cantidad de columnas adicionales (no muy útil), como se muestra en la figura 2-54 .

La figura 2-54. El contenido de una lista de SharePoint que se muestran en el Editor de consultas

Trabajo con datos del mercado de Windows Azure El mercado de Windows Azure ( https://datamarket.azure.com/ ) es un sitio web de Microsoft donde se puede descargar los datos de terceros a utilizar para fines de análisis. Un ejemplo de que esto podría ser útil es si fueras un minorista de estudiar el efecto de las condiciones meteorológicas en las ventas en sus tiendas. Se podría descargar los datos meteorológicos históricos y combinarlo con sus propios datos de ventas para ver si hay alguna correlación entre los dos. Algunos de los datos disponibles en el mercado se deben comprar, pero hay una gran cantidad de datos libres disponibles allí también. Para ser sinceros, muchos de los conjuntos de datos (especialmente los libres) que no son muy útiles en absoluto; Dicho esto, hay algunos servicios muy útiles ocultos allí como el Bing API de búsqueda y los nuevos datos se está agregando con regularidad. Para utilizar los datos del mercado de Windows Azure en el poder de consulta, primero tiene que suscribirse a algunos conjuntos de datos. Para ello, tiene que ir al sitio web de Windows Azure Marketplace y haga clic en la ficha Datos. Esto mostrará una lista de conjuntos de datos disponibles, como se muestra en la figura 2-55 .

49 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-55. Conjuntos de datos del mercado de Windows Azure

Para suscribirse a un conjunto de datos, haga clic en su nombre y luego haga clic en el botón de inicio de sesión en el lado superior derecho de la pantalla, como se muestra en la figura 2-56 .

50 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-56. Suscribirse a un conjunto de datos en el mercado de Windows Azure Una vez hecho esto, ahora puede volver a la energía de consulta. Para utilizar un conjunto de datos que se ha suscrito a, haga clic en el botón Otro De Fuentes y luego desde Windows Azure Marketplace. Si esta es la primera vez que utiliza esta opción, se le pedirá que firme en Hay dos opciones para hacer esto en el diálogo de autenticación.:

•

El uso de una cuenta de Microsoft (la dirección de correo electrónico que utiliza para iniciar sesión en productos y servicios de Microsoft, que solía ser conocido como un Windows Live ID).

•

Usando una llave cuenta de Windows Azure Marketplace. Esto se puede encontrar visitando la página web de Windows Azure Marketplace, la firma de allí, e ir a la pestaña Mi cuenta.

Una vez que haya iniciado sesión, el panel del navegador aparecerá y mostrará una lista de todos los conjuntos de datos que se ha suscrito, como se muestra en la figura 2-57 . La ampliación de un conjunto de datos se mostrará una lista de tablas y funciones que pueden ser utilizados en la energía de consulta. Al hacer clic en el Añadir fuentes de datos de enlace en la parte inferior del panel del navegador le llevará a la página web de Windows Azure mercado para que pueda suscribirse a nuevos servidores.

51 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-57. conjuntos de datos de Windows Azure Marketplace en el panel Navegador Como es habitual, la selección de una tabla y haciendo clic en Editar creará una consulta en el Editor de consultas que utiliza la tabla como fuente de datos.

Trabajo con datos de Windows Azure Blob y depósitos de almacenamiento de tablas La extracción de los datos almacenados en cualquiera de Windows Azure Blob de almacenamiento o Windows Azure Storage Table to Power consulta es sencillo. El primer paso para hacerlo en ambos casos es hacer clic en el botón Otro De Fuentes y luego o bien el botón de Windows Azure Blob de almacenamiento o el botón de Windows Azure tabla Memoria. Al hacer esto, aparecerá un cuadro de diálogo preguntando por el nombre de su cuenta de Azure Storage. Entonces, si esta es la primera vez que accede a esta cuenta, se le pedirá que introduzca su clave de cuenta (que se puede encontrar en el Portal de administración de Windows Azure, donde se le llama una clave de acceso). El panel del navegador se abrirá. Para Windows Azure Blob de almacenamiento, cada contenedor se convierte en una tabla en el panel Navigator; para Windows Azure Storage Table, cada mesa se convierte en una tabla en el panel Navigator. Selección de una tabla en el panel Navegador y haciendo clic en Editar se abrirá el Editor de consultas de forma normal. Una vez allí, se puede ampliar y valores de la tabla de registro en la forma que ya se ha visto con otras fuentes de datos.

Trabajo con datos de HDFS y HDInsight Si bien no sería correcto llamar Poder consultar una herramienta de “grandes datos”, la energía de consulta puede ser muy útil para importar los archivos almacenados en el sistema de archivos distribuido Hadoop (HDFS) para cualquier distribución de Hadoop que admite el protocolo WebHDFS. También se puede conectar a los archivos almacenados en la distribución basada en la nube de Microsoft de Hadoop, HDInsight. En el momento de la escritura, la energía de consulta es incapaz de generar consultas colmena. 52

www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Para conectarse a Hadoop, haga clic en el botón De Otros orígenes de datos y haga clic en el botón De Archivo Hadoop (HDFS). Se abrirá un diálogo que le pedirá que introduzca el nombre o la dirección IP de su servidor de Hadoop. Una vez hecho esto, el Editor de consultas se abrirá y la experiencia será exactamente igual que si estuviera usando la opción Desde carpeta descrito anteriormente en este capítulo: pueden ser devueltos El contenido de los archivos individuales o múltiples archivos se pueden combinar en una sola tabla de datos. Para conectarse a HDInsight, el proceso es un poco diferente. Haga clic en el botón como de otros orígenes de datos a continuación, en Windows Azure De HDInsight. aparecerá un cuadro de diálogo que le pedirá que introduzca su nombre de cuenta y luego su clave de cuenta (al igual que si se conecta a Windows Azure Blob Storage). Una vez que haya hecho eso, una lista de todos los contenedores de Windows Azure Blob de almacenamiento asociados con su cuenta aparecerá en el panel del navegador. Por defecto, habrá un recipiente con el mismo nombre que el clúster HDInsight. Selección de un recipiente y haciendo clic en Editar se abrirá el Editor de consultas para mostrar todos los archivos almacenados en ese contenedor. Una vez más, desde este punto, la experiencia será exactamente igual que si estuviera usando la opción Carpeta De descrito anteriormente.

Trabajo con datos de Active Directory Poder de consulta puede conectarse a los datos de Active Directory y extraer de allí. Para ello, haga clic en el botón Otro De Fuentes y luego de Active Directory. Un cuadro de diálogo aparecerá preguntando por el nombre del dominio para conectarse a, que puede tener el nombre del dominio que actualmente está en el sistema de llenado ya. Una vez que se hace clic en OK, si esta es la primera vez que se conecta a esta dominio, el diálogo de autenticación aparecerá y se puede utilizar cualquiera de las credenciales de Windows o suministrar un nombre de usuario y contraseña para conectarse. A continuación, el panel del navegador aparecerá en la parte derecha de la pantalla que muestra todos los dominios y subdominios que tiene acceso a; se puede expandir un dominio y ver todos los objetos asociados a ese dominio como usuarios, grupos y equipos.

Trabajo con datos de Microsoft Exchange Si utiliza Microsoft Exchange, puede utilizar la energía de consulta para extraer los datos a partir de ahí los correos electrónicos que ha recibido, su calendario, contactos, tareas y solicitudes de reunión. Para conectar a Exchange, haga clic en el botón Otro De Fuentes y luego desde Microsoft Exchange. Si esta es la primera vez que conecta a Exchange, puede que tenga que introducir su em ail dirección y contraseña en el cuadro de diálogo de autenticación en este punto. Sólo una cuenta de Exchange se puede configurar en el poder de consulta en cualquier momento. Cuando haya hecho esto, el panel del navegador aparecerá en la parte derecha de la pantalla que muestra los diversos tipos de información que se puede extraer, como se muestra en la figura 2-58 .

La figura 2-58. Los recursos de Microsoft Exchange en el panel Navegador

53 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Al seleccionar un elemento y haciendo clic en el botón Editar se abrirá la ventana del Editor de consultas. Si selecciona Correo, verá una tabla que contiene todos los mensajes de correo electrónico que ha almacenado en su cuenta de Exchange, como se muestra en la figura 2-59 ; Calendario mostrará una tabla que contiene todos los elementos en su calendario; La gente va a mostrar una tabla que contiene todos sus contactos; Las tareas se mostrará una tabla que contiene todas sus tareas; y las solicitudes de reunión se mostrará una tabla que contiene todas sus solicitudes de reunión.

La figura 2-59. Los correos electrónicos de intercambio que se muestran en el Editor de consultas

Trabajo con datos de Facebook Consulta de potencia es capaz de conectar y extraer los datos del Facebook Graph tanto para cuentas personales y cualquiera de las páginas de la compañía que administra. Para ello, haga clic en el botón Otro De Fuentes y luego haga clic en el botón Desde Facebook. Al hacer esto, el diálogo que se muestra en la figura 2-60 aparecerá, que le pide alguna información inicial acerca de qué datos desea extraer.

La figura 2-60. El diálogo de conexión de Facebook El cuadro de texto superior mostrada en la figura 2-60 le pide que desea conectarse como. El valor por defecto es “yo”, que conectará la utilización de su cuenta personal. Sin embargo, si usted es un administrador de otra página de Facebook, por ejemplo, para una empresa, puede introducir un nombre de usuario diferente o utilizar un Facebook ID del objeto (ambos de los cuales se pueden encontrar en la pestaña Información de la página de su página en el sitio web Facebook ).

El cuadro de texto inferior le permite elegir una parte particular de la gráfica para volver. La opción por defecto es “-Ninguno-”, lo que significa que el Editor de consultas se abrirá y mostrará un registro raíz de la cual se puede navegar a todos los datos asociados con el usuario que ha elegido en el cuadro de texto superior. Elegir otra opción en este cuadro desplegable irá directamente a una parte específica de la gráfica, como la lista de amigos o la fuente de noticias, y devolver una tabla de datos.

Si no ha iniciado sesión en Facebook, el diálogo de autenticación aparecerá y le pedirá que lo haga cuando se hace clic en OK. Al hacer clic en el botón Iniciar sesión se abrirá otra ventana donde tendrá que introducir su contraseña de Facebook. Una vez hecho esto, haga clic en el botón Guardar para cerrar el cuadro de diálogo de autenticación. Una vez que esté en el Editor de consultas, puede navegar a través del gráfico Facebook haciendo clic en la tabla de registro y enlaces en la forma habitual.

54 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Trabajo con datos de SAP BusinessObjects Los datos de SAP BusinessObjects universos UNX (pero no VNU) se puede acceder desde la energía de consulta haciendo clic en el botón Otro De Fuentes de datos y la selección de SAP BusinessObjects BI Universo. Para que esto funcione, necesitará tener instalados los siguientes componentes de SAP BusinessObjects:

•

SAP BusinessObjects Plataforma versión 4.1 con SP2

•

SAP BI 4.1.2 servicio web REST

Después de haber hecho clic en el botón, se le pedirá que introduzca la URL de su universo servidor SAP BusinessObjects BI4, como se muestra en la figura 2-61 . Esto será en el formato http: // : / biprws. Por defecto, el servicio web REST utiliza el número de puerto 6405.

La figura 2-61. Conexión con un universo de SAP BusinessObjects A continuación, tendrá que elegir el tipo de credenciales que desea utilizar para conectarse al servidor SAP universo BI4 e introduzca sus credenciales. Una vez hecho esto, verá una lista de universos en el panel del navegador. Debajo de cada universo, se puede ver una lista de las dimensiones, atributos y medidas en ese universo, y podrá seleccionar los atributos y las medidas que desea importar datos para, como se muestra en la figura 2-62 .

La figura 2-62. El Panel de navegador que muestra el contenido de un universo

55 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Una vez en el Editor de consultas, puede modificar su selección haciendo clic en el Agregar elementos y colapso Columnas botones, como se muestra en la figura 2-63 .

La figura 2-63. Modificación de una selección en el Editor de consultas

■

Nota en el momento de la escritura, la funcionalidad relacionada con SAP BusinessObjects en Query poder estaba todavía en la vista previa y, por lo tanto, es probable que

cambie en futuras versiones. También, cualquier futuro apoyo de fuentes Servicios de análisis de datos de SQL Server será probablemente muy similar a lo que se muestra aquí.

La reutilización de Fuentes de Datos recientes Una vez que haya utilizado una fuente de datos en el poder de consulta, que aparecerá en la lista de fuentes de datos que se muestran cuando se hace clic en el botón Fuentes recientes en el tabulador de consulta de la cinta de Excel (véase la figura 2-64 ).

56

www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

La figura 2-64. Las fuentes recientes cuadro desplegable

Al hacer clic en una de las fuentes de datos en esta lista se creará automáticamente una nueva consulta que utiliza esa fuente de datos y abrir el Editor de consultas. Al hacer clic en la opción Más Recientes en la parte inferior de la lista desplegable se abre el diálogo Gestionar fuentes recientes, como se muestra en la figura 2-65 , Donde se puede precisar las fuentes de datos a la lista, eliminar las fuentes de datos de la lista, y copiar la ruta de acceso a fuentes de datos de archivos de texto en el portapapeles.

La figura 2-65. El Manejo de diálogo orígenes recientes

Administración de credenciales Usted ya ha visto varias veces cómo el poder de consultas gestiona la autenticación al conectarse a diferentes tipos de fuentes de datos. También puede haber notado cómo el poder de consulta guarda la información de autenticación para futuras conexiones a la misma fuente de datos. ¿Qué pasa cuando el libro se envía a otra persona sin embargo? Poder de consulta guarda todos los datos de autenticación a un archivo cifrado en su disco duro local, por lo que cualquier nombre de usuario o contraseñas que se escriben en el se llevan a cabo de forma segura. Este archivo es independiente de cualquier libro. Si envía su libro a otro usuario o abrirlo en otra máquina, la energía de consulta buscará el archivo cifrado en la nueva máquina para ver si las credenciales se guardan para todas las fuentes de datos utilizadas en ese libro. Si las credenciales están presentes en la nueva máquina, que se utilizan. Si no es así, el usuario se le pide que introduzca nuevas credenciales.

57 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Puede editar las credenciales almacenadas en el equipo local haciendo clic en el botón de configuración de origen de datos en la ficha de alimentación consulta de la cinta de Excel. Esto abre el diálogo de configuración de origen de datos, como se muestra en la figura 2-66 . Al hacer clic en una fuente de datos se iluminará la credencial Editar y Eliminar.

La figura 2-66. El cuadro de diálogo Configuración de la fuente de datos

Si está utilizando una consulta compartida y no tiene acceso a una fuente de datos utilizada por él, es posible que aparezca un enlace “Solicitud de acceso” en la esquina superior derecha cuando aparezca el cuadro de diálogo de autenticación del origen de datos. Este enlace será ya sea una página web o una dirección de correo electrónico para que pueda ponerse en contacto con alguien que sea capaz de conceder el acceso a la fuente de datos. Más detalles acerca de cómo las fuentes de datos pueden ser gestionados por los administradores de datos, incluyendo cómo este tipo de enlace “Solicitud de acceso” puede ser creado, se dan en el Capítulo 7.

La importancia de la configuración regional Si está trabajando con datos de muchos países diferentes, se debe tener en cuenta las variaciones internacionales en los datos tiene el formato manera. Por ejemplo, en los Estados Unidos, los datos 07/01/2015 1 de julio de S t 2015, pero en Europa la misma fecha se leería como el 7 de enero de º 2015. Del mismo modo, en los países de habla Inglés, un período (también conocido como un punto o punto decimal) se utiliza como separador decimal, y se utiliza una coma como separador de miles. En muchos países europeos, es todo lo contrario y se utiliza una coma como separador decimal y un período se utiliza como separador de miles. En esta sección se muestra cómo se puede hacer frente a estas cuestiones en el poder de consulta al importar datos. En el capítulo 3, aprenderá cómo se puede establecer la configuración regional utilizado para las transformaciones específicas.

58 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Establecimiento de una configuración regional predeterminada Para ver cuál es el impacto de los cambios en la configuración regional de energía de consulta, la figura 2-67 muestra el contenido de un archivo de punto y coma separados fecha, el producto y los datos de ventas contiene.

La figura 2-67. Los datos para probar el efecto de diferentes lugares Cuando se crea un nuevo libro de Excel, Power consulta utilizará la corriente local del sistema operativo como su defecto. Se puede ver de qué se trata y anular por ir a la pestaña de consulta de la cinta y mirando el cuadro desplegable de configuración regional, como se muestra en la figura 2-68 .

La figura 2-68. El cuadro desplegable regional predeterminada

59 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Con la configuración regional predeterminada se establece en Inglés (Estados Unidos), las fechas serán interpretados como estar en formato MM / DD / AAAA y las comas en la columna Sales serán interpretados como separadores de miles, si el archivo de la figura 2-68

se importa a la energía de consulta. Dos columnas personalizadas se han añadido a la consulta para que los resultados sean más fáciles de interpretar: uno para devolver el nombre del mes utilizado en la columna de la fecha y un segundo para devolver True si el valor de la columna de ventas es inferior a 3. La salida de la consulta utilizando la configuración regional Inglés (Estados Unidos) es como se muestra en la figura 2-69 ; cada fecha, es en un mes diferente, y cada valor de venta es mayor que 3.

La figura 2-69. Los datos importados con la configuración regional Inglés (Estados Unidos)

Si cambia la configuración regional predeterminada a ser francés (Francia) en su lugar, sin embargo, y actualizar la consulta, verá que las fechas se interpretan como en el formato dd / MM / AAAA ( “janvier” es francés para enero) y las comas en la columna de ventas se interpretan como decimales separadores. Cada fecha está ahora en el mes de enero de y cada valor de venta es ahora menor que 3, como se muestra en la figura 2-70 .

La figura 2-70. Los datos importados con el (Francia) entorno local francés

Es muy importante tener en cuenta que la configuración regional de energía de consulta sólo se utiliza cuando la energía de consulta es la importación y la transformación de los datos; la configuración regional de Windows, utilizado por Excel, se utiliza siempre cuando se muestran los resultados de los datos en Excel. en las Figuras 2-69 y 2-70 , La configuración regional de Windows utilizado es el Inglés (Reino Unido), que utiliza un formato DD / MM / AAAA y un punto como separador decimal. Esto explica por qué el contenido de la columna de fecha se muestran en el formato DD / MM / AAAA y los valores de las ventas que se muestran son utilizar un punto como separador decimal.

CSV archivos y páginas de códigos Al importar CSV archivos, también es posible configurar la página de códigos utilizada para interpretar su contenido. Para ello, abra el Editor de consultas y, en el cuadro Pasos Aplicadas en el lado derecho, haga doble clic en el icono de los engranajes junto al paso que importa la CSV presentar, como se muestra en la figura 2-71 .

La figura 2-71. El icono de los engranajes en la caja Pasos Aplicadas

60 www.it-ebooks.info

Capitulo 2 ■ Las fuentes de datos de consulta de potencia

Una vez hecho esto, se abrirá un diálogo (muestra en la figura 2-72 ) Y se puede utilizar el origen del archivo desplegable casilla para establecer la página de códigos utilizada para interpretar este CSV expediente.

La figura 2-72. El cuadro desplegable Origen del archivo

Resumen En este capítulo, usted ha visto la gran variedad de fuentes de datos que pueden conectarse a la utilización de la energía de consulta. Con suerte, también se han visto las similitudes en la forma en que la energía de consultas trata a todas estas fuentes de datos de manera que una vez que han aprendido a trabajar con una fuente de datos, es muy fácil de aprender a trabajar con todos ellos. En el siguiente capítulo, usted aprenderá acerca de cómo se puede filtrar, ordenar, agregar y manipular los datos ahora que se han cargado en energía de consulta.

61 www.it-ebooks.info

Capítulo 3

La transformación de datos con la energía de consultas

Ahora que se han extraído los datos que la necesidad de su fuente, es muy probable que usted va a querer cambiarla o limpiarla antes de cargarlo en la hoja o en el modelo de datos de Excel. Es raro el caso de que los datos de origen está exactamente en el formato correcto y, por supuesto, aquí es donde la energía de consulta muestra su valor. En este capítulo, usted aprenderá acerca de todas las funciones en el editor de consultas de energía de consulta para filtrar, ordenar, agregación y unpivoting sus datos, y que se llevará a un primer vistazo a cómo se puede utilizar el lenguaje M para definir los cálculos de columnas personalizadas. La mayoría de los ejemplos de este capítulo se utiliza el mismo CSV expediente, Chapter3SourceData.csv, que se puede encontrar en las muestras de este libro. Este archivo contiene la información de ventas con una serie de problemas e inconsistencias que son típicos de los datos del mundo real.

Consultas y Pasos En el capítulo 1, que ha aprendido que las consultas de energía de consulta se componen de una o varias etapas, cada una de ellas, ya sea carga de datos desde un

origen de datos o se aplica algún tipo de cambio a la salida de otro paso. También ha aprendido el paso final en la consulta da el resultado de la consulta. Si carga Chapter3SourceDa en el poder de consulta utilizando el botón Desde CSV, el Editor de consultas muestra los datos que se encuentran en la figura 3-1 .

La Figura 3-1. Los datos de Chapter3SourceData.csv en el Editor de consultas Si nos fijamos en el panel Pasos Aplicadas, se verá que tres pasos se han añadido a su consulta de forma automática, como se muestra en la figura 3-2 .

63 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La Figura 3-2. Pasos crean automáticamente cuando se importa un archivo CSV

El último paso, llamado ChangedType, se pone de relieve y es la salida de ese paso que se muestra en la figura 3-1 . Debido a que es el último paso en esta consulta, este será el resultado de la consulta. Al hacer clic en un paso diferente, mostrar la salida de ese paso. Los tres pasos que se han creado, haga lo siguiente:

•

Fuente carga los datos de la CSV expediente.

•

FirstRowAsHeader toma la primera fila de la CSV archivo y utiliza los valores como los nombres de las columnas.

•

ChangedType aplica automáticamente un tipo de datos para cada una de las columnas. Por ejemplo, la energía de consulta piensa que la columna de Fecha contiene únicamente datos de tipo fecha, por lo que establece el tipo de esa columna para que sea la fecha. Esa columna ahora sólo puede contener valores de fecha.

El paso Fuente tiene un icono de los engranajes junto a él. Como se vio en el apartado de lugares en el capítulo 2, al hacer clic en este icono le permite editar la configuración de cómo la CSV archivo se importa. Cualquier paso en una consulta en la que se utiliza un cuadro de diálogo para configurar algunos ajustes tendrá el mismo icono junto a él. Puede hacer clic en el icono de engranajes si desea que el mismo cuadro de diálogo para volver a aparecer para que pueda cambiar algo.

También puede ver en la figura 3-2 que con el paso ChangedType resaltado, un icono de la Cruz es visible junto a él; clic en este icono se eliminará el paso. Cualquier paso se puede eliminar después de que haya sido seleccionado, no sólo el último en la consulta. Si hace clic en un paso, el Eliminar Hasta opción de menú Fin eliminará el paso actual y los pasos subsiguientes.

El orden de los pasos también se puede cambiar haciendo clic derecho en un paso y seleccionar Subir o Bajar. Pasos También puede cambiar el nombre en el menú del botón derecho, seleccionando Cambiar nombre.

■

Nota mover o eliminar pasos podrían dar lugar a errores en su consulta en otros lugares. por ejemplo, si se elimina un paso que crea una nueva columna en su mesa y se hace referencia

a que la columna en un paso posterior, que paso posterior generará un error. si esto es probable que suceda, Consulta de energía mostrará un cuadro de diálogo de advertencia cuando se intenta eliminar un paso. en general, sin embargo, la consulta de potencia es bastante tolerante con el cambio y, si cambia el nombre de un paso, se actualizará cualquier otro paso con el nuevo nombre.

64

www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Una última cosa a mencionar aquí es que al hacer clic en un paso y ver los resultados que devuelve en el Editor de consultas, la energía de consultas no siempre volver a cargar los datos de origen y volver a calcular los valores. Para hacer que la interfaz de usuario de respuesta, almacena en caché los resultados intermedios en el disco. Por lo tanto, si los datos de origen ha cambiado, es posible que tenga que hacer clic en el botón Actualizar en la ficha Inicio de la barra de herramientas del Editor de consultas para ver más resultados.

■

Nota si se trabaja con grandes conjuntos de datos, consulta de poder puede utilizar una gran cantidad de espacio en disco para contener el caché, los resultados intermedios

que se ve en el editor de búsquedas. la ubicación de este caché es C: \ Users \ nombre de usuario \ AppData \ Local \ Microsoft \ Poder consulta. la documentación menciona que hay un límite flexible de 4 GB para el tamaño de este Cache “suave” ya que este límite se puede superar por períodos cortos de tiempo.

Trabajar con columnas Una de las primeras cosas que usted quiere hacer con sus datos tan pronto como se haya cargado que es para asegurarse de que sus columnas están en el lugar correcto, se nombran apropiadamente, y están utilizando los tipos de datos correctos. Esta sección le mostrará toda la funcionalidad disponible en la energía de consulta para hacer esto.

nombrando Columnas Eliminación de los pasos segundo y tercero (llamados FirstRowAsHeader y ChangedType), que fueron creados automáticamente cuando la muestra CSV fichero ha sido cargado en el poder de consulta, significará el Editor de consultas mostrará la salida de sólo el primer paso, Fuente, como en la figura 3-3 .

Figura 3-3. La salida de la etapa de Fuente En este punto, los nombres de las columnas de la CSV archivo se muestra en la primera fila de la tabla. Para recrear lo que el poder de consulta hizo automáticamente antes y hacer que estos valores los nombres de columna en la tabla, haga clic en el uso Primera Fila Como botón de encabezados de la ficha Inicio en la barra de herramientas del Editor de consultas (muestra en la figura 3-4 ). Una vez hecho esto, un nuevo paso será creada FirstRowAsHeader, y el Editor de consultas mostrará su producción.

sesenta y cinco

www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La Figura 3-4. El uso de la primera fila como encabezados, eliminar columnas y elija las columnas botones Los nombres de columna también se pueden editar manualmente haciendo doble clic en el nombre o haciendo clic derecho en la columna y seleccionar Cambiar nombre. Incluso si cambia el nombre varias columnas, la energía de consulta hará todo lo posible para incluir esos cambios en un solo paso nuevo en lugar de múltiples pasos; este paso se llamará RenamedColumns por defecto.

mover Columnas Las columnas se pueden mover hacia la izquierda o derecha de la tabla arrastrando y colocándolos en el lugar apropiado, o pulsando el botón derecho de la columna y luego seleccionar Mover y, o bien Izquierda, Derecha, A principio o fin. Una vez más, cuando sea posible, la energía de consulta incluirá todos esos cambios en un solo paso, llamados ReorderedColumns por defecto.

eliminación de columnas A menudo es el caso de que al importar datos, no es necesario todas las columnas de su fuente. También es importante que no realiza la importación columnas que no es necesario si va a cargar datos en el modelo de datos de Excel porque estas columnas se incrementará la cantidad de memoria necesaria para almacenar los datos. Puede eliminar columnas de una tabla en un número de maneras. Si selecciona una o más columnas en el panel de resultados y haga clic en, puede elegir la opción de quitar columnas para eliminar las columnas seleccionadas o eliminar otras columnas opción para eliminar todos menos las columnas seleccionadas. Los quitar columnas cuadro desplegable de la ficha Inicio de la barra de herramientas del Editor de consultas tiene las mismas opciones. También puede hacer clic en el botón Elegir columnas en la ficha Inicio de la barra de herramientas del Editor de consultas (se muestra en la figura 3-4 ) Para abrir el cuadro de diálogo Elegir columnas, donde se puede seleccionar todas las columnas que desea conservar o eliminar de su mesa.

Columnas de división Habiendo cambiado el nombre y reordenados algunas columnas, la consulta de ejemplo se verá algo como lo que se muestra en la figura 3-5 .

Figura 3-5. Editor de consultas que muestran columnas se divida y se fusionó

66 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Observe cómo en la columna de la gente de ventas, algunas filas contienen dos nombres en una lista separada por comas. Si desea que cada uno de estos nombres aparecen en una columna separada, haga clic en la columna para seleccionarla y, a continuación, haga clic en el botón Dividir la columna en la ficha Transformación de la barra de herramientas del Editor de consultas y, a continuación, seleccione Por delimitador, como se muestra en la figura 3-6 O hacer la misma selección en el menú del botón derecho.

Figura 3-6. La columna dividida por el botón delimitador

Al hacer esto, el “Dividir una columna por delimitador” aparecerá de diálogo, como se muestra en la figura 3-7 .

Figura 3-7. El “Dividir una columna por delimitador” de diálogo

En este caso particular, las opciones por defecto son los que desea utilizar: Usted desea dividir la columna en cada caso de una coma y terminar con dos columnas. Al hacer clic en Aceptar se traducirá en la columna de la gente de ventas el vigente siendo eliminado de su mesa y de ser reemplazado con dos columnas llamadas de ventas habitantes.1 y Ventas personas.2, que luego se puede cambiar el nombre a algo más significativo, como persona de ventas 1 y 2 Persona de las ventas, como se muestra en la figura 3-8 .

67 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La Figura 3-8. Editor de consultas resultados que muestran columnas divididas

Como ya se habrán dado cuenta, también es posible dividir una columna en varias columnas de un cierto número de caracteres haciendo clic en el número de caracteres Por opción se muestra en la figura 3-6 . El cuadro de diálogo para esto se muestra en la figura 3-9 .

Figura 3-9. El diálogo “Dividir una columna por la posición” A pesar de que no tiene sentido hacer esto, el resultado de dividir el nombre del producto en columnas de tres caracteres se muestra en la figura 3-10 . Todos los ejemplos posteriores de este capítulo se asumirá este paso ha sido suprimido!

68 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-10. El producto columna dividida en columnas Nombre de tres caracteres

La fusión de las Columnas También es posible combinar dos columnas que contienen datos de texto en una sola columna. En figura 3-8 , Se pueden ver dos columnas denominadas nave a nave a la ciudad y país. Para combinar estas columnas en una columna de texto único, haga clic en uno de ellos y luego en la otra mientras mantiene pulsada la tecla Mayús o la tecla Ctrl del teclado, de modo que ambas columnas se seleccionan. A continuación, seleccione la opción Combinar Columnas desde el menú del botón derecho como se muestra en la figura 3-11 . Como alternativa, puede hacer clic en el botón Columnas de combinación en la pestaña Transformar en la barra de herramientas del Editor de consultas. Observe que el botón Combinar las consultas en la ficha Inicio de la barra de herramientas no es para la fusión de las columnas, pero para la fusión de dos consultas.

Figura 3-11. La opción Columnas botón Combinar y 69 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Cuando haya hecho esto, aparecerá el cuadro de diálogo Columnas de combinación, como se muestra en la figura 3-12 Y usted será capaz de

elegir un personaje para separar los valores de las dos columnas en el nuevo, la columna fusionada.

Figura 3-12. El cuadro de diálogo Combinar Columnas

La elección de una coma y hacer clic en OK dará lugar a la nave a nave a la ciudad y las columnas País siendo sustituida por una sola columna que contiene los valores de esas dos columnas, separados por una coma. La nueva columna se llamará fusionada. (De nuevo, es una buena idea para cambiar el nombre de la columna inmediatamente después de algo más significativo, como enviar a la dirección.) El resultado final se muestra en la figura 3-13 .

Figura 3-13. El Editor de consultas que muestra la dirección Enviar a la columna fusionada

La fusión de las columnas también es posible desde la barra de herramientas haciendo clic en el botón Columnas de combinación en la ficha Insertar de la barra de herramientas del Editor de consultas; funciona casi de la misma manera, excepto que las dos columnas originales permanecen en la consulta y se añade una nueva columna que contiene los valores combinados.

Ajuste del tipo de datos de una columna Como ya se ha visto, al importar un CSV presentar al poder de consulta, que automáticamente tratará de establecer un tipo de datos de cada columna. De hecho, la energía de consulta tratará de hacer esto después de otras operaciones, así como la división de una columna. En la mayoría de los casos, la energía de consulta va a hacer un buen trabajo, pero puede elegir su propio tipo de datos para una columna con bastante facilidad haciendo clic en él y luego seleccionar el tipo adecuado en el cuadro desplegable Tipo de datos en la ficha Inicio de la barra de herramientas en el Editor de consultas, como se muestra en la figura 3-14 . Alternativamente, se puede cambiar el tipo, haga clic en la columna, seleccionando Cambiar tipo en el menú del botón derecho, y luego seleccionar el tipo apropiado.

70 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-14. El cuadro desplegable Tipo de datos Los siguientes tipos de datos se pueden utilizar para las columnas en el poder de consulta:

•

Binario, que es para el almacenamiento de secuencias de bytes. Este tipo de datos se puede utilizar para almacenar imágenes y el contenido de archivos de disco.

•

Fecha, que es para almacenar valores de fecha. Poder de consulta puede almacenar cualquier fecha del 1 de enero S t

0001 CE y 31 de diciembre S t 9999 CE en el calendario gregoriano. •

Fecha y hora, que es para almacenar valores que representan tanto una fecha y una hora.

•

Fecha / Hora / Zona horaria, que es para almacenar valores que contienen una fecha, una hora y una zona horaria. Un valor de zona horaria se almacena como una la diferencia en el número de minutos entre una hora local y la hora universal coordinado (UTC).

•

Duración, que es para almacenar valores que representan la diferencia entre dos tiempos, de fecha / veces, o fecha / hora / zonas horarias. La granularidad mínima de un valor de duración es de 100 nanosegundos.

•

Lógico, que sólo puede almacenar valores verdadero o falso.

•

Número, que es para almacenar cualquier valor numérico. Esto incluye números enteros y números fraccionarios; el número se almacena al menos con la precisión de un doble.

•

Texto, que es para el almacenamiento de texto Unicode. Poder de consultas entre mayúsculas y minúsculas, lo que significa que el texto distingue entre mayúsculas y minúsculas. Por ejemplo, el texto “Hello World” se trata como un valor diferente del texto “hola mundo”. Esto tiene implicaciones importantes cuando se trata de hacer las cosas tales como el filtrado y la transformación de los valores de texto.

•

Hora, que es para almacenar los valores de hora del día.

•

Alguna, que se puede utilizar para almacenar cualquier tipo de valor. Cualquier tipo de datos no se muestra en el cuadro desplegable Tipo de datos porque no se puede establecer explícitamente una columna de ser del tipo Any. Sin embargo, al importar datos de algunas fuentes de datos, como una tabla de Excel, se verá que no realiza ninguna selección en el cuadro desplegable Tipo de datos. Esto significa que la columna es de tipo Cualquier. Cuando esto sucede, es una buena idea para seleccionar otro, el tipo más apropiado para la columna como texto o número para que pueda utilizar la funcionalidad especial de alimentación de consulta para estos tipos.

71 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Other data types exist in Power Query, such as Tablas, listas, y Archivos. Si bien ya hemos visto estos valores en las celdas de una tabla, no es posible establecer el tipo de datos de una columna a uno de estos tipos; podrá aprender más acerca de ellos en el capítulo 5. Una columna de cualquier tipo también puede contener valores nulos, que se utilizan para representar la ausencia de datos.

Cambio de tipos de datos y Locales En el capítulo 2, hemos visto cómo la configuración regional puede influir en la forma en que los valores de texto se interpretan cuando se cambia el tipo de una columna. En lugar de establecer una configuración regional predeterminada para el conjunto de alimentación de consulta, también es posible utilizar un entorno local al cambiar el tipo de una sola columna. Esto se puede lograr haciendo clic derecho en una columna, seleccionando Cambiar tipo en el menú del botón derecho, y seleccionando la opción Configuración regional Usando. Al hacer esto, aparecerá el cuadro de diálogo Cambiar tipo de configuración regional, como se muestra en la figura 3-15 .

Figura 3-15. La Cambiar tipo de diálogo con la configuración regional

Este cuadro de diálogo le permite cambiar el tipo de una columna, mientras que la especificación de la configuración regional utilizada para la fuente de datos. Por ejemplo, la muestra CSV archivo contiene el texto “04/03/2014” en una de las filas de la columna de la SalesDate. Si este valor es arrojado a un tipo de fecha utilizando la configuración regional francesa (Francia), esto se interpreta como una fecha en el formato DD / MM / AAAA, y se convertirá en la fecha 3 rd Abril de 2014. Si, por el contrario, este valor es arrojado a un tipo de fecha utilizando la configuración regional Inglés (EE.UU.), se interpreta como una fecha en el formato MM / DD / AAAA y se convierten en la fecha 4 º Marzo del 2014.

Filas de filtrado Es muy probable que usted no tendrá todos los datos que ha importado y que tendrá que filtrar algunas filas. Hay un número de maneras de hacer esto en el poder de consulta, que usted aprenderá acerca de esta sección.

Filas de filtrado Uso de Auto-Filter Probablemente la forma más fácil de filtrar filas de una tabla es utilizar el cuadro de auto-filtro. Usted puede encontrar esta haciendo clic en el icono de flecha abajo junto al nombre de la columna en el Editor de consultas, como se muestra en la figura 3-16 .

72 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-16. La caja automática de filtro

La caja de Auto-Filter muestra los primeros 1.000 valores distintos que encuentra en una columna. Si hay más de 1.000 valores distintos en una columna, una carga botón Más parece que, si se hace clic en él, se cargará 1.000 valores más distintos. Al desactivar un valor en el cuadro de Auto-Filter significará que cualquier fila que contiene ese valor para la columna seleccionada se elimina de la tabla. De manera predeterminada, se seleccionan todos los valores. Desactivando la casilla (Seleccionar todo) en la parte superior de la lista de valores se desactive todos los valores por debajo de ella, por lo que se puede comprobar sólo los valores que desee. También puede buscar valores en el cuadro de búsqueda. Tan pronto como empiece a escribir en el cuadro de búsqueda, los valores del cuadro de auto-filtro se filtran hacia abajo a aquellos que coinciden con el término de búsqueda. Para desactivar la casilla de búsqueda, haga clic en el icono X en el extremo de la derecha de la misma.

Filas de filtrado usando el número, texto y Filtros de fecha Dependiendo del tipo de datos de la columna, justo por encima de las opciones de filtro de números, texto y fecha cuadro de Auto-Filter especializados estará disponible.

número Filtros Figura 3-17 Muestra el número de menú que está disponible sólo de la caja automática de filtración para las columnas de tipo de filtros número.

73 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-17. El número de menú Filtros Las opciones aquí son fáciles de entender: Ellos le permiten filtrar la tabla de manera que sólo se ven filas en las que un valor en la columna seleccionada es igual, no es igual, es mayor que, mayor que o igual a, menor que o menor o igual a un valor dado. También puede filtrar por intervalos de valores. Al seleccionar una de estas opciones se mostrará el cuadro de diálogo Filtrar filas como se muestra en la figura 3-18 . condiciones de filtro múltiples pueden ser aplicados con AND u OR lógica.

Figura 3-18. El cuadro de diálogo Filtrar filas

Una vez que se ha aplicado un filtro, la opción de menú de filtro Clear (muestra en la figura 3-17 ) Será habilitado; seleccionar esta

opción de menú se eliminará el filtro.

Filtros de texto Columnas de tipo texto tener un conjunto diferente de filtros especiales que se pueden aplicar a ellos, como se muestra en la figura 3-19 .

74 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-19. El menú Filtros de texto

Una vez más, las opciones aquí son fáciles de entender: Puede filtrar filas de una tabla en función de si los valores en una columna de texto igual o no igual a un valor de texto proporcionado, o en función de si comienzan con, extremo con, contener, o no contienen ninguna pieza dada de texto. Al hacer clic en una de estas opciones se abrirá un cuadro de diálogo Filtrar filas que es esencialmente la misma que la que se muestra en la figura 3-18 .

Filtros de fecha Columnas de tipo fecha tener un gran número de opciones para el filtrado, como se muestra en la figura 3-20 .

75 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-20. El menú Filtros de fecha Filas de una tabla se pueden filtrar de acuerdo a si la fecha de esa fila es igual a, antes, o después de una fecha determinada. También se pueden filtrar en función de si la fecha está en uno de los muchos intervalos de tiempo predefinidos, tales como la semana actual o el mes en curso. Todos estos rangos de fechas se basan en un calendario estándar que no se pueden modificar para requisitos particulares. Al hacer clic en la opción de menú Filtro a medida se abrirá el cuadro de diálogo Filtrar filas y le permiten crear más filtros de fecha complejas.

Filas de filtrado por Rango Hay una serie de opciones disponibles para filtrar filas por su posición en la tabla. Estas opciones son accesibles desde los botones en la sección Reducir de la ficha Inicio de la barra de herramientas del Editor de consultas, como se muestra en la figura 3-21 .

76 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-21. Reducir la sección de la barra de herramientas del Editor de consultas

Las mismas opciones también se puede acceder haciendo clic en el icono de la tabla en la esquina superior izquierda de la tabla en el Editor de consultas, como se muestra en la figura 3-22 .

Figura 3-22. El menú del icono de la tabla

Las filas Retire el cuadro desplegable de la barra de herramientas le permite eliminar filas de una tabla de diferentes maneras. Eliminar filas superiores y quitar filas inferiores eliminar un número dado de filas de la parte superior o la parte inferior de la mesa; Mantenga la parte superior filas elimina todos menos un número determinado de filas de la parte superior de la tabla. Al hacer clic en cualquiera de estos botones se abre un cuadro de diálogo donde se especifica el número de filas que desea conservar o eliminar. El botón Range Keep te permite filtrar todos menos un número determinado de filas, a partir de un número de fila dada; clic en este botón, se abrirá un cuadro de diálogo que le permite introducir el número de la primera fila y el número de filas para mantener a partir de ese punto.

Retire filas alternativas elimina un número alternativo de las filas de la tabla. Al hacer clic en este botón se abre el diálogo de eliminar filas alternas, como se muestra en la figura 3-23 .

77 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-23. Las filas alternativas de diálogo Remove

En el diálogo de eliminar filas alternas, se especifica un patrón para la eliminación de filas de la tabla: •

El número de la primera fila que desea eliminar. Entrando 1 aquí significaría que la primera fila de la tabla sería el primero en ser eliminado.

•

El número de filas que se elimina de la tabla.

•

El número de filas de la tabla que se mantendrán después de filas se han eliminado. Después de las filas que se han mantenido, serán eliminados otra sección de filas, a continuación, otra sección mantuvo, y así sucesivamente, hasta que se ha alcanzado el final de la tabla.

Extracción de valores duplicados También es posible filtrar filas de una tabla que tiene valores duplicados en una cierta columna y también para eliminar las filas duplicadas. En figura 3-24 , Se puede ver que las filas 7 y 8 en la tabla ambos tienen el valor 129 en la columna Número de factura.

Figura 3-24. filas de la tabla con los números de factura duplicados

78 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La selección de la columna Número de factura y haciendo clic en el botón Eliminar duplicados en la sección Reducir de la ficha Inicio en la barra de herramientas del Editor de consultas se traducirá en la fila 8, la segunda fila con el número de factura 129, se elimina de la tabla. Si selecciona varias columnas, Power consulta sólo eliminará una fila si todos los valores de las columnas seleccionadas se duplica. Para eliminar filas en las que se duplica cada valor en cada columna, que o bien tienen que seleccionar manualmente cada columna y haga clic en el botón Duplicados en Quitar en la barra de herramientas o haga clic en el icono de la tabla en la esquina superior izquierda de la tabla y seleccione Eliminar duplicados de la menú. Potencia de consulta es sensible a mayúsculas en la comprobación de los duplicados de modo con los datos de ejemplo que se muestra en la figura 3-24 , No hay filas se eliminarían si se ha seleccionado cada columna y hecho clic en Quitar duplicados. Esto se debe a la fila 7 contiene el valor “Chris” en la columna 1 de la persona de ventas, pero Fila 8 contiene el valor “Chris”.

Filtrando las filas con errores Los valores de error pueden aparecer en las columnas por un número de razones: un valor calculado podría contener un error de sintaxis, por ejemplo, o un valor de error podría ser el resultado de una conversión de tipo fallado. En los datos de ejemplo que se muestran en la figura 3-24 , Se puede ver que en la fila 5 columna Número de factura contiene un valor no numérico; si intenta convertir la columna Número de factura para escribir Número, verá un valor de error aparece en esta fila, como se muestra en la figura 3-25 .

Figura 3-25. Un valor de error en una celda

Filas que contienen valores de error pueden ser filtrados de una tabla de dos maneras. En primer lugar, puede seleccionar una o más columnas y haga clic en el botón de eliminar los errores en la ficha Inicio en la barra de herramientas del Editor de consultas. Esto eliminará todas las filas que contienen errores en las columnas seleccionadas. Como alternativa, puede hacer clic en el icono de la tabla y luego seleccionar la opción de menú Eliminar los errores de. Esto eliminará todas las filas de la tabla que contiene los errores en cualquier columna.

Ordenando una tabla Las tablas de consulta de energía se pueden clasificar en orden ascendente o descendente por una o más columnas. Para ordenar una tabla por una sola columna, seleccione la columna que en el Editor de consultas y luego o bien haga clic en uno de los dos botones de ordenación en la barra de herramientas (muestra en la figura 3-26 ) O haga clic en la flecha hacia abajo junto al nombre de la columna y seleccione Orden ascendente o descendente Ordenar en el menú. Cuando se ordena una columna, una pequeña flecha que apunta hacia arriba o hacia abajo aparecerá en el encabezado de la columna, lo que indica si la tabla se ordena en orden ascendente o descendente.

79 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Figura 3-26. Ordenar los botones en la barra de herramientas del Editor de consultas

Si selecciona más de una columna en la tabla, se desactivarán los botones de ordenación. Para ordenar por más de una columna, hay que aplicar una clasificación a una columna y luego inmediatamente después, haga clic en otra columna y ordenar eso. Al hacer esto, las dos operaciones de ordenación serán combinados en un solo paso y un número aparecerán en las cabeceras de las dos columnas que indican qué columna se ordenará primero y cuál será clasificado segundo. Figura 3-27 muestra una tabla que ha sido ordenados, en primer lugar, por Sales fecha en orden descendente, y luego por el nombre del producto en orden ascendente.

Figura 3-27. Una tabla ordenada por dos columnas Si hay varios pasos en una consulta en la que se aplica la clasificación a una mesa (que tendrán que ser separados por otras medidas que no hacen otra cosa), el último paso especie anulará cualquier clasificación anterior que se ha aplicado. También puede invertir el orden de las filas de la tabla haciendo clic en el botón Filas inversa en la ficha Transformación de la barra de herramientas del Editor de consultas.

Cambio de valores en una tabla Poder consulta también le permite modificar los valores dentro de una tabla en un número de maneras.

Sustitución de los valores con otros valores El botón Reemplazar valores, que se encuentra en la sección de la columna de la pestaña de transformación en la barra de herramientas del Editor de consultas (ver figura 3-28 ), Le permite buscar los valores en las columnas y reemplazarlos con otros valores. La misma funcionalidad se puede acceder haciendo clic derecho en una columna y seleccionando la opción de menú reemplazar los valores.

Figura 3-28. El botón Reemplazar valores

80 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Después de haber hecho clic en este botón, aparecerá el cuadro de diálogo Reemplazar los valores, como se muestra en la figura 3-29 .

Figura 3-29. El cuadro de diálogo Reemplazar valores

Las opciones aquí son sencillos: Debe introducir el valor para encontrar el valor para reemplazar con, y si el contenido de toda la celda debe estar emparejado. Tenga en cuenta que la coincidencia de mayúsculas y minúsculas así, por ejemplo, el texto “naranjas” no serán emparejados con el texto “naranjas”.

Para eliminar las ocurrencias de un valor por completo, es necesario dejar el cuadro Reemplazar vacía. En nuestros datos de ejemplo, una fila en la columna Número de factura contiene el valor “Invoice127”, y, como ya se ha visto, esto se traduce en un error cuando la columna Número de factura se convierte al tipo de datos Número. Para evitar el error, antes de emitir la columna puede seleccionar la columna Número de factura, haga clic en Reemplazar con, escriba “Factura” en el Valor de cuadro de texto Buscar, deje el cuadro de texto Reemplazar en blanco, deje la casilla de células toda Partido sin control y haga clic en Aceptar. Esto significaría el valor “Invoice127” llegaría a ser justamente “127” y el elenco de Número tendría éxito. Para reemplazar los valores de texto en blanco con un valor nulo, es necesario dejar el valor a Encontrar cuadro de texto vacío e introduzca el valor “nulo” en el cuadro de texto Reemplazar. Para reemplazar cualquier otro valor de texto con un nulo, tiene que introducir un valor en el valor de Reemplazar cuadro de texto y luego asegurarse de que la casilla de verificación célula entera de ajuste está marcada; Si no lo está, obtendrá un error en cualquier célula emparejados.

Transformadas de texto La selección de una o más columnas de texto en el Editor de consultas permitirá el cuadro desplegable Formato de la ficha Transformaciones de la barra de herramientas del Editor de consultas, como se muestra en la figura 3-30 . La misma funcionalidad también se puede acceder desde el menú del botón derecho.

La figura 3-30. El cuadro desplegable Formato

81 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Los siguientes son los cinco elementos de este menú:

•

minúscula, que establece todo el texto en las columnas seleccionadas a minúsculas.

•

MAYÚSCULAS, que establece todo el texto en las columnas seleccionadas a mayúsculas.

•

Capitalizar cada palabra, lo que hace que todas las palabras en la columna seleccionada comienzan con una letra mayúscula y establece todas las letras siguientes en una palabra a minúsculas.

•

Recortar, que elimina cualquier carácter de espacio en blanco iniciales o finales de texto.

•

Limpiar, que elimina todos los caracteres no imprimibles del texto en las columnas seleccionadas.

En los datos de origen, los nombres en la persona de ventas 1 y persona de ventas 2 columnas están en una mezcla de mayúsculas y minúsculas; la selección de estas columnas y luego usando la mayúscula Cada Palabra transformar formateará todos estos nombres consistentemente, como se muestra en la figura 3-31 . Esto es importante no sólo por razones estéticas: Recuerde que la energía de consultas entre mayúsculas y minúsculas, por lo que por lo general tienen que utilizar estos textos se transforma para otras funciones tales como Reemplazar para trabajar con eficacia.

Figura 3-31. El resultado de usar en mayúscula la transformada de cada palabra

Las dos opciones en el cuadro desplegable Analizar, también en la ficha Transformación de la barra de herramientas del Editor de consultas, le permiten transformar un trozo de texto en una celda ya sea en una XML documento o una JSON documento. Figura 3-32 muestra algunos datos de la muestra en el Editor de consultas, donde hay una tabla que contiene dos filas y una columna de texto y cada célula contiene XML.

Figura 3-32. datos XML almacenados en celdas de la tabla

Usando el texto XML transformar en esta columna se traduciría en contenido de cada celda está convirtiendo en una tabla XML (como se muestra en la figura 3-33 ), Que luego pueden ser agregadas o ampliado en la forma en que ha visto con otros valores de la tabla.

82 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-33. células tabla que contiene valores de la tabla XML

Transforma números Selección de una columna del tipo de datos Número permitirá a un número de artículos en la sección Número de la pestaña Transformar en la barra de herramientas, como se muestra en la figura 3-34 . Una vez más, la misma funcionalidad se puede acceder desde el menú del botón derecho.

La figura 3-34. La sección de Número de la pestaña Transform

La funcionalidad de esta sección le permite aplicar diversos tipos de cálculo de los valores en la columna seleccionada.

El cuadro desplegable de redondeo tiene las siguientes tres opciones:

•

Redondo le permite redondear los números de la columna hasta un determinado número de cifras decimales, introducidos por el usuario.

•

Redondeo redondea los números de la columna hasta el número entero más próximo.

•

Redondear a la baja redondea los números en la columna hacia abajo al número entero más próximo.

El cuadro desplegable Científica tiene las siguientes opciones:

•

Valor absoluto devuelve el valor absoluto de los números en la columna; es decir que los números positivos no se cambian, y los números negativos se convierten en positivos. Por ejemplo, el número 3 se mantendría como 3, pero se convertiría en 4 -4.

•

Factorial Devuelve el factorial de los números en la columna. Por ejemplo, el valor 4 se convertiría en 4 * 3 * 2 * 1 = 24.

83 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

•

•

Logaritmo •

Natural Devuelve el logaritmo natural de los números de la columna.

•

Base-10 devuelve el logaritmo en base 10 de los números en la columna.

Poder

•

Cubo devuelve el cubo de los números en la columna. Por ejemplo, el valor 2 se convertiría en 2 * 2 * 2 = 8.

•

Cuadrado devuelve el cuadrado de los números de la columna. Por ejemplo, el valor 3 se convertiría en 3 * 3 = 9.

•

Poder eleva los números de la columna a una potencia dada, introducidos por el usuario.

•

Raíz cuadrada devuelve la raíz cuadrada de los números de la columna.

•

Exponente devuelve el exponente de los números de la columna.

El cuadro de información desplegable tiene las siguientes opciones:

•

Incluso devuelve TRUE si los valores de la columna son iguales, y de lo contrario.

•

Es impar devuelve TRUE si los valores de la columna son impares, y de lo contrario.

•

Firmar devuelve 1 si los valores de la columna son positivos, -1 si son negativas, y 0 en caso contrario.

El cuadro desplegable trigonometría permite encontrar el seno, coseno, tangente, arcoseno, arcocoseno y arcotangente de los valores en las columnas seleccionadas. Tenga en cuenta que estas transformadas esperan que los valores de las columnas seleccionadas para estar en radianes y no en grados.

Transforma Fecha / Hora / Duración Selección de una columna del tipo de datos Fecha y hora, o Duración ( o cualquier tipo híbrido) permitirá ya sea la fecha, hora, o cuadros desplegables Duración en la sección Fecha y hora de la pestaña Transformar en la barra de herramientas, como se muestra en la figura 3-35 . Una vez más, la misma funcionalidad se puede acceder desde el menú del botón derecho.

La figura 3-35. La fecha / hora / zona horaria del menú Transforma

84 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Las opciones en el cuadro desplegable Fecha:

•

Sólo fecha devuelve sólo la parte de fecha de un valor DateTime

•

Año

•

•

•

•

•

Año devuelve sólo la parte de año de la fecha en la columna.

•

Inicio del Año devuelve la primera fecha del año para cada fecha en la columna.

•

Fin de año devuelve la última fecha del año para cada fecha en la columna.

Mes •

Mes devuelve el número del mes de la fecha.

•

Inicio del Mes devuelve la primera fecha del mes.

•

Fin de mes devuelve la última fecha del mes.

•

Días en Mes devuelve el número de días del mes.

Día •

Día devuelve el número del día de la fecha.

•

Día de la semana devuelve el número que representa el día de la semana.

•

Día de Año devuelve el número de días del año.

•

Comienzo del día devuelve un valor DateTime para el inicio de la jornada.

•

Fin del día devuelve un valor DateTime para el final del día.

Trimestre

•

Trimestre del año devuelve el número cuarto de la fecha.

•

Inicio del barrio devuelve la primera fecha en el trimestre.

•

Final del trimestre devuelve el último día del trimestre.

Semana

•

Semana del año devuelve el número de semana del año.

•

Semana del Mes devuelve el número de la semana del mes.

•

Inicio de la Semana devuelve la primera fecha en la semana.

•

Fin de semana devuelve el último día de la semana.

Las opciones de la lista desplegable Tiempo:

•

Tiempo solo devuelve sólo la parte de tiempo de cada valor DateTime en la columna.

•

Hora devuelve el número de horas del tiempo.

•

Minuto devuelve el número de minutos de la hora.

•

Segundo devuelve el segundo número del tiempo.

Las opciones en el cuadro desplegable Duración:

•

Dias devuelve un número que representa el componente de día de un valor de duración.

•

horas devuelve un número que representa el componente de hora de un valor de duración.

85 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

•

Minutos devuelve un número que representa el componente de minutos de un valor de duración.

•

Segundos devuelve un número que representa el componente de segundos de un valor de duración.

•

Días totales devuelve el número total de días en la duración.

•

Horas totales devuelve el número total de horas en la duración.

•

Total de Minutos devuelve el número total de minutos en la duración.

•

segundos totales devuelve el número total de segundos en la duración.

Por estas transformadas Duración, vale la pena señalar la diferencia entre el primer y segundo grupos de opciones. Por ejemplo, dado un valor de duración de 1 día y 2 horas, la opción Horas volvería 2 mientras que la opción Total de horas volvería 26.

Llenar Arriba y Abajo para reemplazar los valores perdidos El cuadro Relleno desplegable en la ficha Transformación de la barra de herramientas del Editor de consultas (muestra en la figura 3-36 ) Contiene dos opciones: Rellenar hacia abajo permite reemplazar los valores nulos en una columna con el último valor no nulo en una columna, y se llenan hace lo mismo, pero utiliza el siguiente valor no nulo en la columna.

La figura 3-36. El botón Rellenar hacia abajo en la barra de herramientas del Editor de consultas

Esta funcionalidad es muy útil para la limpieza de datos que contiene los valores que faltan. Por ejemplo, la figura 3-37 muestra el contenido de la nave a otro y de la nave para columnas City en nuestros datos de la muestra antes de que se fusionaron. Como se puede ver, en dos casos, el nombre del país no se encuentra.

La figura 3-37. Datos de ejemplo que muestra los valores de los países que faltan

86 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Si se hace el supuesto de que cada nombre de la ciudad siempre debe estar asociado con el mismo nombre del país (por lo que Oslo debe estar asociado con el país Noruega, y Londres, deberían participar en el país Reino Unido), y que no siempre va a ser uno fila en la que se le da al país para cada ciudad, entonces usted puede utilizar el botón rellenar hacia abajo junto con algunas de las otras funciones que ya hemos visto para llenar los valores que faltan. El primer paso es usar el botón Reemplazar valores para reemplazar el texto vacía en la nave para la columna de campo con valores nulos; cómo hacer esto se ha descrito anteriormente en la sección “Sustitución de los valores con otros valores.” Una vez que haya hecho esto, los datos serán como se muestra en la figura 3-38 . Esto es importante porque el botón Rellenar hacia abajo sólo reemplazar los valores nulos y no de texto en blanco.

La figura 3-38. Los datos de muestra con valores de texto vacíos reemplazados por valores nulos

A continuación, es necesario ordenar su mesa en primer lugar por la nave para la columna City (ya sea en orden ascendente o descendente) y luego por la nave para la columna País en orden descendente, como se muestra en la figura 3-39 .

La figura 3-39. datos de la muestra ordenados por barco a la ciudad y el envío a País

Por último, está listo para hacer clic en el botón Rellenar hacia abajo: Los valores nulos son ahora directamente por debajo de los valores que desea reemplazarlos con. Al hacer clic en Rellenar hacia abajo dará lugar a los valores nulos de ser reemplazado con el nombre del país correcta, como se muestra en la figura 3-40 . Recuerde que la tabla puede reordenarse en pasos posteriores sin afectar este cambio.

87 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-40. Los datos de muestra con valores de plumón

La agregación de valores Los valores numéricos de una tabla pueden ser agregados (es decir, resumió, contados, en promedio, o de otro modo resumen) haciendo clic en el botón Agrupar por en la ficha Inicio de la barra de herramientas del Editor de consultas, como se muestra en la figura 3-41 . Esta funcionalidad se llama así por su equivalente en el lenguaje de consulta de base de datos SQL, la cláusula GROUP BY, y funciona casi de la misma manera.

Figura 3-41. El botón Agrupar por en la barra de herramientas del Editor de consultas

Haciendo clic en este botón se abre el Grupo Por diálogo, como se muestra en la figura 3-42 .

La figura 3-42. Por el Grupo de diálogo

88 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Hay dos secciones principales en el Grupo By diálogo. En primer lugar, la parte superior, Grupo de sección le permite especificar las columnas que desea agrupar por, al agregar. Por defecto, las columnas que fueron seleccionados en el Editor de consultas se incluirán en esta sección; se puede añadir o eliminar columnas con las teclas + y -. La salida del grupo de operación incluirá todas las distintas combinaciones de valores de las columnas seleccionadas. En segundo lugar, la sección inferior le permite especificar las operaciones de agregación que desea realizar en cualquiera de las columnas restantes. Una vez más, varias operaciones de agregación se pueden especificar aquí haciendo clic en los botones + y -. Cada agregación operación dará lugar a una nueva columna que se agrega a la salida y el nombre de la columna que se especifica en el cuadro de texto Nuevo nombre de la columna.

Las siguientes operaciones de agregación están disponibles en el cuadro desplegable de la operación:

•

Suma, que devuelve la suma de los valores en la columna seleccionada en el cuadro desplegable Columna.

•

Promedio, que devuelve el promedio de los valores en la columna seleccionada en el cuadro desplegable Columna.

•

min, que devuelve el mínimo de los valores en la columna seleccionada en el cuadro desplegable Columna.

•

Max, que devuelve el máximo de los valores en la columna seleccionada en el cuadro desplegable Columna.

•

Contar filas, que devuelve un recuento del número de filas (la caja desplegable de la columna está desactivada en este caso).

•

Contar filas distintas, que devuelve el número de filas que contienen distintas combinaciones de valores en cada columna de la tabla (de nuevo el cuadro desplegable Columna está deshabilitado en este caso).

•

Todas las filas, que devuelve un valor de tipo de mesa en una sola célula. Esta opción tiene poco uso práctico a menos que usted está planeando utilizar estos valores de la tabla en su propio código personalizado M.

Con todos los cambios realizados hasta ahora para los datos de la muestra en este capítulo, el Editor de consultas mostrará lo que se muestra en la figura 3-43 .

La figura 3-43. Los datos de muestra para la agregación

Usando estos datos de muestra, la configuración se muestra en la figura 3-42 devolverá una tabla con dos columnas, como se muestra en la figura 3-44 : Una columna llamada Nombre de producto que contiene todos los valores distintos de la columna Nombre del producto y una columna llamada Conde de ventas, que contiene el número de filas de la tabla original que se asocian con cada valor en el nombre del producto.

89 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-44. Conde de ventas agrupadas por nombre del producto

Un ejemplo algo más complejo implicaría seleccionar tanto el nombre del producto y la persona de ventas columnas 1 y la suma del valor de las ventas tanto por Sum y media, como se muestra en la figura 3-45 .

La figura 3-45. Agrupar por Nombre del producto y la persona de ventas 1

La salida de esto se muestra en la figura 3-46 : Una mesa con cuatro columnas, que contiene todas las distintas combinaciones de valores de Productos y Ventas Persona 1 que existía en la tabla original, y valor de las ventas sumaron y promediaron.

La figura 3-46. Valor de ventas agrupadas por nombre del producto y la persona de ventas 1

90 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Finalmente, la opción Filas agregación Todo se puede utilizar con la columna Nombre del producto, como se muestra en la figura 3-47 .

La figura 3-47. La opción de todas las filas de agregación

La salida de esto se muestra en la figura 3-48 . Al hacer clic en el icono Expandir situado junto a la cabecera de la columna tabla de ventas se

le permiten agregar columnas adicionales o valores agregados a la mesa exactamente de la misma manera como se vio en el capítulo 2 cuando se navega a través de una base de datos SQL Server. Esta opción es útil para situaciones en las que necesita para escribir código M para la agregación de datos de manera que no están disponibles a través del Grupo A de diálogo.

La figura 3-48. La salida de la opción Filas agregación Todo

Unpivoting columnas a filas Un problema muy común que se encontrará en los datos de origen se hace pivotar datos. Por ejemplo, los datos de origen pueden contener una columna para las ventas de cada año. Si bien esto podría ser la forma en que los datos se visualiza por lo general en un informe, para muchos propósitos (tales como la agregación de datos) es mucho más conveniente tener una sola columna para sus valores de venta y una fila para cada año. Figura 3-49 muestra un ejemplo sencillo de datos de venta de esta manera.

91 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-49. Los datos con formato de años como columnas

Para UNPIVOT estos datos, seleccionar las cinco columnas años, de 2010 a 2014 y haga clic en el botón UNPIVOT en la ficha Transformación de la barra de herramientas del Editor de consultas (muestra en la figura 3-50 ).

La figura 3-50. El botón UNPIVOT La versión no girados de estos datos se muestra en la figura 3-51 . ¿Qué estaban los nombres de las columnas se almacenan ahora en una nueva

columna llamada Attribute; los valores se almacenan ahora en una nueva columna llamada Valor.

La figura 3-51. los datos no girados con una fila para cada producto y el año

92 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Unpivoting datos también pueden ser útiles para el manejo de muchos-a-muchos relaciones en los datos. En los datos de la muestra en la figura 3-43 , Se puede ver que cada venta tiene la gente ya sea uno o dos de venta asociadas con ella en la persona de ventas y 1 persona de ventas 2 columnas. Si usted quiere saber el valor total de las ventas de cada vendedor, teniendo en cuenta el hecho de que una venta puede estar vinculado a la gente dos ventas, se puede seleccionar la persona de ventas 1 y Sales Person 2 columnas y haga clic en el botón UNPIVOT. La salida de este se muestra en la figura 3-52 . Nótese cómo no hay filas en las Ventas Persona 2 contenía un valor nulo.

La figura 3-52. datos de la muestra con la persona de ventas 1 y 2 no girados persona de ventas

Ahora puede cambiar el nombre de la columna Valor a algo más significativo, como persona de ventas, y el uso del Grupo Por funcionalidad para encontrar la suma de la columna de valor de las ventas de cada vendedor, como se muestra en la figura 3-53 .

La figura 3-53. Las ventas acumuladas por persona de ventas

No hay manera de hacer lo contrario de un UNPIVOT, en otras palabras, para pivotar filas en columnas en la barra de herramientas del Editor de consultas. Sin embargo, es posible que si usted escribe su propio código M. (Un ejemplo se da en el Capítulo 5)

La transposición de una tabla La transposición de una mesa consiste en convertir las filas de una tabla en columnas y las columnas en filas, y se puede lograr haciendo clic en el botón de transposición en la pestaña Transformar de la barra de herramientas del Editor de consultas (como se muestra en la figura 3-54 ).

93 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-54. El botón de transposición de la barra de herramientas del Editor de consultas

La transposición de la tabla mostrada en la figura 3-49 da la tabla mostrada en la figura 3-55 .

La figura 3-55. El resultado de la transposición de la tabla mostrada en la figura 3-49

Como se puede ver, los nombres de las columnas se han perdido por completo, y los valores de la primera columna de la tabla original ahora se celebran en la primera fila. (Usted sería capaz de utilizarlos como nombres de columna haciendo clic en el primer uso filas como botón de encabezados, por supuesto.)

Crear columnas personalizadas Nuevas columnas que contienen valores calculados se pueden agregar a su mesa en el Editor de consultas. Estas columnas, denominadas columnas personalizadas, se pueden crear con un número de cálculos incorporados, o puede escribir su propio cálculo utilizando un lenguaje M Power consulta.

Incorporado en las columnas personalizadas Todos los tipos de columna personalizados incorporados se pueden encontrar en la ficha Insertar de la barra de herramientas del Editor de consultas, como se muestra en la figura 3-56 . La misma funcionalidad también se puede encontrar en el menú del botón derecho y haciendo clic en el icono de la tabla.

94 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

La figura 3-56. La ficha Insertar de la barra de herramientas del Editor de consultas

Las columnas de índice El botón Insertar columna Índice agrega una nueva columna a la tabla que contiene los valores de cero a uno menos que el número de filas en la tabla, con la primera fila de la tabla que contiene el valor cero. Un ejemplo de una columna de índice se muestra en la figura 3-57 .

La figura 3-57. Un ejemplo de una columna de índice

columnas de índice no suelen ser muy útiles por su cuenta, pero son extremadamente útiles como un paso intermedio en la creación de otros cálculos más complejos, como se verá en el capítulo 5.

Duplicación Columnas Al igual que con columnas de índice, la capacidad de duplicar una columna en una tabla no es muy útil por sí misma, pero es importante al crear otros cálculos, por ejemplo, cuando se está utilizando uno de los incorporados en las transformaciones descritas anteriormente en este capítulo y desea conservar el contenido original de una columna. Para duplicar una columna, sólo tiene que seleccionar la columna que desea duplicar en el Editor de consultas y haga clic en el botón de columna duplicados en la barra de herramientas. Al hacer esto, una nueva columna llamada “Copia de x”, donde “x” es el nombre de la columna original, se añadirá a la parte derecha de la tabla, como se muestra en la figura 3-58 . Esta columna contiene los mismos valores que la columna original y no se verá afectado por los cambios posteriores a la columna original.

La figura 3-58. Un ejemplo de una columna duplicada

95 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Cálculo de valores en las columnas Hay muchos tipos de cálculos, tales como sumas, se pueden realizar a través de múltiples columnas numéricas mediante la selección de las columnas, haga clic en el botón Insertar columna personalizada y, a continuación, hacer clic en una de las opciones en las estadísticas, estándar o cajas Scientific desplegables. Al hacer esto, una nueva columna se creará en el lado derecho de la mesa, y se visualizará cada fila de la nueva columna que contiene el resultado del cálculo a través de todos los valores en las columnas seleccionadas. Figura 3-59 muestra una columna que contiene la suma del valor de las ventas y las columnas de coste de envío, creado mediante la selección de esas dos columnas y seleccionando la opción de la suma en el cuadro desplegable Estadísticas.

La figura 3-59. Una columna calculada que muestra la suma de valor de venta y el costo de envío Es importante entender que estas opciones le permiten calcular los valores verticalmente, a través de los valores en diferentes columnas en la misma fila de una tabla. Si desea realizar un cálculo sobre la totalidad de los valores de todas las filas en una sola columna, es probable que desee utilizar el Grupo Por funcionalidad descrita anteriormente en este capítulo.

Las siguientes opciones están disponibles en el cuadro de Estadísticas desplegable:

•

Suma agrega todos los valores de las columnas seleccionadas.

•

min se encuentra el mínimo de los valores en las columnas seleccionadas.

•

Max se encuentra el máximo de los valores en las columnas seleccionadas.

•

Promedio se encuentra la media de los valores de las columnas seleccionadas.

•

Desviación estándar se encuentra la desviación estándar de los valores en las columnas seleccionadas.

•

Los valores de recuento cuenta el número de valores en las columnas seleccionadas. Los valores nulos se ignoran.

•

Contar Valores únicos cuenta el número de valores distintos en las columnas seleccionadas. Los valores nulos se ignoran.

Las siguientes opciones están disponibles en el cuadro desplegable estándar: •

Añadir agrega todos los valores de las columnas seleccionadas. Esto hace exactamente lo mismo que la opción de la suma anterior.

•

Multiplicar multiplica todos los valores de las columnas seleccionadas.

•

Sustraer resta los valores de la columna seleccionada primero de los valores de la columna seleccionada segundo cuando se seleccionan dos columnas.

96 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

•

Dividir divide los valores en la columna seleccionada por primera vez por los valores en la columna seleccionada segundo cuando se seleccionan dos columnas.

•

Divide (entero) hace lo mismo que Divide pero sólo devuelve la parte entera del resultado.

•

Modulo hace lo mismo que divide sino que sólo devuelve el resto (o módulo).

La opción Power también está habilitada en el Científica desplegable cuando se seleccionan sólo dos columnas. Esto crea una nueva columna que plantea la columna de valores seleccionada en primer lugar a la potencia del valor en la misma fila de la segunda columna que ha seleccionado.

Columnas personalizadas con cálculos M El poder real de las columnas personalizadas se hace evidente una vez que defina sus propios cálculos utilizando el lenguaje M. Una introducción completa a la lengua M se dará más adelante en este libro en el capítulo 5, pero es fácil de escribir expresiones M simples sin necesidad de saber mucho sobre el lenguaje mismo. Para crear una columna personalizada que utiliza una expresión M, haga clic en el botón Insertar columna personalizada en la barra de herramientas. Al hacer esto, aparecerá el cuadro de diálogo Insertar columna personalizada, como se muestra en la figura 3-60 .

La figura 3-60. El cuadro de diálogo Insertar columna personalizada

Los cálculos basados ​en los valores de otras columnas en la misma fila son fáciles de crear en este diálogo. Para hacer referencia a un valor en otra columna, seleccione el nombre de la columna en el cuadro Columnas disponibles y luego haga doble clic en él o haga clic en el botón Insertar. Al hacer esto, el nombre de la columna, rodeado de corchetes para mostrar que es una referencia de columna, aparecerá en el cuadro de texto Columna Fórmula personalizada y, a continuación, puede aumentar la expresión en ese cuadro de texto a sí mismo. Cuando haya terminado de escribir su expresión, haga clic en el botón OK y una nueva columna se añadirá a la parte derecha de la tabla que contiene los valores calculados. Puede introducir el nombre de la nueva columna en el cuadro Nuevo nombre de la columna.

97 www.it-ebooks.info

Capítulo 3 ■ transformar los datos de consultas con el poder

Utilizando los datos de la muestra para este capítulo, la expresión M [Valor de ventas] * 1.2 devolverá el valor de la columna de valor de las ventas en la fila actual multiplicado por 1.2, y la expresión M [Valor de ventas] + [Costo de envío] devolverá el el valor de la columna de valor de las ventas en la fila actual más el valor de la columna de gastos de envío. Los resultados de estos dos cálculos se muestran en la figura 3-61 .

La figura 3-61. Dos ejemplos de columnas calculadas

Resumen Ahora ha visto la gama completa de lo que es posible en el Editor de consultas de consultas de energía y, para muchos usuarios, esto será todo lo que alguna vez se desee o necesite utilizar. Sin embargo, los últimos capítulos de este libro le mostrará cómo, a través del lenguaje M, se puede lograr transformaciones aún más complejos y cálculos. En el siguiente capítulo, aprenderá cómo controlar donde el poder de consulta da salida a sus datos y la forma de gestionar el proceso de actualización de datos en sí.

98 www.it-ebooks.info

Capítulo 4

destinos de datos

Tienes que controlar la información que necesita. Ha filtrado, ordenados, agregados, y de otra manera transformasteis. Ahora, la última cosa que necesita hacer es decidir en que los datos deben terminar. Poder de consulta le da dos opciones para donde se carga el resultado de una consulta: una tabla en una hoja de cálculo o el modelo de datos de Excel. En este capítulo, aprenderá cómo utilizar estos dos destinos y cómo actualizar una consulta para que sus datos es de hasta al día.

La elección de un destino para sus datos Cuando haya terminado de trabajar en el Editor de consultas, se puede elegir un destino para los datos utilizando las casillas de verificación en la sección Configuración de carga en la parte inferior derecha de la pantalla (como se muestra en la figura 4-1 ).

La Figura 4-1. Las opciones de configuración de carga en el Editor de consultas

La carga de la opción de hoja de trabajo dará lugar a la salida de la consulta que se está cargando a una tabla en la hoja de trabajo; la carga al modelo de datos dará lugar a la salida de la consulta que se está cargando a una tabla en el modelo de datos de Excel. Ambas cajas se puede comprobar con el resultado de que los datos serán cargados a ambos destinos. También puede dejar ambos casillas sin marcar, lo que desactivar la consulta, lo que significa que los datos no se pueden cargar en cualquier lugar por el poder de consulta; Sin embargo, la consulta se puede seguir utilizando como fuente para otras consultas en su libro. Al hacer clic en el botón Aplicar y cerrar en la barra de herramientas del Editor de consultas para cerrar el Editor de consultas, los datos serán cargados a los destinos seleccionados.

■

Nota La opción Cargar modelo de datos que sólo está disponible si está utilizando la energía de consultas con Excel 2013. Si está utilizando Excel 2010, esta opción no está

disponible debido a que el modelo de datos de Excel no existe como una característica en esta versión de Excel.

Puede configurar sus propios valores por defecto para estos parámetros para todas las nuevas consultas haciendo clic en el botón Opciones de la ficha de alimentación consulta de la cinta de Excel, como se muestra en la figura 4-2 .

99

www.it-ebooks.info

Capítulo 4 ■ destinos de datos

La Figura 4-2. El botón Opciones de la pestaña de consulta de la cinta de Excel Al hacer clic en el botón Opciones abre el cuadro de diálogo Opciones, como se muestra en la figura 4-3 . Aquí se puede elegir ya sea para utilizar la configuración de carga estándar de alimentación de consulta o tiene la carga de la hoja de trabajo y / o la carga a las opciones de modelos de datos controladas por defecto.

La Figura 4-3. Configuración de los ajustes por defecto de carga de la consulta

Cargando datos a la hoja Si desea cargar los datos de potencia de consulta directamente a una hoja de cálculo de Excel, tiene dos opciones: utilizar la carga incorporada en la hoja de trabajo funcionalidad o crear sus propias tablas en Excel y conectarlos a la energía de consulta.

100 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Utilizando la tabla de salida por defecto Excel Cuando se utiliza la opción Cargar en una nueva consulta a la hoja de trabajo, se añadirá una nueva hoja al libro y se añadirá a los datos de la consulta para una nueva tabla de Excel cuya parte superior izquierda celular estará en la celda A1 de la nueva la hoja de trabajo la primera vez que se ejecute la consulta. La tabla de Excel tendrá el mismo nombre que la consulta de alimentación de consulta. Por ejemplo, tome una consulta que devuelve los datos mostrados en la figura 4-4 en el Editor de consultas.

La Figura 4-4. El resultado de una consulta en el Editor de consultas

Cuando se carga a una tabla en la hoja de trabajo utilizando la opción de carga a la hoja de trabajo, verá el cuadro que se muestra en la figura 4-5 .

La Figura 4-5. Los datos de la consulta que se muestra en la figura 4-4 en la hoja de trabajo

Uno de los inconvenientes de la carga a la opción de hoja de cálculo es que no hay manera de controlar el punto en que se crea, la mesa estará siempre en la celda A1 de la hoja de trabajo de nueva creación. Sin embargo, una vez que se ha creado la tabla, puede cambiar el formato y añadir columnas adicionales. Estas personalizaciones se mantendrán después de la consulta ha sido actualizada.

Carga de datos a sus propias tablas de Excel Una alternativa al uso de la opción Cargar a la hoja de trabajo es crear sus propias tablas de Excel en la hoja de trabajo y conectarlos a la energía de consulta. Esto es posible debido a que cada vez que se crea una consulta utilizando la energía de consulta, una conexión OLE DB se crea en el interior de Excel que apunta a la salida de esta consulta. Esta conexión es utilizable incluso cuando la propia consulta está desactivada.

Para crear una nueva tabla de consulta en Excel, tiene que ir a la pestaña de datos en la cinta de Excel y haga clic en el botón Conexiones existentes, que se muestra en la figura 4-6 .

101 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Figura 4-6. El botón existente Conexiones de la ficha de datos Al hacer esto, el diálogo de las conexiones existentes se abrirá, como se muestra en la figura 4-7 . Toda la energía de consultas

conexiones se enumeran en las conexiones En esta sección Libro de trabajo en la ficha Conexiones, y tendrán nombres en el formato “Power Consulta-nombreconsulta”.

La Figura 4-7. El cuadro de diálogo Conexiones existentes

102 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Haga doble clic sobre el enlace para su búsqueda se abrirá el cuadro de diálogo Importar datos, como se muestra en la figura 4-8 .

La Figura 4-8. El diálogo Importar datos Al seleccionar la opción Tabla va a crear una nueva tabla de consulta de la salida de la consulta en la posición especificada en el lugar en el que desea colocar los datos? sección. La nueva tabla de consulta se comportará de la misma manera que una tabla de Excel conectado a cualquier otro tipo de fuente de datos, y se pueden crear varias tablas de Excel conectados a la misma consulta de alimentación de consulta. Usted puede dar a la mesa un nuevo nombre, el formato de los datos de las columnas, y añadir nuevas columnas a la tabla. Estos cambios se conservan incluso cuando se actualizan los datos.

Carga de datos al modelo de datos de Excel Como se vio en el capítulo 1, el modelo de datos de Excel es un motor de base de datos en memoria que es una característica nueva en Excel 2013-que es el motor de base de datos detrás de la energía de pivote, de forma nativa integrada en Excel. Si está utilizando la energía de consultas con Excel 2013, hay una serie de ventajas para la carga de datos en el modelo de datos de Excel sobre la carga directamente a la hoja de trabajo que va a aprender acerca de esta sección. Una descripción completa de lo que puede hacer con el modelo y el Poder de datos dinámica de Excel está fuera del alcance de este libro. (Si no está familiarizado con este tema, es una buena idea para aprender más sobre él antes de continuar.)

Visualización de las tablas en el modelo de datos de Excel Una vez que haya cargado el resultado de una consulta con el modelo de datos de Excel utilizando la opción Cargar modelo de datos en el Editor de consultas a, se puede ver que la mesa sea a través del diálogo Conexiones existentes que viste encima o mediante la apertura de la ventana de energía del pivote, si usted tiene el poder de pivote complemento activado.

Tablas en el modelo de datos de Excel y las conexiones existentes de diálogo En la ficha de datos en la cinta de Excel, haga clic en el botón existente Conexiones (muestra en la figura 4-5 ) Se abrirá el cuadro de diálogo Conexiones existentes. Para ver las tablas de carga en el modelo de datos de Excel, usted tiene que ir a la pestaña Tablas, como se muestra en la figura 4-9 .

103 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Figura 4-9. La ficha Tablas del cuadro de diálogo Conexiones existentes El contenido de esta ficha se divide en tres secciones. En la sección superior, haciendo clic en este modelo de datos Libro de trabajo creará una conexión con todo el modelo de datos de Excel. Al hacer clic en esta sección, el cuadro de diálogo de importación de datos se abrirá como en la figura 4-7 , Pero el botón de radio para cargar datos en una tabla aparecerá en gris y los botones de radio para crear un informe de Power View o simplemente crear estará habilitada una conexión. Si crea una tabla dinámica de esta manera se va a conectar a todas las tablas en el modelo de datos de Excel.

En la siguiente sección abajo, llamado WorkbookName (Este libro de trabajo), verá una lista de todas las tablas en las hojas de cálculo en su libro. Esto puede ser muy confuso si está utilizando tablas de Excel como fuentes de datos para consultar usando la funcionalidad De la tabla, pero nada aquí está relacionada con el modelo de datos de Excel o Power consulta. Por último, verá una lista de todas las tablas cargadas en el modelo de datos de Excel agrupados por las conexiones que están asociados. Verá todas las tablas que ha cargado en el modelo de datos de Excel a partir de energía Consulta aquí, junto con las tablas que ha cargado en el modelo de datos de Excel por otros medios (por ejemplo, a través de la ficha de datos en Excel o en la ventana de alimentación de pivote) . Al hacer clic en una de las tablas mencionadas aquí también se abrirá el cuadro de diálogo Importar datos, esta vez con todos los botones de radio habilitado para que pueda crear tablas, tablas dinámicas, gráficos dinámicos, y poder ver los informes de la tabla que ha seleccionado.

Cuando se crea una tabla de Excel vinculado a una tabla en el modelo de datos de Excel usando este método, que se verá igual que las tablas de Excel que ha visto anteriormente en este capítulo, pero con una diferencia bien escondido. Estas tablas muestran todos los datos de la tabla que están obligados a cuando se crean, pero pueden ser modificados de manera que se muestren

104 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

los resultados de una consulta DAX contra las tablas en el modelo de datos de Excel en lugar. Para ello, haga clic dentro de la tabla y seleccione Tabla y editar DAX. A continuación, en el cuadro de diálogo Editar DAX, seleccione DAX en el cuadro desplegable Tipo de comando, y usted será capaz de entrar en una consulta DAX en el cuadro de texto Expresión.

Tablas en el modelo de datos de Excel y la Ventana Power Pivot Si usted tiene el poder de pivote complemento activado en Excel (se instala por defecto, pero no habilitado para ver http: // tinyurl. com / EnablePP para obtener instrucciones de cómo hacer esto), también se puede abrir la ventana de pivote de energía para ver las tablas que se ha cargado en el modelo de datos de Excel. Si usted está haciendo cualquier tipo de trabajo de BI grave en Excel, esta es la opción recomendada para la visualización de tablas en el modelo de datos de Excel.

Puede abrir la ventana de energía de pivote por ir a la pestaña PowerPivot en la cinta de Excel y haciendo clic en el botón Administrar, como se muestra en la figura 4-10 .

Figura 4-10. El botón Administrar en la pestaña PowerPivot Después de haber hecho esto, la ventana de energía del pivote se abrirá en Vista de datos, como se muestra en la figura 4-11 . Puedes ver los datos de cada tabla en el modelo de datos de Excel haciendo clic en su nombre en la barra de pestañas en la parte inferior de la pantalla.

Figura 4-11. La ventana de energía de pivote en la vista Datos

105 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Al hacer clic en el botón Vista de diagrama en la barra de herramientas lo llevará a una vista donde se puede ver cada una de sus tablas en un diagrama, como se muestra en la figura 4-12 . Aquí se puede ver con mayor facilidad y especificar las relaciones entre las tablas.

Figura 4-12. La ventana de energía de pivote en el Diagrama de Al hacer clic en el botón de tabla dinámica en la cinta va a crear una nueva tabla dinámica en una hoja de trabajo conectada a los datos en el modelo de datos de Excel.

Ventajas del uso del modelo de datos de Excel Si usted es un usuario experimentado de Excel, se puede sentir la carga de datos más cómodas de alimentación de consulta en una tabla en una hoja oculta en lugar de utilizar el modelo de datos de Excel. Sin embargo, hay una serie de razones de peso para utilizar el modelo de datos de Excel como el destino de los datos:

•

Una hoja de Excel 2013 puede tener un máximo de 1.048.576 filas. Si está trabajando con grandes cantidades de datos, es posible que usted tiene que importar más que este número de filas de una fuente de datos. El modelo de datos de Excel puede hacer esto. No hay un límite establecido en la cantidad de datos que se pueden cargar en el modelo de datos de Excel; que es dictada por una combinación de un número de factores, incluyendo la cantidad de memoria en su PC, el número de valores distintos en cada columna de la tabla, y la forma en que se clasifican los datos. Es cierto que el modelo de datos de Excel puede manejar tablas con millones, incluso decenas o cientos de millones de personas, de las filas. El uso de la versión de 64 bits de Excel es una buena idea si se trabaja con grandes volúmenes de datos, ya que puede almacenar muchos más datos que la versión de 32 bits de Excel.

•

El modelo de datos de Excel es también capaz de realizar cálculos tales como agregados a través de tablas de gran tamaño mucho más rápidamente que los equivalentes fórmulas de hoja de cálculo de Excel. El lenguaje DAX (que es en realidad una extensión del lenguaje de fórmulas de Excel para el modelo de datos de Excel) es capaz de expresar cálculos complejos que serían muy difíciles de poner en práctica lo contrario.

106 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

•

Carga de datos en el modelo de datos de Excel desbloquea algunas funciones muy útiles cuando se está trabajando con esos datos en una hoja de cálculo de ejemplo-para, la capacidad de crear una tabla dinámica vinculada a varias tablas de datos y la capacidad de utilizar Excel Cubo fórmulas tales como el CubeMember ()

y CubeValue () funciones. Mapa de potencia sólo puede trabajar con los datos almacenados en el modelo de datos de Excel, y aunque Power View puede trabajar con los datos de la hoja de trabajo, así como el modelo de datos de Excel, funciona mucho más suavemente con este último.

•

Dado que el modelo de datos de Excel comprime los datos que almacena, libros de trabajo que tienen los datos almacenados en el modelo de datos de Excel son generalmente mucho más pequeños que los libros de trabajo que tienen los mismos datos almacenados en las tablas de la hoja de trabajo. tamaño de libro de trabajo también es significativo si está cargando un libro a un sitio de alimentación de BI, donde los diferentes límites se aplican sobre la cantidad de datos que se permite en el modelo de datos Excel y en la hoja de trabajo. A pesar de estos límites están sujetos a cambios, al cargar un libro en un sitio de alimentación de BI, se le permite tener hasta 250 MB de datos en el modelo de datos de Excel, mientras que el resto del libro puede ser no más de 10 MB de tamaño, por lo el momento de la escritura.

Aprender sobre la energía pivote y el modelo de datos de Excel puede parecer una perspectiva desalentadora, pero los beneficios de hacerlo son inmensas. Para cualquier moderadamente compleja aplicación de BI o la presentación de informes, se aconseja utilizar el modelo de datos de Excel para almacenar sus datos.

Si ya ha utilizado la energía de pivote y el modelo de datos de Excel, usted está probablemente consciente de que también se puede cargar datos en el modelo de datos de Excel en la ficha de datos en Excel 2013 y también desde el interior de la ventana de energía del pivote. Aunque estas opciones no proporcionan ninguna parte de la funcionalidad rica que la energía de consulta tiene para transformar los datos de limpieza y, lo hacen compatible con algunas fuentes de datos que la energía de consulta (todavía) no admite tales como conexiones / ODBC genéricos OLE DB. Si es necesario utilizar una de estas fuentes de datos, entonces no tienen más remedio que utilizar la ficha de datos de Excel o en la ventana de alimentación de pivote para cargar sus datos. Además, si los datos que va a cargar ya está limpio y en el formato que necesita (por ejemplo, si va a cargar los datos de un almacén de datos), puede que no haya necesidad de utilizar la energía de consulta.

Potencia para realizar consultas y relaciones entre tablas En general, el papel de la energía de consulta en una solución de BI de autoservicio es extraer, transformar y cargar datos en el modelo de datos de Excel; después de eso ha sucedido, se utiliza la energía de pivote para modelar los datos. Una de las tareas de modelado más importantes es definir relaciones entre tablas. Usted puede hacer esto arrastrando y soltando las columnas de una tabla a otra en la vista Diagrama de la ventana de energía del pivote. Sin embargo, en algunas circunstancias, la energía de consulta es capaz de detectar relaciones entre tablas y crearlos de forma automática cuando se carga de datos en el modelo de datos de Excel.

Relaciones de mesa y bases de datos relacionales Cuando existen relaciones de clave externa entre las tablas de una base de datos relacional como SQL Server, la energía de consulta creará automáticamente las relaciones en el modelo de datos de Excel al importar dichas tablas en el modelo de datos de Excel. No hay ninguna indicación de que esto está sucediendo en la interfaz de usuario de energía de consultas, pero verá las relaciones creadas cuando se mira en el Diagrama de potencia en la ventana de pivote.

Tomando la base de datos de muestra Adventure Works DW para SQL Server como un ejemplo, la DimDate, FactInternetSales, DimProduct, DimProductSubcategory, y tablas DimProductCategory tienen relaciones de clave externa, como se muestra en la figura 4-13 .

107 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Figura 4-13. Adventure Works relaciones de clave externa de bases de datos DW Si se conecta a la base de datos Adventure Works DW usando la opción de base de datos SQL Server desde (descrito en el capítulo 2) y seleccionar las siguientes tablas en el panel del navegador, como se muestra en la figura 4-14 , También se pueden importar las relaciones entre estas tablas.

108 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Figura 4-14. Importación de varias tablas de SQL Server en el panel Navegador

Abrir la ventana de energía pivote en Vista de diagrama mostrará que estas relaciones han sido importados en el modelo de datos de Excel, como se ve en la figura 4-15 .

109 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Figura 4-15. las relaciones de tablas importadas en la ventana de energía Pivote

Relaciones de mesa y otros datos Fuentes En ciertas circunstancias, la energía de consulta también creará relaciones de la tabla entre consultas que utilizan datos procedentes de fuentes distintas bases de datos relacionales. Lo hace cuando detecta que existen claves en las mesas de alimentación de consulta y que estas tablas se han unido usando la funcionalidad de combinación; estos temas se discutirán en los capítulos 5 y 6, respectivamente. Sin embargo, la funcionalidad de detección de relación es muy difícil conseguir trabajar de forma fiable, por lo menos en el momento de la escritura, por lo que se recomienda que siempre se definan explícitamente las relaciones dentro del pivote de alimentación después de los datos de origen se ha cargado.

Los cambios de última hora Borrar o renombrar las columnas de la consulta Consulta de energía, o cambiar el nombre de la consulta en sí misma, podría causar problemas si se han creado las columnas calculadas o campos calculados en la ventana de alimentación de pivote. Los cambios de este tipo pueden dar lugar a errores en el poder pivotar o, en el peor, conduce a cálculos y otros objetos construidos en la ventana de alimentación de pivote está perdiendo. Por lo tanto, se recomienda encarecidamente a terminar el trabajo en sus consultas de consulta de alimentación antes de comenzar a trabajar en el Poder pivote, y para asegurarse de copia de seguridad del libro antes de hacer cualquier cambio en el poder de consulta más adelante.

Las consultas refrescantes Después de haber creado una consulta, es muy probable que tendrá que volver a cargar los datos de la fuente de datos en algún momento, si ha cambiado. Esto se puede hacer manualmente en varias formas, o puede configurar cada consulta para actualizar automáticamente. Ya que ni las tablas de Excel ni el modelo de datos de Excel admite la adición de nuevos datos a lo que ya ha sido cargada, la energía de consultas no le permite hacer cualquier tipo de actualización incrementales. Cuando se actualiza una consulta en la energía de consulta, siempre hay que volver a cargar todos los datos en su destino.

110 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Refrescante Consultas Manualmente Las consultas se pueden actualizar manualmente desde el panel de libro consultas en una de dos maneras. En primer lugar, cuando el puntero del ratón pasa por encima de una consulta de un botón Actualizar aparece a la derecha del nombre (como se muestra en la figura 4-16 ), Y haciendo clic en este botón se actualizará la consulta. Mientras que una consulta es refrescante, puede cancelar la actualización haciendo clic en el mismo botón. También puede actualizar una consulta por él y seleccionando la opción Actualizar en el menú contextual haciendo clic derecho.

Figura 4-16. El botón Actualizar en el panel de libro consultas El panel de libro de consultas muestra la fecha y hora en que la consulta se actualizó por última, así como el número de filas que se devuelve. Las consultas también se pueden actualizar desde la ficha de datos de Excel, o bien haciendo clic en el botón Actualizar todo, o haciendo clic en el botón de Conexiones para abrir el cuadro de diálogo Conexiones del libro, la selección de la conexión asociada a la consulta, y luego haciendo clic en el botón Actualizar, como se muestra en Figura 4-17 .

Figura 4-17. El diálogo Conexiones de libro de Excel 111 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Por último, las consultas se pueden actualizar haciendo clic en el botón Actualizar de la pestaña Inicio del Editor de consultas, como se muestra en la figura 4-18 . Puede cancelar la actualización de datos en el Editor de consultas, ya sea haciendo clic en la opción Cancelar en el cuadro desplegable debajo del botón Actualizar, o haciendo clic en el mensaje en la esquina inferior derecha de la ventana del Editor de consultas bajo el área Configuración de carga (si son rápidos).

Figura 4-18. El botón Actualizar en la barra de herramientas del Editor de consultas

Automatizar la actualización de datos Debido a que Excel ve consultas de energía de consulta como conexiones OLE DB, todos los métodos que se suele utilizar para automatizar la actualización de datos va a funcionar con alimentación de consulta. Al hacer clic en el botón Propiedades en el cuadro de diálogo Conexiones del libro (que se muestra en la figura 4-17 ) Se abrirá el cuadro de diálogo Propiedades de conexión, como se muestra en la figura 4-19 .

112 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Figura 4-19. El diálogo Propiedades de conexión En el cuadro de diálogo Propiedades de conexión, puede configurar una consulta Consulta de energía para refrescarse después de un número determinado de minutos y también para que se actualice automáticamente cuando se abre el libro de Excel. Esta última opción es muy útil para garantizar que el libro siempre muestra datos actualizados al día.

Aunque no existe una integración directa entre el poder de consulta y VBA, las conexiones de alimentación de consulta se puede actualizar utilizando VBA en la misma forma que cualquier otra conexión de Excel. Usando ActiveWorkbook.Connections ( “El poder de consulta - QueryName”). Refrescarse en una macro de Excel refrescará una consulta individual y ActiveWorkbook. RefreshAll actualizará todas las conexiones de un libro, si han sido hechos por el poder de consulta o no.

113 www.it-ebooks.info

Capítulo 4 ■ destinos de datos

Resumen En este capítulo, usted ha visto cómo cargar sus datos en una tabla en una hoja de cálculo de Excel o en el modelo de datos de Excel y cómo actualizar sus datos. Con esto concluye la primera mitad del libro: En este punto, usted debe ser capaz de construir una solución completa de consultas de energía, y usted debe tener un conocimiento profundo de lo que es posible utilizando sólo la interfaz de usuario. En el siguiente capítulo, usted aprenderá acerca de la lengua M y descubrir lo que es posible cuando se escribe código M para implementar las transformaciones más avanzados y cálculos.

114 www.it-ebooks.info

Capítulo 5

Introducción a M Si desea liberar todo el potencial de la energía de consulta, usted tiene que aprender M. M es el nombre no oficial de lenguaje de fórmulas de alimentación de consultas, y el Poder de consultas genera código M para cada paso en su consulta. La interfaz de usuario de energía de consulta también le permite escribir sus propias expresiones M en situaciones donde se necesita una mayor flexibilidad y control sobre lo que hace su consulta.

El mayor problema con el idioma M, desde el punto de vista de un desarrollador de Excel, es que tiene poco parecido con las fórmulas de Excel o VBA: es un lenguaje funcional como F #. Usted tiene que aprender una nueva sintaxis, nuevas funciones y nuevas formas de hacer las cosas familiares, y no habrá una curva de aprendizaje. Sin embargo, si ya tiene alguna experiencia en programación encontrará M muy fácil para ponerse en marcha con, y una vez que haya dominado usted podrá apreciar su elegancia. Incluso si usted no es un programador de confianza, debe ser capaz de escribir Expresiones del sencilla, por ejemplo, cuando se desea crear columnas personalizadas. Es poco probable que alguna vez tendrá que escribir un montón de código M, sin embargo. En casi todos los casos, puede utilizar la interfaz de usuario para generar los pasos para su consulta, En este capítulo aprenderá acerca de cómo escribir sus propias expresiones M; el lenguaje M y su sintaxis; objetos tales como tablas, listas y registros de uso general; y cómo crear sus propias funciones M para que pueda compartir la lógica de negocio entre los pasos e incluso entre las consultas.

Escribir M en el Editor de consultas antes de aprender cómo escribir M, lo que necesita saber dónde en la interfaz de usuario de energía de consulta puede escribir en él. Hay dos lugares que se pueden crear y editar las expresiones M utilizados por una consulta en el Editor de consultas: en la barra de fórmulas y en la ventana Editor avanzado. Además, para aquellas situaciones en las que desee escribir el código para su búsqueda desde cero, puede hacer clic en el botón De otras fuentes en el tabulador de consulta de la cinta de Excel y seleccione la opción de consulta en blanco para crear consultas con ningún código en ellas en absoluto.

La barra de fórmulas La barra de fórmulas en el Editor de consultas (que se muestra en la figura 5-1 ) Le permite ver y editar el código M de un paso existente y también para crear nuevos pasos en su búsqueda usando expresiones M escritas a mano.

115 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

La Figura 5-1. La barra de fórmulas en la ventana del Editor de consultas

La barra de fórmulas siempre muestra la expresión M para el paso en su consulta que se encuentra actualmente seleccionado en el panel Pasos Aplicadas en el lado derecho del Editor de consultas. Una vez que haya seleccionado un paso que puede hacer clic dentro de la barra de fórmulas para editar el código M generada por el paso; cuando haya terminado puede pulsar la tecla Intro en el teclado o haga clic en el botón de verificación a la izquierda de la barra de fórmulas para guardar los cambios. Al hacer esto, verá la nueva salida de la etapa mostrada. Si se hace un cambio y luego quiere desprenderse de ella, debe hacer clic en el botón de cruz en la parte izquierda del extremo de la barra de fórmulas. Como se vio en los capítulos 2 y 3, algunos pasos visibles en el panel Pasos Aplicadas tienen un icono de los engranajes junto a ellos, y haciendo clic en este icono le permite editar el escenario de este paso utilizando el mismo cuadro de diálogo que utilizó para crearlo. Si edita el código M de un paso en la barra de fórmulas y hacer un cambio que es sintácticamente correcta, pero no es compatible con la interfaz de usuario, el icono de engranajes desaparecerá, y usted no tendrá otra opción después de eso, pero para editar el paso utilizando la barra de fórmulas.

También puede crear un nuevo paso en su consulta haciendo clic en el ƒx botón en el lado izquierdo de la barra de fórmulas. Al hacer esto se añadirá un nuevo paso para la consulta, cuya expresión es simplemente el nombre del paso que fue seleccionado en el panel Pasos Aplicadas al hacer clic en el botón. Esto significa que el paso estará exactamente el mismo resultado que el paso anterior en la consulta. Una vez hecho esto, puede editar el código M en la barra de fórmulas como normal, alterando el paso para hacer lo que quiere hacer.

La ventana del editor de avanzada El código M para una consulta completa se puede ver y editar en la ventana Editor avanzado. Para abrir el Editor avanzado, vaya a la pestaña Vista de la cinta dentro del Editor de consultas y haga clic en el botón Editor avanzado, como se muestra en la figura 5-2 .

116 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

La Figura 5-2. La ventana Editor avanzado El editor avanzado no es más que un texto básico redactor no hay funciones como IntelliSense o resaltado de sintaxis para ayudarle a escribir código, solamente un mensaje en la esquina inferior izquierda de la ventana para decir si los errores de sintaxis están presentes en el código actualmente en la ventana. Cuando haya terminado de trabajar en el Editor avanzado puede hacer clic en el Hecho botón para guardar los cambios y cerrar la ventana o Cancelar para cerrar la ventana sin guardar los cambios.

Crear una consulta en blanco La única opción que aparece debajo del botón De Otro origen de la ficha de alimentación consulta de la cinta que ya no se ha mencionado es la última: Consulta en blanco. Al hacer clic en esta opción creará una nueva consulta consulta de potencia con sólo un paso en ella, y sin código para importar datos desde cualquier fuente de datos. Una vez que haya creado una consulta en blanco que puede escribir su propio código en la barra de fórmulas o el Editor avanzado; aún puede utilizar toda la funcionalidad de la interfaz de usuario del Editor de consultas, también, pero usted no será capaz de generar automáticamente cualquier código para importar datos de fuentes de datos externas. Esto es útil en situaciones donde se desea copiar el código M de una consulta en otro libro o de un artículo o entrada de blog en Internet, en una nueva consulta.

117 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Conceptos M Idioma Microsoft proporciona dos documentos muy detallados que describen el lenguaje M: la especificación del lenguaje de fórmulas de alimentación de consultas y el Poder Fórmula de consulta Biblioteca especificación. Estos documentos se pueden encontrar en el sitio web de Microsoft aquí: http://tinyurl.com/PQSpecs . Proporcionan una guía esencial para el lenguaje, el aprendizaje y cualquiera M necesitarán tener ambos a la mano mientras trabajan con fines de referencia, pero son largos y complejos. Este capítulo no trata de reemplazar estos documentos, sino que actúa como un compañero que le enseñe las características importantes de M de una manera que es, con suerte, más fácil de digerir.

Expresiones, valores y Dejar declaraciones Los dos conceptos fundamentales en el lenguaje M son los de expresiones y valores. Estos conceptos no son difíciles de entender. Los valores son valores como el número 1 o el texto “Hello World” o los objetos más complejos como las tablas. expresiones M valores de retorno cuando se evalúan, así que por ejemplo la expresión 10 + 10 devuelve el valor 20. Lo que no es tan obvio es que cada consulta de alimentación de consulta es de hecho una sola expresión M, y el valor que esta expresión devuelve es la salida de la consulta. ¿Cómo puede una consulta de alimentación de consulta sea una sola expresión M, sin embargo, cuando se compone de varios pasos? Esto es posible a través de la Dejar declaración, que permite que una sola expresión para ser dividida en múltiples expresiones más pequeñas. Considere la tabla Excel simple que se muestra en la figura 5-3 por ejemplo.

La Figura 5-3. Una simple tabla de Excel

Si ha importado esta tabla de consulta en el poder, después se filtró a ella para que se quedaron con las filas en las ventas es mayor que

5, y luego ordena la tabla en orden descendente por la columna Sales, que tendría una consulta con tres pasos, los nombres de los cuales se pueden ver en la figura 5-4 .

La Figura 5-4. Una consulta con tres pasos

118 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Si vas a la ventana Editor avanzado, verá el código M para toda la consulta consiste en una sola Dejar declaración, dada en el Listado 5-1.

Listado 5-1. Un ejemplo de una Dejar declaración dejar

Fuente = Excel.CurrentWorkbook () {[Name = "SalesTable"]} [Contenido], FilteredRows = Table.SelectRows (Fuente, cada uno [de ventas]> 5), SortedRows = Table.Sort (FilteredRows, {{ "Ventas", Order.Descending}}) en SortedRows Los nombres de los tres pasos en la consulta ( Fuentes, FilteredRows, y SortedRows) son claramente visibles en el código, como se puede ver. Cada paso en una consulta es de hecho uno de una lista separada por comas de variables definidas en el dejar

cláusula de una Dejar declaración, donde cada variable devuelve el resultado de la expresión M para ese paso. Las variables pueden acceder a los valores devueltos por otras variables, como en el ejemplo del Listado 5-1 se puede ver que SortedRows hace referencia al valor devuelto por FilteredRows, y FilteredRows a su vez hace referencia al valor devuelto por Fuente ( en este caso, las tres variables valores que son tablas de ida y vuelta). los Dejar instrucción devuelve el valor de la expresión dada en el en

cláusula, que en este caso es sólo el nombre de la última variable de la lista. Curiosamente, una Dejar declaración puede devolver el resultado de cualquier expresión: puede devolver el resultado de cualquier variable en su lista de variables, o puede devolver el resultado de una expresión que hace referencia a ninguna de las variables. Además, las variables pueden hacer referencia a cualquier otra variable de la lista, no sólo la variable declarada inmediatamente antes de él, incluyendo las variables declaradas más tarde en la lista. Sin embargo, es aconsejable mantener las variables en la lista en algún tipo de orden significativo para facilitar la lectura. Más importante aún, si se mueve alrededor de sus variables demasiado, el Editor de consultas ya no será capaz de mostrar los pasos individuales en los Pasos Aplicadas (aunque la propia consulta seguirá funcionando). También vale la pena señalar que un paso sólo se evalúa si el valor que devuelve es utilizado por otro paso o es el resultado final de la consulta.

escritura M Si bien la sintaxis M es bastante sencillo, hay algunas características del lenguaje que vale la pena resaltar antes de empezar a aprenderlo.

La Biblioteca Estándar M viene con un gran número de funciones integradas para que usted utilice, llamada la biblioteca estándar. Estas funciones son todos los que figuran en el documento Especificación Biblioteca Fórmula potencia de consulta mencionado en “Conceptos M idioma.” Volviendo al Listado 5-1, Excel.CurrentWorkbook (), Table.SelectRows (), y Table.Sort () son ejemplos de funciones de la librería estándar. Más funciones se agregan con cada versión del Poder consulta. Se puede ver otros textos de ayuda y ejemplos para las funciones de la biblioteca estándar mediante la creación de un paso en su consulta que devuelve esa función. Por ejemplo, si se crea un paso con la siguiente expresión (nótese que no hay soportes en el final del nombre de la función aquí):

Table.SelectRows a continuación, como se muestra en la figura 5-5 , La energía de consulta mostrará ayuda y ejemplos para la Table.SelectRows () función. Esto no es lo mismo que llamar o invocar la función de la etapa está devolviendo la función en sí. Si usted decide invocar la función puede hacer clic en el botón de invocación debajo de la lista ayuda, o el botón de función de invocación en la barra de herramientas del Editor de consultas.

119 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Figura 5-5. Visualización de la ayuda para el Table.SelectRows () función

entre mayúsculas y minúsculas A diferencia de otros idiomas que pueden estar familiarizados con, M es sensible a mayúsculas-algo que puede causar una gran frustración cuando se empieza a escribir sus propias expresiones. Esto significa que aunque el poder de consulta reconoce Excel.CurrentWorkbook () como una función de la biblioteca estándar, Excel.Currentworkbook () No se reconocerán porque la carta w No se escribe con mayúscula. Como resultado de ello, es necesario tener mucho cuidado cuando se escribe código M para evitar los errores causados ​por la sensibilidad caso.

tipos Ya ha visto en el capítulo 3 que cada columna en una consulta es de un tipo de datos en particular. De hecho, todos los valores de M tiene un tipo, tanto si se trata de un tipo simple como un número o un tipo estructurado como una mesa; esto incluye las variables de la lista de variables de la Dejar declaración. Usted no tiene que declarar el tipo de un valor-M va a determinar dinámicamente eso.

M es fuertemente tipado, lo que significa que, si bien la expresión "El número" y "1" funciona bien y devuelve el texto

"El número 1" la expresion "El número" & 1 arroja un error porque el y operador (que concatena dos valores de texto) no se puede utilizar con un valor de texto y un número. Hay una gran cantidad de funciones disponibles en la librería estándar para convertir un tipo a otro, y en este caso la función Number.ToText se puede utilizar para emitir un número a texto como sigue:

"El número" & Number.ToText (1) 120 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Usted puede comprobar para ver si un valor es un tipo particular mediante el es operador. Por ejemplo la expresión 1 es el número

devuelve el valor CIERTO, mientras 1 es el texto

devuelve el valor FALSO.

La declaración de fechas, horas, datetimes datetimezones, y duraciones Si tiene que declarar valores de tipo fecha, fecha y hora, DateTimeZone, o duración en una expresión, puede utilizar la función intrínseca # fecha(), #fecha y hora(), #datetimezone (), y # duración(). Por ejemplo, la firma de # fecha y hora() es como sigue: # de fecha y hora (año, mes, día, hora, minuto, segundo) Si utiliza # de fecha y hora (2014,2,28,13,48,46) en una expresión M devolverá el fecha y hora valor que contiene la Fecha 28 de febrero de 2014 y el tiempo 13:48:46. Similar, # fecha (2014,1,1) devuelve la fecha del 1 de enero de 2014 y el # duración (1,0,0,0) devuelve una duración equivalente a un día.

comentarios Sin duda, ya saben que es importante añadir comentarios a cualquier código que escriba, y el código M no es una excepción. Hay dos formas de añadir comentarios al código de M: los comentarios de una sola línea son precedidos por // y comentarios multilínea empiezan con / * y terminan con * /. Listado 5-2 muestra la misma consulta que el listado 5-1, pero con comentarios añadidos:

Listado 5-2. Un ejemplo de código comentado / * Esta consulta cargas de datos de la tabla de contenido en la hoja de trabajo

y luego se filtra y agregados que * / dejar // Cargar los datos de la tabla de contenido

Fuente = Excel.CurrentWorkbook () {[Name = "SalesTable"]} [Contenido], // filtro donde las ventas> 5 FilteredRows = Table.SelectRows (Fuente, cada uno [de ventas]> 5), // clasificar en orden descendente por ventas

SortedRows = Table.Sort (FilteredRows, {{ "Ventas", Order.Descending}}) en SortedRows Por desgracia, a pesar de los comentarios son siempre visibles en el Editor avanzado, en la mayoría de los casos que no se muestran en las expresiones en la barra de fórmulas. Se pueden añadir comentarios al inicio o al final de la expresión utilizada para un paso en la barra de fórmulas, pero al hacer clic a otro paso y luego regresa, los comentarios son filtrados. Sólo se mostrarán los comentarios incrustados dentro de una expresión, como en este ejemplo:

Table.Sort (/ * esto es un comentario * / FilteredRows, {{ "Ventas", Order.Descending}})

121 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

atrapando errores Como se vio en el capítulo 3, es posible filtrar las filas de una tabla que contiene los valores de error. Sin embargo, es mucho mejor para capturar los errores en el nivel de expresión y se puede hacer que el uso de una tratar de otra manera ... expresión.

Por ejemplo, la figura 5-6 muestra una tabla en la que una columna contiene números y una columna contiene una mezcla de números y texto.

Figura 5-6. Una tabla que contiene los números y el texto

Si importa esta tabla en la alimentación de consulta, a continuación, establezca explícitamente el tipo de la columna TextColumn al texto, a continuación, puede crear una columna personalizada utilizando la siguiente expresión para tratar de sumar los valores en NumberColumn y TextColumn:

[NumberColumn] + Number.FromText ([TextColumn]) los Number.FromText () función intenta tomar un valor de texto y convertirlo a un número (también conocido como “fundición” el valor de texto a un número); si el valor de texto no se puede convertir la función devuelve un error, por lo que en este caso la segunda y tercera filas de la tabla contendrá valores de error como se muestra en la figura 5-7 .

La Figura 5-7. Una columna personalizada que contiene los valores de error

Una forma de prevenir los errores que aparezcan sería cambiar la expresión a lo siguiente: [NumberColumn] + (prueba Number.FromText ([TextColumn]) en caso contrario 0)

El resultado de la consulta ahora será como se muestra en la figura 5-8 .

Figura 5-8. Una columna personalizada donde se han atrapado errores

122 www.it-ebooks.info

Capítulo 5 ■ Introducción a M los tratar expresión se evalúa la expresión que se pasa a él, y si no se produce ningún error, entonces se devuelve el valor devuelto por que la expresión; si se produce un error, sin embargo, entonces el valor se especifica en el de otra manera cláusula se devuelve en su lugar.

La lógica condicional La lógica condicional en M puede ser implementado utilizando una si ... entonces ... otra cosa expresión. Funciona exactamente como era de esperar: la expresión booleana en el Si cláusula se evalúa, y si el resultado es cierto entonces el resultado de la expresión en el entonces cláusula se devuelve; de lo contrario el resultado de la expresión en el más se devuelve cláusula. Tomando los datos mostrados en la tabla de la figura 5-6 como punto de partida, si importa que los datos en una nueva consulta Consulta de energía y crear una columna personalizada utilizando la siguiente expresión:

si [NumberColumn]> 2 a continuación, "Más de 2" más "2 o menos" entonces la salida será como se muestra en la figura 5-9 .

Figura 5-9. Una columna personalizada utilizando una si ... entonces ... otra cosa expresión

No hay un equivalente de la instrucción Case como se encuentra en otros idiomas, pero se puede múltiple nido Si

expresiones juntos, como en el siguiente ejemplo: si [NumberColumn]> 3 a continuación, "Más de 3" else if [NumberColumn]> 2 a continuación, "Más de 2" más "2 o menos"

Listas, Registros y tablas Usted ya ha aprendido acerca de los tipos de datos primitivos números, fechas, texto, etc.-que describen los valores que por lo general ver células dentro de una tabla. Cuando vio cómo navegar a través de una base de datos SQL Server o un archivo XML también se vio diferentes tipos de valores que aparezcan en celdas: las tablas y registros. Estos son los tipos estructurados: objetos que contienen muchos valores unidas entre sí de una manera particular.

Liza Una lista es una secuencia ordenada de valores, similar en algunos aspectos a una matriz en otros lenguajes de programación. Elementos de una lista pueden ser de cualquier tipo, sin embargo, e incluso se puede tener listas de las listas. Las listas son útiles como un medio para un fin: que va a utilizar cuando se está escribiendo cálculos más complejos, pero es poco probable que el resultado final de una consulta será una lista. Muchos de los ejemplos M más complejos a partir de este punto en adelante contará con listas en alguna capacidad.

123 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

La definición de listas manualmente Las listas pueden ser definidas manualmente como una lista separada por comas de valores rodeados de aparatos de ortodoncia. Por ejemplo, { 1,2,3}

define una lista con los valores 1, 2, y 3 en este orden; {" A B C"} define una lista con los valores “A”, “B”, y “C” en ella. Se puede definir una lista continua de números enteros usando la sintaxis x..y, que devolverá una lista de números de X a y inclusivo; por ejemplo, { 1..4} devuelve la lista { 1,2,3,4}. Es posible tener una lista vacía con artículos en ella, que se escribe como {}. Cada elemento de una lista puede ser en sí mismo una lista, por lo que {{ 1,2}, {3,4}} define una lista que contiene dos elementos, cada uno de los cuales es una lista que contiene dos elementos.

Trabajar con listas en el Editor de consultas Al hacer clic en un paso en una consulta que devuelve una lista, el Editor de consultas mostrará el contenido de esa lista y una pestaña nueva lista aparecerá en la cinta, como se muestra en la figura 5-10 .

Figura 5-10. Una lista que se muestra en el Editor de consultas

al hacer clic en el A la mesa botón de la ficha Lista creará un nuevo paso en su consulta que convierte la lista en una tabla.

Funciones que generan listas Para generar más secuencias de números complejos, puede utilizar la List.Numbers () función, la firma para la que es: List.Numbers (comienzan con el número, cuentan como el número, la subasta opcional como el número anulable)

los comienzo parámetro es el número para empezar a, contar es el número de valores en la lista para volver, y incremento

es la diferencia entre cada valor de la lista, por lo que la expresión List.Numbers (5,4,3) devuelve la lista { 5,8,11,14}. Similar, List.Dates () devuelve un rango de fechas a partir de una fecha determinada, por lo List.Dates (#date (2014,1,1), 3, # duración (1,0,0,0)) devuelve una lista que contiene las tres primeras fechas en enero de 2014. Muchas otras funciones de la biblioteca estándar son capaces de convertir valores de otros tipos de listas, tales como Mesa. Listar(), que convierte una tabla en una lista, y Table.column (), que devuelve todos los valores de una columna en una tabla como una lista.

124 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

La agregación de valores en las listas Una de las principales razones por las que se desea almacenar una secuencia de números en una lista es ser capaz de agregar los valores de alguna manera. Todos los métodos básicos de agregación que se esperan son apoyados a través de funciones de la biblioteca estándar. List.Count () devuelve el número de elementos de una lista, por lo List.Count ({1,2,3}) devuelve el valor 3 porque hay tres números de la lista { 1,2,3}, y List.Sum ({1,2,3}) devuelve el valor 6, la suma de los números de la lista. Otras funciones de agregación incluyen List.Product (), List.Average (), List.Mode (), List.StandardDeviation (), List.Max (), y List.Min (). Algunas de estas funciones de trabajo con las listas que contienen otros tipos de datos, también, por lo List.Sum (),

por ejemplo, también se suma una lista de valores de tipo duración.

clasificación de listas los List.sort () función se puede utilizar para ordenar los elementos de una lista. Su firma es:

List.sort (lista como lista, comparisonCriteria opcionales como los hay) El primer parámetro es la lista para ser ordenados y el segundo parámetro controla la forma en la clasificación se lleva a cabo. En la mayoría de los casos todo lo que se necesita para declarar en el segundo parámetro es Order.Descending o Order.Ascending, aunque es posible especificar criterios de ordenación más complejos. Por ejemplo List.sort ({1,2,3,4,5}, Order.Descending)

devuelve la lista { 5,4,3,2,1} y List.sort ({ "Helen", "Natasha", "Mimi", "Chris"}, Order.Ascending) devuelve la lista {" Chris", "Helen", "Mimi", "Natasha"}. List.Reverse () invierte el orden de los elementos en una lista, de modo que List.Reverse ({3,5,4}) devoluciones { 4,5,3}.

Las listas de filtrado Hay varias funciones que se pueden utilizar para filtrar los elementos de una lista. List.First () y List.Last () devolver los primer y último elemento de una lista, mientras List.FirstN () y List.LastN () listas que son un número determinado de valores desde el principio y el final de una lista regresar. List.Distinct () devuelve una lista de todos los valores distintos de una lista.

Más escenarios de filtrado avanzadas se pueden manejar con el List.Select () función. La firma de esta función es:

List.Select (lista como lista, condición como la función) El primer parámetro es la lista para ser filtrada; el segundo es algo nuevo: que necesita para escribir una expresión que es una función. Este tema se tratará en profundidad más adelante en este capítulo, pero por ahora se le complace saber que la sintaxis básica para hacer esto es bastante sencillo. Por ejemplo:

List.Select ({1,2,3,4,5}, cada uno _> 2) devuelve la lista { 3,4,5}. los cada expresión visto en este ejemplo es una manera de definir una función sin tipo, sin nombre cuyo único parámetro está representado por el carácter de subrayado _. Por lo tanto cada _> 2 es una función que devuelve

CIERTO cuando el valor pasado a es mayor que 2 y FALSO de otra manera, y cuando se utiliza en el segundo parámetro de List.Select (), cada elemento de la lista se pasa a ella, y sólo los elementos en los que la función devuelve cierto son devueltos.

125

www.it-ebooks.info

127