Power-Query-For-Power-Bi-And-Excel Parte2.en - Es

Capítulo 5 ■ Introducción a M Archivos Se puede pensar en un registro como si fuera una tabla con sólo una fila en el m

Views 1,522 Downloads 0 File size 7MB

Report DMCA / Copyright

DOWNLOAD FILE

Recommend stories

Citation preview

Capítulo 5 ■ Introducción a M

Archivos Se puede pensar en un registro como si fuera una tabla con sólo una fila en el mismo. Es una secuencia ordenada de campos, donde cada campo tiene un nombre y un valor único (de cualquier tipo) asociado con él. Los registros pueden ser definidas manualmente como una lista commadelimited de campo / valor rodeadas por corchetes. Por ejemplo, el siguiente define un registro con cuatro campos llamados nombre, apellido, sexo y la ciudad:

[Nombre = "Chris", apellido = "Webb", sexo = "Hombre", la ciudad = "Amersham"] Al igual que con una lista, cuando ve un paso que devuelve un registro en el Editor de consultas se puede ver cada campo en el registro, como se muestra en la figura 5-11 .

Figura 5-11. Un registro que se muestra en el Editor de consultas

al hacer clic en el en la tabla botón en la barra de herramientas convierte el registro en una tabla con una fila para cada campo, como se muestra en la figura 5-12 .

Figura 5-12. Una tabla creada a partir de un registro

126

www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Mesas La tabla es el más importante de todos los tipos estructurados. En la mayoría de los casos los datos se importan en una consulta es en forma de tabla; la mayoría de los pasos de un retorno tablas de consulta; y casi siempre se vuelve tablas de consultas. Una tabla consta de datos organizados en filas y columnas, donde cada columna tiene un nombre único y contiene datos de un tipo particular.

Creación de tablas Si nos fijamos en el código M para el primer paso en la mayoría de las consultas en la barra de fórmulas, verá una expresión que devuelve una tabla de datos de una fuente de alimentación de datos fuera de consulta. Por ejemplo, en el Listado 5-1 el paso de origen utiliza la expresión

Excel.CurrentWorkbook () {[Name = "SalesTable"]} [Contenido] para devolver el contenido de la tabla llamada SalesTable en el actual libro de Excel. La biblioteca estándar contiene muchas funciones similares que le permiten recuperar las tablas de datos de diferentes fuentes de datos, y se utilizan en las expresiones M generados por toda la funcionalidad que usted vio en el capítulo 2. También es posible crear tablas sin una fuente externa de datos en un número de maneras diferentes. En primer lugar se puede utilizar el # mesa() función intrínseca, que toma dos parámetros: una lista que contiene los encabezados de columna y una lista (o lista de listas) que contiene las filas de las tablas. Por ejemplo, la siguiente expresión:

# mesa ({ "fruto", "venta"}, {{ "Manzanas", 1}, { "naranjas", 2}})

devuelve la tabla mostrada en la figura 5-13 .

Figura 5-13. Una mesa creado usando el # mesa función intrínseca Las columnas de las tablas creadas de esta manera todos tienen el tipo alguna; puede especificar los tipos de columnas mediante la especificación de un registro en el primer parámetro en lugar de una lista, como en el siguiente ejemplo:

# tabla (tabla tipo [Fruta = texto, Sales = número], {{ "manzanas", 1}, { "Naranjas", 2}}) Otras funciones también se pueden utilizar para crear tablas, tales como Table.FromRows ():

Table.FromRows ({{ "manzanas", 1}, { "naranjas", 2}}, { "fruto", "Ventas"}) y Table.FromRecords (): Table.FromRecords ({[Fruta = "manzanas", Ventas = 1], [fruta = "naranjas", Ventas = 2]})

La agregación de datos en las tablas En el capítulo 3 se vio una gran cantidad de ejemplos de cómo agregar los datos de las tablas a través de la interfaz de usuario utilizando la

Agrupar por botón, y esto será lo suficientemente bueno para la mayoría de sus necesidades. Si tienes curiosidad sobre el código M generada por estas operaciones, por supuesto puede ver el código M que la interfaz de usuario genera en la barra de fórmulas o el Editor avanzado, y esto es una buena manera de aprender acerca de las funciones que están disponibles para este propósito. Veamos cómo algunas de estas funciones de agregación trabajo utilizando los datos de la muestra ver en la figura 5-14 .

127 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Figura 5-14. tabla de datos de muestra para la agregación

Para encontrar el número de filas en esta tabla se puede utilizar el Table.rowCount () función. Listado 5-3 muestra una consulta que carga los datos de la tabla de Excel y cuenta el número de filas en el mismo.

Listado 5-3. Contando filas en una tabla con Table.rowCount () dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[Name = "SalesTable"]} [Contenido], // el número de filas

CountRowsInTable = table.rowCount (Fuente) en CountRowsInTable Vale la pena señalar que esta consulta devuelve el valor 12 como un número, como se muestra en la figura 5-15 Y no una mesa

con una célula que contiene el valor 12, aunque si el resultado de la consulta se carga en la hoja de cálculo o el modelo de datos de Excel No obstante, se trata como una tabla con una fila y una columna.

Figura 5-15. La salida de table.rowCount en el Editor de consultas

128 www.it-ebooks.info

Capítulo 5 ■ Introducción a M Para obtener una salida de la tabla tipo y para realizar otros tipos de agregación que se necesitan para utilizar el Table.Group ()

función en lugar de Table.rowCount (). La firma de Table.Group () es como sigue: Table.Group (tabla como tabla, como cualquier clave, como aggregatedColumns lista, groupKind opcional como el número anulable, comparador opcional como función anulable) El primer parámetro es la tabla que va a agregarse, el segundo parámetro es una lista que contiene las columnas a grupo por, y el tercer parámetro especifica la manera de agregar los datos. Para contar el número de filas de la tabla de ejemplo, el código para toda la consulta M se muestra en el listado 5-4.

Listado 5-4. Contando filas en una tabla con Table.Group () dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[Name = "SalesTable"]} [Contenido], // el número de filas y devolver una tabla

GroupedRows = Table.Group (Fuente, {}, {{ "cuenta de filas", cada table.rowCount (_), el número de modelo}}) en GroupedRows En este ejemplo, el primer parámetro utilizado por Table.Group () es Fuente, la salida de la primera etapa. El segundo parámetro es una lista vacía, porque en este caso no se está agrupando por cualquier columna en la tabla. El tercer parámetro es una lista que contiene un elemento que es en sí mismo una lista, y que la lista contiene tres elementos: el nombre de la columna en la tabla de salida, cuenta de filas; un cada expresión que llama Table.rowCount () para obtener el recuento; y el tipo de número, que especifica el tipo de esta columna. La salida de esta consulta se muestra en la figura 5-16 .

Figura 5-16. La salida de Table.Group () en el Editor de consultas Listado 5-5 muestra un ejemplo más complejo que los grupos de la columna de la trimestre y no sólo cuenta filas sino también suma los valores de la columna Sales.

Listado 5-5. La agrupación por trimestre con Table.Group dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[Name = "SalesTable"]} [Contenido], // Grupo de cuartos, el número de filas y suma las ventas GroupedRows = (Table.Group

Fuente, { "Quarter"},

129 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

{{ "Cuenta de filas", cada table.rowCount (_), número de tipo}, { "suma de las ventas", cada List.Sum ([Ventas]), el número de modelo}}

) en GroupedRows En este ejemplo se puede ver que el segundo parámetro es ahora una lista con un elemento en él, “Barrio”, el nombre de la columna que se utiliza en el Agrupar por operación), y ahora hay una segunda lista en el segundo parámetro que utiliza

List.Sum () para resumir todos los valores en la columna de ventas. La salida se muestra en la figura 5-17 .

Figura 5-17. Contar y sumar por trimestres usando Table.Group () Una última cosa a mencionar acerca Table.Group () es el cuarto parámetro, que controla el tipo de agrupación. El valor por defecto es GroupKind.Global, y esto significa que cuando se agrupan por una columna, el orden de clasificación de la tabla no es relevante: todas las filas relacionadas con un valor distinto en una columna se agregan juntos. GroupKind.Local, el otro valor posible, significa que Table.Group () no tener un orden de mesa en cuenta y sólo los grupos por rangos de valores continuos. Para ilustrar esto, la figura 5-18 muestra una tabla que contiene las ventas y unidades por fecha con una columna que muestra si la fecha era un día de la semana o cayó en un fin de semana.

Figura 5-18. tabla de ventas mostrando los días laborables y fines de semana

130 www.it-ebooks.info

Capítulo 5 ■ Introducción a M La siguiente expresión utiliza Table.Group () para encontrar el recuento de filas agrupadas por la columna de la WeekdayOrWeekend; el valor por defecto para el cuarto parámetro, GroupKind.Global, se establece explícitamente en aras de la claridad:

Table.Group (Fuente, { "WeekdayOrWeekend"},

{{ "Cuenta de filas", cada table.rowCount (_), número de tipo}}, GroupKind.Global) La salida de esta expresión se muestra en la figura 5-19 .

Figura 5-19. Table.Group () salida utilizando GroupKind.Global

Si en lugar de utilizar GroupKind.Local en el cuarto parámetro, como sigue: Table.Group (Fuente, { "WeekdayOrWeekend"},

{{ "Cuenta de filas", cada table.rowCount (_), número de tipo}}, GroupKind.Local) la salida será como se muestra en la figura 5-20 . La razón por la que ahora hay cuatro filas en lugar de dos, es que Table.Group () sólo ha agregado las secuencias de filas que tienen los mismos valores en la columna de la WeekdayOrWeekend: las tres primeras filas de la tabla original se lunes a viernes, a continuación, las dos filas siguientes son los fines de semana, a continuación, los próximos cinco filas son los días de semana y las dos últimas filas son los fines de semana .

Figura 5-20. Table.Group () salida utilizando GroupKind.Local

Tablas de clasificación Al igual que con una lista, las filas de una tabla se clasifican inherentemente incluso si el orden de las filas es simplemente el orden en que llegaron desde la fuente de datos. Las opciones para ordenar una mesa son muy similares a las opciones para ordenar una lista, por lo Table.ReverseRows ()

invierte las filas de una tabla y Table.Sort () ordena las filas de una tabla en orden ascendente o descendente por una o más columnas. El uso de los datos mostrados en la figura 5-18 como un ejemplo, la siguiente expresión primeras clases esta tabla en orden descendente por la columna de la WeekdayOrWeekend, a continuación, en orden ascendente por la columna de la venta:

Table.Sort (Fuente, {{ "WeekdayOrWeekend", Order.Descending}, { "Ventas", Order.Ascending}})

131 www.it-ebooks.info

Capítulo 5 ■ Introducción a M Observe cómo, en el segundo parámetro, una lista de listas se utiliza para pasar las combinaciones de columnas y criterios de ordenación utilizados. La salida de esta expresión se muestra en la figura 5-21 .

Figura 5-21. Una tabla ordenada con Table.Sort ()

Tablas de filtrado Una vez más, las funciones de tablas de filtrado son similares a las funciones disponibles para listas de filtrado. Table.First () y

Tabla.APELLIDO () devolver la primera y última filas de una tabla; Table.FirstN () y Table.LastN () devolver la primera y la última norte filas de una tabla. Table.SelectRows () que funciona de una manera similar a List.Select (), aunque en el segundo parámetro se puede hacer referencia al valor en una columna de la tabla sin utilizar la notación _. Por ejemplo, usando los datos de la tabla se muestra en la figura 5-18 , Para eliminar todas las filas en la columna Sales contiene valores de menos de 6, puede utilizar la expresión:

Table.SelectRows (Fuente, cada uno [de ventas]> 5) En este caso se puede usar [ Ventas] para referirse al valor de la columna Ventas para la fila actual como las iteraciones de la función sobre la mesa. La salida de esta expresión se muestra en la figura 5-22 .

Figura 5-22. Una tabla filtrada con Table.SelectRows ()

132 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Pivotantes y Unpivoting Tablas Se vio en el capítulo 3 cómo UNPIVOT los datos en una tabla en la interfaz de usuario. Las funciones de seguimiento detrás de esta característica son Table.UnPivot (), unpivots el que una lista dada de columnas en una tabla, y Table.UnPivotOtherColumns (), unpivots cuales todos menos una lista dada de columnas en una tabla. Las firmas de los mismos son:

Table.Unpivot (tabla como tabla, pivotColumns como lista, attributeColumn como texto, ValueColumn como texto) Table.UnpivotOtherColumns (tabla como tabla, pivotColumns como lista, attributeColumn como texto, ValueColumn como texto)

Una vez más, teniendo la tabla mostrada en la figura 5-18 como punto de partida, la expresión

Table.UnpivotOtherColumns (Fuente, { "Fecha", "WeekdayOrWeekend"}, "Atributo", "Valor") unpivots la mesa de manera que en lugar de dos columnas para ventas y unidades, que tienen una sola columna que contiene los valores para ambos y dos filas en la tabla de salida para cada fila de la tabla original, como se muestra en la figura 5-23 .

Figura 5-23. Una tabla no girados con Table.UnPivotOtherColumns ()

133 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Lo que no es posible en la interfaz de usuario es lo contrario de lo anterior: la capacidad de girar una mesa, así filas se convierten en columnas. Para ello, puede utilizar Table.Pivot (), la firma para la que es:

Table.Pivot (tabla como tabla, pivotValues ​como lista, attributeColumn como texto, ValueColumn como texto, aggregationFunction opcional como función anulable) Usando la tabla de la figura 5-18 una vez más, la siguiente expresión gira la mesa para que en lugar de una columna para las ventas que tiene dos: una para las ventas en un día laborable y otra para las ventas en un fin de semana:

Table.Pivot (Fuente, { "Día de la semana", "fin de semana"}, "WeekdayOrWeekend", "Ventas", List.Sum) La salida de esta expresión se muestra en la figura 5-24 . Observe cómo la columna Unidades no se ve afectada por el pivote

operación, y cómo era necesario suministrar una lista de valores en la columna WeekdayOrWeekend-en lugar de codificar esta lista, una combinación de la Table.column () función (que devuelve una lista de valores de una columna en una tabla) y List.Distinct () ( que devuelve sólo los valores distintos de una lista) podría utilizarse en su lugar:

Table.Pivot (Fuente, List.Distinct (table.column (Fuente: "WeekdayOrWeekend")), "WeekdayOrWeekend", "Ventas", List.Sum)

Figura 5-24. Una mesa con pivotted Table.Pivot () El parámetro final de Table.Pivot (), que en este ejemplo es el List.Sum () función, se utiliza si la operación de pivote necesita agregar ningún valor. En este ejemplo no es necesario, pero si había varias filas en la tabla con la misma combinación de valores de la fecha y columnas WeekdayOrWeekend, entonces sería necesario agregar los valores de estas filas, ya que la tabla resultante contiene sólo una fila para cada fecha.

134 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Tablas y claves A pesar de este hecho está bien escondido, tablas de consulta en el poder pueden tener claves primarias y externas definidas por ellos. Tablas importadas de fuentes de datos, tales como bases de datos relacionales tendrán claves definidas por ellos de forma automática y se pueden definir sus propias columnas clave en cualquier tabla que cree.

La interfaz de usuario no muestra las columnas en una tabla son las columnas de clave, pero se puede utilizar la Table.Keys ()

para devolver una lista de las columnas de clave en una mesa. Por ejemplo, si se importa la tabla DimDate en la base de datos de SQL Server Adventure Works utilizado en el capítulo 2, y luego utiliza la Table.Keys () en función de la tabla resultante de la consulta, devolverá una lista que contiene dos registros: uno para la columna de DateKey, la clave primaria; y uno para la columna de FullDateAlternateKey, que es una clave externa. Figura 5-25 muestra el resultado de la expansión de esta lista de registros en una tabla:

Figura 5-25. Las columnas de clave en la tabla DimDate Las llaves se pueden añadir a una tabla que se ha definido ninguna utilizando el Table.AddKey () función. Utilizando los datos de la tabla de la figura 5-18 El listado 5-6 muestra cómo definir una clave primaria basada en la columna de la fecha después de la tabla se ha importado.

Listado 5-6. dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[name = "DailySales"]} [Contenido], // definir una clave principal en la columna Fecha DefineDateKey = Table.AddKey (Fuente, { "fecha"}, true) en DefineDateKey Tenga en cuenta que el uso de Table.AddKey () en realidad no comprueban si las columnas que se utilizan para definir la tecla contener valores distintos, por lo que depende de usted para asegurarse de que lo hacen en alguna otra forma.

La realización de algunos otros tipos de operación en una tabla también tiene el efecto secundario de definir una clave principal en una tabla, como la selección de una columna en el Editor de consultas y haciendo clic en el botón Eliminar duplicados de la barra de herramientas, que de hecho genera una expresión utilizando el Table.Distinct () función para eliminar todas las filas en la tabla que han duplicado valores en la columna seleccionada, y que a su vez define una clave principal en la columna seleccionada. Definición de teclas en una tabla tiene algunas ventajas que son inmediatamente obvias. Ciertas funciones, tales como los que se agregan los datos en una tabla, pueden funcionar mejor cuando las llaves se definen en una mesa, y las teclas se utiliza cuando la importación de varias tablas de una base de datos relacional, junto con las relaciones entre ellos. Dicho esto, las teclas definidas sobre una mesa afectarán cómo se puede hacer referencia a filas individuales en una tabla, como se puede encontrar en la siguiente sección.

Selecciones y Proyecciones Los valores individuales en las Tablas, registros o listas pueden ser referenciados usando los de selección y de proyección operadores.

135 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Hacer referencia a elementos de las listas Teniendo en cuenta que la lista se ordena por sí, se puede hacer referencia a un elemento de una lista usando un índice basado en cero. Por ejemplo, si se toma la lista {" A B C"}, entonces la expresión {" A", "B", "C"} {0} devuelve “A” y la expresión {" A", "B", "C"} {2} devuelve “C”. Si utiliza un índice que no existe en la lista, como en {" A", "B", "C"} {4}, por defecto se devolverá un error; pero si se agrega el? operador en el extremo de la expresión, como en {" A", "B", "C"} {4} ?, obtendrá un valor nulo devuelto en lugar de un error.

Hacer referencia a filas en las tablas Las filas de mesas pueden ser referenciados por el índice de la misma manera como elementos de una lista, aunque en lugar de los valores individuales que están siendo devueltos, un registro que representa esa fila se devuelve en su lugar. Figura 5-26 muestra una tabla simple que se puede utilizar para ilustrar esto.

Figura 5-26. Una sencilla tabla Listado 5-7 muestra cómo se puede hacer referencia la primera fila de la tabla: la expresión Fuente {0} devuelve el registro que se muestra en la figura 5-27 que contiene los valores de la primera fila de la tabla.

Listado 5-7. dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[name = "FruitSales"]} [Contenido], // Devuelve la primera fila de la tabla FirstRow = Fuente {0} en

Primera fila

Figura 5-27. Registro que representa la primera fila de una tabla

La misma fila de la tabla se puede hacer referencia de manera diferente, utilizando la expresión Fuente {[Fruta = "Manzanas"]}. Esta busca en la tabla de la fila en la columna de la fruta contiene el valor “manzanas”; Si no se encuentra ninguna fila, o si se encuentran varias filas, se devuelve un error. Por lo tanto en el ejemplo actual la expresión Fuente {[Fruta = "uvas"]} devolvería un error, pero la adición de la? operador al final de una expresión,

136 www.it-ebooks.info

Capítulo 5 ■ Introducción a M como en Fuente {[Fruta = "uvas"]} ?, devuelve un valor nulo en lugar de un error. Si la columna que se busca en la expresión es una clave principal de la tabla, entonces no debería haber ningún riesgo de filas que contienen valores duplicados y por lo tanto no hay riesgo de errores.

Hacer referencia a los campos de registros Los valores de los campos de un registro se puede hacer referencia mediante el nombre del campo. Listado 5-8 se basa en la consulta que se muestra en el listado 5-7 para devolver el valor del campo llamado “fruta” en la primera fila de la tabla utilizando la expresión

FirstRow [Fruta], que es el valor “manzanas”. Listado 5-8. dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[name = "FruitSales"]} [Contenido], // Devuelve la primera fila de la tabla FirstRow = Fuente {0} ?, // devuelve el valor de la primera columna de la primera fila, llamado Fruit FirstColumn = FirstRow [Fruta] en Primera columna

En lugar de devolver un valor individual, también es posible devolver un registro que contiene sólo algunos de los campos de un registro más grande pasando un registro que contiene los nombres de columna en lugar de sólo un nombre de columna. Por ejemplo, el cambio de la última línea del Listado 5-7 para usar la expresión FirstRow [[Fruta], [ventas]] devolvería el registro se muestra en la figura 5-28 .

Figura 5-28. Record que representa los valores de las dos primeras columnas de una fila

Hacer referencia a los valores en los cuadros La elaboración de lo que ha aprendido hasta el momento, se hace muy fácil hacer referencia a un valor individual en una tabla seleccionando una fila y luego una columna en la misma expresión. Listado 5-9 muestra cómo recuperar el valor de la columna de la fruta de la primera fila de la tabla de ejemplo en una sola expresión.

Listado 5-9. dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[name = "FruitSales"]} [Contenido], // devuelve el valor de la primera columna de la primera fila, llamado Fruit firstValue = Fuente {0} [Fruta] en

firstValue

137 www.it-ebooks.info

Capítulo 5 ■ Introducción a M La expresión importante aquí, el que devuelve el valor que se desea, es Fuente {0} [Fruta]. El patrón de NombreTabla {rowIndex | concordancia de palabra clave} [ColumnName] para recuperar un único valor de una tabla es muy común en M, y de hecho aparece en el primer paso en la consulta que se muestra en el listado 5-9, también. los Excel.CurrentWorkbook ()

función devuelve una tabla que contiene todas las tablas de Excel en el libro actual, y Excel.CurrentWorkbook () {[name = "FruitSales"]} [Contenido] devuelve el valor de la [ Contenido] columna de la fila de esa tabla, donde la columna Nombre contiene el valor “FruitSales”. También es posible utilizar la interfaz de usuario para seleccionar un valor individual en una tabla. Para ello debe hacer clic derecho en una tabla y luego elegir la profundización desde el menú del botón derecho como se muestra en la figura 5-29 .

Figura 5-29. El Drill Down opción del menú del botón derecho

Es importante entender, sin embargo, que las referencias generadas utilizando este método variarán dependiendo de si una clave principal se ha definido para esa tabla. Si se ha definido ninguna clave principal, entonces la referencia se basa en el índice de fila, por ejemplo Fuente {0} [Fruta]. Si se ha definido una clave principal, sin embargo, la referencia utilizará un valor de las columnas de clave primaria en lugar del índice de la fila, por ejemplo Fuente {[Fruta = "Manzanas"]} [Fruta]. Los dos son sutilmente diferentes: la primera expresión siempre devolverá el valor de la columna de la fruta por la primera fila de la tabla, mientras que el segundo volverá lo que fila de la tabla contiene el valor “Manzanas” en la columna de la fruta.

funciones Así como la capacidad de utilizar todas las funciones de la biblioteca estándar, M le permite definir y utilizar sus propias funciones. Estas funciones pueden ser definidas dentro de una consulta o incluso como consultas separadas, y son una forma muy útil de compartir la lógica de negocio entre diferentes pasos en una consulta o entre varias consultas.

Definición de funciones Dentro de una consulta Listado 5-10 muestra como una función se puede definir como un paso dentro de una consulta.

Listado 5-10. dejar

// Definir una función que multiplica dos números luego añade una ExampleFunction = (x, y) => (x * y) 1, // Llame a la función y pasar 3 y 4 como parámetros QueryOutput = ExampleFunction (3,4) en

QueryOutput

138 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

En esta consulta el primer paso define una función llamada ExampleFunction que toma dos parámetros, x e y, y devuelve el valor de (x * y) + 1. El siguiente paso, QueryOutput, llama a esta función con los valores 3 y 4, y devuelve el valor de (3 * 4) + 1, que es 13. Los parámetros de una función se puede especificar para ser un tipo particular, y también es posible especificar el tipo del valor que la función devuelve. Los parámetros pueden ser marcados como opcionales, en cuyo caso no es necesario pasar cuando la función se llama; si no se pasan entonces el valor del parámetro será nulo. Los parámetros opcionales deben estar inscritos después de todos los parámetros necesarios en la lista de parámetros de una función. Listado 5-11 muestra otra consulta que declara una función, esta vez con tres parámetros x, y, y z de número de tipo de los que z es opcional. Si el parámetro z se hace pasar entonces la función devuelve el valor (x * y) + z; si z no se pasa devuelve el valor de (x * y) + 1.

Listado 5-11. dejar

// Definir una función que multiplica dos números a continuación, añade o bien el tercer número o 1 ExampleFunction = (x como el número, y como el número, z opcional como número) como el número =>

(X * y) + (si z = null then 1 else z), // Llame a la función y pasar 3 y 4 como parámetros QueryOutput = ExampleFunction (3,4) en QueryOutput

cada expresiones Ya hemos visto anteriormente, en la sección “Listas de filtrado”, un ejemplo de cómo una cada expresión se puede utilizar para definir una función sin nombre con un único parámetro para el uso con funciones como List.Select (). los cada expresión puede ser utilizado en cualquier lugar se requiere una declaración de la función, y es la abreviatura de una función que toma un solo parámetro, sin tipo que se nombra _. Listado 5-12 muestra un ejemplo de una declaración de función que utiliza una cada expresión.

Listado 5-12. dejar

// Definir una función que multiplica un número por 2 ExampleFunction = cada _ * 2, // Llame a la función y pasar 3 como parámetro QueryOutput = ExampleFunction (3) en QueryOutput En este ejemplo la expresión cada _ * 2 es directamente equivalente a la expresión (_) => _ * 2. El resultado de la consulta es el valor 6. En su mayoría, sin embargo, cada expresiones se utilizan cuando el paso de parámetros a otras funciones. Muchas de las funciones que no sean List.Select () tomar funciones como parámetros; un ejemplo ligeramente más complejo es Table.TransformColumns ()

cuya firma es: Table.TransformColumns (tabla como tabla, transformOperations como lista,

defaultTransformation opcional como función anulable, missingField opcional como el número anulable) Listado 5-13 muestra un ejemplo de cómo una cada expresión se puede utilizar con Table.TransformColumns () para multiplicar todos los valores en la columna de las ventas de la tabla mostrada en la figura 5-26 por dos.

139 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Listado 5-13. dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[name = "FruitSales"]} [Contenido], // multiplicar cada valor de la columna de ventas por 2 SalesTimesTwo = Table.TransformColumns (fuente, { "Ventas", cada uno _ * 2}) en SalesTimesTwo En este ejemplo, el primer parámetro pasa a Table.TransformColumns () es la tabla de origen, y el segundo parámetro es una lista en la que el primer

elemento es el nombre de la columna cuyos valores son para ser alterado y el segundo elemento es la función de aplicar a cada valor en la columna. los Table.TransformColu () función pasa cada valor en la columna de las ventas al _ parámetro de la función definida por cada _ * 2. La salida de esta consulta se muestra en la figura 5-30 .

Figura 5-30. Salida del Table.TransformColumns () función

Las consultas como funciones Así como la definición de las funciones dentro de las consultas, también es posible definir una consulta como una función. Hacer esto le permitirá utilizar esta función en cualquier otra consulta en el libro de trabajo, lo que significa que se puede compartir la lógica de negocio entre varias consultas. La razón por la que esto funciona es porque una función es un tipo de datos de consulta en el poder, al igual que los tipos de tabla, número o texto, por lo que su consulta puede devolver un valor de tipo de función.

Para definir una consulta que devuelve una función que tendrá que crear una nueva consulta utilizando la opción de consulta en blanco en la cinta de alimentación de consulta y luego usar el Editor avanzado para escribir el código M para su búsqueda. Listado 5-14 muestra un ejemplo de una consulta que devuelve una función que toma dos números y devuelve el valor de los multiplicó en conjunto más 1:

Listado 5-14. dejar

MyFunction = (x, y) => (x * y) + 1 en MyFunction Una vez que haya entrado en este código en el Editor avanzado y se hace clic en OK, verá la función que aparece como un paso en su consulta y una nueva pestaña aparecerá en el Editor de consultas: la pestaña de funciones, como se muestra en la figura 5-31 . En esta pestaña se encuentra el botón de función de invocación, que, si se hace clic en él, se le pedirá que introduzca valores para los parámetros de la función y llamar a la función (también hay un botón en el cuerpo de la ventana del Editor de consultas que hace lo mismo) -pero en este caso de que no desea hacer esto, por supuesto; que desea devolver la función en sí.

140 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

Figura 5-31. Una función que se muestra en el Editor de consultas

El nombre que le asigne esta consulta será el nombre de la función creada; en este caso el nombre de la consulta es MultiplyThenAddOne. Verá que tanto el Cargar a la hoja de y Carga de Modelo de Datos casillas de verificación están desactivadas debido a que una función no devuelve ningún dato hasta que se invoca; Ahora puede hacer clic en el Aplicar y cerrar botón y la salida del Editor de consultas.

Para utilizar esta función en otra consulta, se crea otra consulta como normal y se puede invocar la nueva función de la misma manera que lo haría recurrir a cualquier otra función. Listado 5-15 muestra el código M para una consulta que llama a la MultiplyThenAddOne () función dentro de una columna personalizada usando las ventas y columnas Unidades de la tabla mostrada en la figura 5-26 como parámetros. La salida se muestra en la figura 5-32 .

Listado 5-15. dejar

// Cargar los datos de la tabla de origen

Fuente = Excel.CurrentWorkbook () {[name = "FruitSales"]} [Contenido], // la función de llamada en una columna personalizada

InsertedCustom = Table.AddColumn (Fuente: "FunctionCallExample", cada MultiplyThenAddOne ([Ventas], [Unidades])) en

InsertedCustom

Figura 5-32. Resultado de una consulta con el MultiplyThenAddOne () función

141 www.it-ebooks.info

Capítulo 5 ■ Introducción a M La función también se puede invocar directamente desde el panel de libro consultas haciendo clic derecho en ella y seleccionando Invoke, como se muestra en la figura 5-33 . Al hacer esto, aparecerá un cuadro de diálogo le pide que introduzca parámetros de la función, y una vez que lo haya hecho una nueva consulta será creado que invoca las funciones con estos valores y devuelve el resultado.

La figura 5-33. Una función en el panel de libro consultas

dejar Las expresiones en la definición de funciones Más definiciones de funciones complejas requerirán más que una sola línea de código para su definición, por lo que tendrá que utilizar una dejar expresión dentro de la definición de función para permitir esto. Listado 5-16 muestra la función de la lista 5-15 reescrito para que las operaciones de multiplicar los dos parámetros y añadiendo luego 1 se llevan a cabo en dos etapas separadas en un interior dejar expresión.

Listado 5-16. dejar

// definir la función que devolverá el valor de la expresión let interior MyFunction = (x, y) => dejar

// multiplicar x e y Paso 1 = x * y, // Añadir uno al resultado Paso2 = Paso 1 + 1 en Paso 2 en

MyFunction 142 www.it-ebooks.info

Capítulo 5 ■ Introducción a M Recuerde que una dejar expresión es simplemente una manera de romper una operación en varios pasos y devolver un valor y que ese valor puede ser una función. Si se utiliza una dejar expresión dentro de una definición de función, el Editor de consultas sólo será capaz de mostrar un solo paso para toda la definición de la función y no podrá ver todo el código M para ello en la barra de fórmulas.

Funciones recursivas Una función recursiva es una función que llama a sí mismo dentro de su propia definición. Para evitar que la función que se está atrapado en un bucle infinito, donde se sigue llamando en sí para siempre, una función recursiva sólo debe llamarse a sí misma si se cumple una determinada condición.

Listado 5-17 muestra un ejemplo de una función que toma un único parámetro, y si ese parámetro es mayor que o igual a 100, entonces se devuelve el valor del parámetro, pero si es menos de 100 llama a la función de forma recursiva con el original valor de parámetro multiplica por 2.

Listado 5-17. dejar

// definir una función recursiva con un parámetro DoubleToOneHundred = (x) => // si el parámetro es mayor de cien, devuelva el parámetro si x> 100 entonces x

// de lo contrario llamar a la función de nuevo con el parámetro inicial // multiplicado por dos más @DoubleToOneHundred (x * 2) //, llamar a la función con el valor 4 // la salida es 128 FunctionOutput = DoubleToOneHundred (4) en

FunctionOutput Desde el punto de vista del lenguaje de la única nueva cosa a notar aquí es que cuando el DoubleToOneHundred () función llama a sí mismo de forma recursiva, tiene que utilizar el @ operador antes de su nombre de la siguiente manera: @ DoubleToOneHundred ().

Funciones importadas de fuentes de datos Ciertos tipos de objetos en fuentes de datos externas, como las funciones definidas por el usuario en una base de datos SQL Server y operaciones de servicio OData, son tratadas como funciones al importarlos en el poder de consulta. Una vez que hayan sido importados se comportan como cualquier otra función de alimentación de consulta, aunque por supuesto no existe una definición M que se puede ver.

El trabajo con los Servicios Web Un tipo importante de origen de datos que tendrá que utilizar para trabajar con M es servicios web. Poder de consulta es capaz de llamar a un servicio web REST utilizando el Web.Contents () función, y si bien no es un ejemplo práctico de cómo utilizar esta función para recuperar datos del servicio web de Bing Maps en el capítulo 8, hay algunas características de esta función que vale la pena resaltar.

143 www.it-ebooks.info

Capítulo 5 ■ Introducción a M

los Web.Contents () función toma dos parámetros. El primer parámetro es la dirección URL del servicio web que desea llamar, mientras que el segundo parámetro, opcional lleva un registro que contiene propiedades adicionales. Considere un servicio web imaginaria que se puede llamar desde la siguiente URL:

http://www.mywebservice.com/GetData Este servicio web podría ser llamado desde la energía de consulta utilizando la expresión:

Web.Contents (" http://www.mywebservice.com/GetData ") Normalmente, un servicio web devuelve datos en forma de un documento XML o JSON y Poder de consulta reconocerá automáticamente el formato utilizado y abrirlo para que pueda ver el contenido.

Muchos de los servicios web también requieren que añadir parámetros a la URL; algo como esto:

http://www.mywebservice.com/GetData?search=somedata&results=10 Mientras que usted podría construir la URL completa a sí mismo y pasarlo al primer parámetro de Web.Contents (), Hay una manera más fácil. En el segundo parámetro de Web.Contents () se puede utilizar el Consulta terreno para la construcción de una cadena de consulta como la siguiente:

Web.Contents (" http://www.mywebservice.com/GetData ", [Consulta = [# "buscar"= "somedata", # "resultados"= "10"]]) Si usted necesita para pasar las cabeceras HTTP personalizados, puede hacerlo de forma similar, utilizando el encabezados campo:

Web.Contents (" http://www.mywebservice.com/GetData ", [Consulta = [# "buscar"= "somedata", # "resultados"= "10"], encabezados = [# "HeaderName"= "ValueToPass"]])

En algunos casos, un servicio web será necesario que para pasar una clave o ficha a través de un encabezado HTTP personalizado. Mientras que usted puede hacer esto, le tomaría forzar a codificar el valor de esta clave o ficha en el código de la consulta, lo cual no sería seguro. En su lugar puede utilizar el campo ApiKeyName para especificar el nombre del encabezado HTTP personalizado que contiene la clave o ficha, así:

Web.Contents (" http://www.mywebservice.com/GetData ", [Consulta = [# "buscar"= "somedata", # "resultados"= "10"], encabezados = [# "HeaderName"= "ValueToPass"], ApiKeyName = "APIToken"])

Al hacer esto, la primera vez que la consulta se ejecuta el diálogo de credenciales de alimentación de consulta aparecerá y le pedirá que ingrese el valor de la clave o ficha. Después de esto la clave o ficha serán almacenados en el almacén de credenciales seguras de alimentación de consulta, tal como se describe en el capítulo 2.

Por último, aunque Web.Contents () genera una OBTENER solicitud por defecto, usted puede hacer que genere una ENVIAR solicitar lugar mediante la especificación de la Contenido campo, como en este ejemplo:

Web.Contents (" http://www.mywebservice.com/SendData ", [Contenido = Text.ToBinary ( "Contenido de texto para enviar en la solicitud POST"])

144 www.it-ebooks.info

Capítulo 5 ■ Introducción a M En este caso el valor de la Contenido campo contiene los datos binarios que se va a utilizar como el contenido de la ENVIAR

solicitud; la Text.ToBinary () función debe ser usado para convertir el valor de texto para ser enviado a un valor de tipo Binario.

plegable consulta Algo que no es inmediatamente evidente sobre el poder de consulta es donde todo el trabajo duro que no tiene lugar realmente: cuando una consulta se conecta a una fuente de datos y se aplica una serie de transformaciones a los datos, siempre que sea posible de energía de consulta tratará de empujar tan gran parte del trabajo como sea posible volver a la fuente de datos, donde (o eso se supone) se puede hacer de manera más eficiente. Este comportamiento se denomina plegado consulta. Si el poder de consulta es capaz de hacer esto o no depende del tipo de fuente de datos que está utilizando y el tipo de transformaciones en su consulta. Con una base de datos relacional como SQL Server, Power consultas intentará traducir toda la lógica en la consulta para una instrucción SQL SELECT, y con una fuente de datos OData intentará traducir toda la lógica en una sola dirección URL OData. Sin embargo, para las fuentes de datos como archivos de texto, la energía de consulta no tiene más remedio que cargar todos los datos en su propio motor y realizar todas las transformaciones en la consulta interna.

Como es fácil adivinar, si el poder de consulta puede empujar una operación de vuelta al origen de datos puede tener un gran impacto en el rendimiento de la operación. Será mucho más eficiente para SQL Server para agregar datos de una tabla grande que para la alimentación de consulta para descargar todos los datos al escritorio y realizar la agregación allí. En general, por tanto, plegado de consulta es una buena cosa y usted querrá que suceda en sus consultas. Por otro lado, puede haber ocasiones en las que la energía de consulta es la opción más eficiente, o cuando la fuente de datos cambia con tanta frecuencia que hay que evitar el plegado consulta se llevara a cabo para que tenga una visión estable de datos para trabajar con ellos.

Si plegado consulta es una buena cosa, entonces la siguiente pregunta obvia es: ¿cómo se construye su consulta para asegurarse de plegado consulta pasa? Desafortunadamente, no hay respuestas fáciles. En el momento de la escritura de plegado consulta se limita a cuatro tipos de fuentes de datos: bases de datos relacionales, fuentes de datos OData, Exchange y Active Directory. Esto podría cambiar en el futuro, sin embargo. Por otra parte, hay un montón de otros factores que determinan si plegado consulta puede llevarse a cabo, tales como ciertos tipos de transformación, y de nuevo estos factores es casi seguro que cambiar en el futuro. Como resultado sólo recomendaciones muy generales se pueden dar sobre cómo asegurar consulta plegado tiene lugar; si el rendimiento es un problema,

Monitoreo de Peticiones plegable en SQL Server No hay ninguna indicación en la interfaz de usuario de energía Consulta de si plegado consulta se lleva a cabo o no, por desgracia, hay que utilizar otras herramientas para supervisar la comunicación entre el poder de consulta y el origen de datos. Si está utilizando SQL Server como una fuente de datos, se puede ver con bastante facilidad que tiene lugar mediante la ejecución de una traza de SQL Server, mientras que su consulta se ejecuta. La consulta en el Listado 5-18 se conecta a la mesa DimProductCategory en la base de datos Adventure Works DW en SQL Server, lo filtra de modo que sólo las filas donde la columna de la ProductCategoryKey es inferior a 4, y luego se cuenta el número de filas después de que el filtro haya tenido lugar .

Listado 5-18. dejar

// conectarse a SQL Server

Fuente = Sql.Database ( "localhost", "Adventure Works DW"), // conectarse a la mesa DimProductCategory dbo_DimProductCategory = Fuente {[Esquema = "dbo", pos = "DimProductCategory"]} [Datos], // filtrar la tabla donde ProductCategoryKey es inferior a 4 FilteredRows = Table.SelectRows (dbo_DimProductCategory, cada uno [ProductCategoryKey] = 50), // rangos de tiempo agregado GroupedRows = Table.Group (

InsertedCustom, { "Above50"}, {{ "Inicio", cada List.Min ([hora]), el tiempo de tipo}, { "EndTemp", cada List.Max ([hora]), el tiempo de tipo}, { "Minutos", cada table.rowCount (_), el número de modelo}}, GroupKind.Local),

// Añadir un minuto para los valores de la columna EndTemp InsertedCustom1 = Table.AddColumn (GroupedRows, "Fin", cada uno [EndTemp] + #duration (0,0,1,0)), // Eliminar la columna de la EndTemp RemovedColumns = Table.RemoveColumns (InsertedCustom1, { "EndTemp"}), // Mover la columna de la Final en el medio de inicio y Actas

ReorderedColumns = Table.ReorderColumns (RemovedColumns, { "Above50", "Inicio", "Fin", "Minutos"}), // filtro para mostrar sólo los rangos de precio de las acciones, donde es mayor o igual a 50 FilteredRows = Table.SelectRows ( ReorderedColumns, cada uno ([Above50] = true)), // Ordenar por Minutos en orden descendente

SortedRows = Table.Sort (FilteredRows, {{ "Minutos", Order.Descending}}) //, Mantenga primera fila de la tabla KeptFirstRows = Table.FirstN (SortedRows, 1) en KeptFirstRows

Trabajo con datos de la Web Web-raspado pronóstico del tiempo de Datos

Objetivo Figura 8-60 muestra una tabla de Excel que contiene una lista de algunos de los palacios reales en el Reino Unido, junto con sus códigos postales (un código postal en el Reino Unido es como un código postal en los Estados Unidos, que identifica a un pequeño número de direcciones en la misma geográfica zona).

230 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-60. Una tabla que contiene palacios y sus códigos postales

Puede buscar el tiempo para cualquier código postal en el Reino Unido en el sitio web del tiempo de la BBC. Por ejemplo, el tiempo para la 6HF de código postal HP6 se ​puede encontrar tecleando el código de destino en el cuadro Buscar un pronóstico en www.bbc.co.uk/ tiempo / ; la salida es como se muestra en la figura 8-61 .

La figura 8-61. El pronóstico del tiempo de la BBC para el código postal HP6 6HF Fuente - www.bbc.co.uk/weather/hp6 - © 2014 BBC La dirección de esta página contiene la primera parte del código postal, antes de que el espacio, por ejemplo, www.bbc.co.uk/ tiempo / HP6 . Los caracteres después de la última barra representan el área de código postal.

El objetivo de este ejercicio es para raspar la previsión meteorológica para todos los palacios se muestra en la figura 8-60 y combinarlos en una sola tabla.

■ Nota si el formato de la página web o las URL que se utilizan en esta sección los cambios en el futuro, entonces la consulta en esta sección ya no puede trabajar.

231

www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

Pasos 1.

Este ejercicio requerirá la creación de dos consultas de energía de consulta. La primera consulta será una función que se puede utilizar para raspar los datos de previsión meteorológica de la página web se muestra en la figura 8-61 . Haga clic en el botón De Web en el tabulador de consulta de la cinta de Excel e introduzca la URL http://www.bbc.co.uk/weather/hp6 en el cuadro de diálogo De la Web, como se muestra en la figura 8-62 .

La figura 8-62. El De diálogo Web 2. Al hacer clic en OK, el panel del navegador en el lado derecho de la pantalla mostrará el nombres de todas las tablas de la página Web que se pueden identificar, como se muestra en la figura 8-63 . Haga clic pronóstico diario para seleccionarlo y luego haga clic en el botón Editar en la parte inferior del panel del navegador. El Editor de consultas se abrirá.

La figura 8-63. Tablas de una página web

3. Cuando se abre el Editor de consultas, se mostrará el pronóstico para los próximos cinco días para el HP6 área de código postal, como se muestra en la figura 8-64 .

232 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-64. pronóstico de cinco días para el área de código postal HP6

4.

Ahora se necesita para convertir esta consulta en una función que puede devolver el pronóstico para cualquier área de código postal. Para ello, haga clic en el botón Editor avanzada en la pestaña Ver en la barra de herramientas del Editor de consultas para abrir el Editor avanzado. En este punto el código M para la consulta será como se muestra en el listado 8-10.

Listado 8-10. dejar

Fuente = Web.Page (Web.Contents ( "http://www.bbc.co.uk/weather/hp6")), Data0 = Fuente {0} [Datos], ChangedType = Table.TransformColumnTypes (

Data0, {{ "Día", el tipo de texto}, { "Tiempo", el tipo de texto}, { "Temperatura día como máximo", el tipo de texto}, { "Temperatura noche mínimo", el tipo de texto}, { "dirección del viento y velocidad", forma de texto}})

en ChangedType 5. Editar el código como se muestra en el Listado 8-11 (los cambios en el código original están en letra negrita).

Listado 8-11. dejar

GetWeather = (Postcode1 como texto) como tabla => dejar

Fuente = Web.Page (Web.Contents ( "http://www.bbc.co.uk/weather/" & Postcode1)), Data0 = Fuente {0} [Datos], ChangedType = Table.TransformColumnTypes (

Data0, {{ "Día", el tipo de texto}, { "Tiempo", el tipo de texto}, { "Temperatura día como máximo", el tipo de texto}, { "Temperatura noche mínimo", el tipo de texto}, { "dirección del viento y velocidad", forma de texto}})

en ChangedType en

GetWeather

233 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

6.

En este punto el Editor de consultas debe mostrar el nombre de la nueva función y sus parámetros, como se muestra en la figura 8-65 . Cambie el nombre del GetWeather consulta.

La figura 8-65. detalles de función que aparecen en el Editor de consultas

7. Desactive la carga a la caja de la hoja de trabajo y la carga de cajas de Modelo de Datos en la configuración de carga, a continuación, haga clic en el botón Cerrar Aplicar y en la barra de herramientas del Editor de consultas para cerrar el Editor de consultas.

8.

En el tabulador de consulta de la cinta de Excel, haga clic en el botón Combinar rápido y luego, en el cuadro de diálogo Fast Combinar, haga clic en el botón Activar. Esto detendrá Poder consulta desde que le pide la configuración de privacidad de datos más adelante.

9. Ahora haga clic dentro de la tabla que contiene la información sobre los palacios dentro del Excel

hoja de cálculo 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 una nueva consulta será creado mostrando los datos de esa tabla. 10. Haga clic en la columna Código postal en la tabla, y en el menú del botón derecho seleccione Dividir

Columna ➤ Por Delimitador ..., como se muestra en la figura 8-66 .

La figura 8-66. La Columna de Split ➤ Por Delimter ... opción de menú del botón derecho

11.

En el Dividir una columna por delimitador diálogo, seleccione espacio en el menú desplegable, como se muestra en la figura 8-67 , A continuación, haga clic en Aceptar. La columna Código postal se dividirá en dos columnas llamadas Postcode.1 y Postcode.2; la columna de la Postcode.1 contendrá el área de código postal.

234 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-67. los Dividir una columna por delimitador diálogo

12. Haga clic derecho en la columna de la Postcode.2 y seleccione Eliminar en el menú del botón derecho para eliminar

esta columna de la tabla. La salida será como se muestra en la figura 8-68 .

La figura 8-68. La tabla con las áreas de código columna puesto que muestra Postcode.1 13.

Las áreas de código postal ahora necesitan ser transformadas en minúsculas. Para ello, haga clic en la columna y seleccione Transformar Postcode.1 ➤ minúsculas en el menú del botón derecho como se muestra en la figura 8-69 .

235 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-69. La transformación de la columna de la Postcode.1 a minúsculas

14.

Ahora tiene que llamar a la función que ha creado anteriormente para cada fila de esta tabla. Haga clic en el botón Insertar columna personalizada en la ficha Insertar de la barra de herramientas del Editor de consultas, llame a su nuevo pronóstico columna personalizada, y el uso de la siguiente expresión:

GetWeather ([Postcode.1]) Haga clic en OK para cerrar el cuadro de diálogo Insertar columna personalizada. La salida será como se muestra en la figura 8-70 .

La figura 8-70. La columna personalizada previsión que contiene valores de la tabla

15. Haga clic en el icono Expandir en la cabecera de la columna de Previsión y luego, en el menú flotante, haga clic OK para expandir todas las columnas de los valores de la tabla de esa columna (como se muestra en la figura 8-71 ).

236 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-71. La expansión de los valores de la tabla en la columna de la Previsión

dieciséis. Una vez hecho esto, podrás ver los datos de previsión del tiempo para cada muestra en palacio

la mesa. La salida final de la consulta será como se muestra en la figura 8-72 .

La figura 8-72. El resultado final de la consulta el pronóstico del tiempo

Código Listado 8-11 ya da el código completo para el M GetWeather () función. Listado 8-12 da el código M para la consulta que llama a esta función para cada uno de los palacios de la tabla de Excel.

237 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

Listado 8-12. dejar // Cargar los datos de hoja de cálculo de Excel

Fuente = Excel.CurrentWorkbook () {[name = "Palacios"]} [Contenido], // códigos postales dividida por el espacio en dos columnas SplitColumnDelimiter = Table.SplitColumn ( Fuente, "Código postal",

Splitter.SplitTextByDelimiter (" "), {" Postcode.1" , "Postcode.2"}), // Establecer el tipo de cada columna a texto ChangedType = Table.TransformColumnTypes (

SplitColumnDelimiter, {{ "Postcode.1", el tipo de texto}, { "Postcode.2", el tipo de texto}}), // eliminar la columna de la Postcode.2

RemovedColumns = Table.RemoveColumns (ChangedType, { "Postcode.2"}), // Cambiar los valores en Postcode.1 a minúsculas

TransformedColumn = Table.TransformColumns (RemovedColumns, {{ "Postcode.1", Text.Lower}}), // Llamar a la función GetWeather () para cada fila InsertedCustom = Table.AddColumn ( TransformedColumn, "Previsión", cada GetWeather ([Postcode.1])), // Ampliar los valores de la tabla devueltos por el GetWeather () Función # "Ampliar el pronóstico" = Table.ExpandTableColumn (

InsertedCustom, "Previsión", { "Día", "Tiempo", "Día Temperatura Máxima", "Temperatura mínima de Noche", "Dirección y velocidad del viento"}, { "Forecast.Day", "Forecast.Weather", "Día Forecast.Maximum temperatura", "Temperatura Forecast.Minimum Noche", "Dirección y velocidad Forecast.Wind"})

en # "Ampliar el pronóstico"

Encontrar la distancia de conducción entre dos puntos utilizando el servicio web de Bing Mapas de Ruta

Objetivo Figura 8-73 muestra una tabla en una hoja de trabajo que contiene dos direcciones. El objetivo de esta sección es encontrar la longitud (en kilómetros) de la ruta de conducción más corta entre estas dos direcciones mediante una llamada al servicio de ruta web de Bing Maps.

La figura 8-73. El cuadro que contiene las dos direcciones para encontrar la distancia entre

238 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder



Nota el servicio web de Bing Maps usa en esta sección es de uso gratuito durante 90 días para fines de evaluación; para la mayoría de otros usos comerciales que

tendrá que comprar una licencia. los términos y condiciones de este servicio se pueden encontrar en

www.microsoft.com/maps/product/terms.html .

Pasos 1.

Para utilizar cualquiera de los servicios web de Bing Maps, es necesario obtener una clave. Puede obtener una gratis por ir a https://www.bingmapsportal.com .

2.

En el tabulador de consulta de la cinta de Excel, haga clic en el botón Combinar rápido; a continuación, en el cuadro de diálogo Fast Combinar, haga clic en el botón Activar. Esto detendrá Poder consulta desde que le pide la configuración de privacidad de datos más adelante.

3. Haga clic dentro de la tabla mostrada en la figura 8-73 y luego haga clic en el botón Tabla de De

el tabulador de consulta de la cinta. El Editor de consultas se abrirá y se creará una nueva consulta.

4. Haga clic en el ƒx botón situado junto a la barra de fórmulas en el Editor de consultas para agregar un nuevo paso para la

consulta. Eliminar todo en la barra de fórmulas después del signo igual y copiar en la siguiente expresión:

(Web.Contents "Http://dev.virtualearth.net/REST/V1/Routes/Driving", [Consulta = [# "Wp.0" = Fuente {0} [Inicio], # "WP.1" = Fuente {0} [Fin], # "Tecla" =" InsertYourBingMapsKeyHere "]])

Insertar su propia clave de Bing Maps que se indique. Esta expresión utiliza el Web.Contents () la función para llamar al servicio de rutas web de Bing Maps. 5. Una vez hecho esto, el Editor de consultas mostrará un icono de documento como se ve en

Figura 8-74 . Este es el documento JSON que devuelve el servicio web que contiene la información que necesita. Haga doble clic en él.

La figura 8-74. El documento JSON devuelto por el servicio Web 6. Una vez que haya hecho doble clic sobre el documento, verá los datos dentro de ella (como se muestra

En figura 8-75 ). Haga clic en la lista de valores en la fila resourceSets que se indique.

239

www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-75. Contenido del documento JSON

7. Al hacer clic en la lista de valores mostrará una lista con un solo valor, Record. Haga clic en el registro de valor

donde se indica en la figura 8-76 .

La figura 8-76. Navegación por el documento JSON

8.

El contenido de otro registro aparecerá, y hay que hacer clic en el valor de la lista en la fila de los recursos que se indique en la figura 8-77 .

La figura 8-77. Aún navegar a través del documento JSON

9.

Aparecerá otra lista que contiene un único valor Record, exactamente de la misma como se muestra en la figura 8-76 . Una vez más, haga clic en el valor de Registro.

10.

Va ahora, por fin, ha llegado a los datos que necesita, como se muestra en la figura 8-78 . Haga clic en la fila travelDistance y seleccione la profundización desde el menú del botón derecho.

240 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

La figura 8-78. Aislando el valor de la distancia de viaje

11. La salida final de la consulta será como se muestra en la figura 8-79 . El valor mostrado es la distancia en kilómetros entre las dos direcciones en la tabla de origen.

La figura 8-79. El resultado final de la consulta

Código El código M para la consulta se da en el listado 8-13. Listado 8-13. dejar

// datos de origen de carga de hoja de Excel Fuente = Excel.CurrentWorkbook () {[Name = "RouteData"]} [Contenido], // Llamar a Bing Maps ruta servicio web Custom1 = Web.Contents (

"Http://dev.virtualearth.net/REST/V1/Routes/Driving", [Consulta = [# "Wp.0" = Fuente {0} [Inicio], # "WP.1" = Fuente {0} [Fin],

# "Tecla" =" InsertYourBingMapsKeyHere "]]), // respuesta Tratar como documento JSON ImportedJSON = Json.Document (Custom1),

241 www.it-ebooks.info

Capítulo 8 ■ Recetas de consulta poder

// Navegar a través del documento JSON para encontrar el valor de la distancia resourceSets = ImportedJSON [resourceSets], resourceSets1 = resourceSets {0}, recursos resourceSets1 = [recursos], recursos1 = recursos {0},

travelDistance = recursos1 [travelDistance] en distancia de viaje

Resumen En este capítulo se ha visto una serie de ejemplos de cómo resolver problemas con la energía de consulta. Esperemos que ahora será capaz de tomar estos ejemplos, aprender de ellos, y ser capaz de utilizar la energía de consulta para cargar sus propios datos en Excel. Como se habrá dado cuenta a estas alturas, la energía de consulta es una herramienta muy flexible y potente que es central en la nueva visión de Microsoft de Business Intelligence y Analytics. ¡Diviértete con eso!

242 www.it-ebooks.info

Índice



función RANK (), 200-201

ABC

Cálculos, Consulta de energía

clientes distintos

pasos, 199 Servicios en la nube, 2

de valores separados por comas (CSV), 27

implementaciones de código, 205

fecha y cliente duplicar, 204 grupo por la configuración de diálogo, 204



Modelo de datos de Excel

objetivo, 202 salida, 205

ventajas, 106-107 cambios de ruptura, 110 definición, 103

barra de herramientas del Editor de consultas, 203

eliminar duplicados botón, 203 pasos, 203 gran total

Las conexiones existentes de diálogo, 104

modelo de datos de libro existente, 104 Ventana pivotante de potencia, 105-106

implementaciones de código, 193

hoja de Excel

diálogo columna personalizada, 192

de diálogo de datos, 103

salida final, 193

pestaña de datos, 102

grupo por la configuración de diálogo, 190

Las conexiones existentes de diálogo, 102

objetivo, 189

creación de la tabla, 101

editor de consultas, 191

editor de consultas

datos fuente, 191

cinta de Excel, 100

crecimiento de las ventas

ajuste de la carga, 99

implementaciones de código, 198

diálogo de opciones, 100

definición de columna personalizada, 197

botón de actualización

inserte diálogo columna personalizada, 194

propiedades de conexión de diálogo, 113

fusionar de diálogo, 195

Excel de diálogo Conexiones del libro, 111

fusionar operación, 196 objetivo, 193 columna PreviousDateSales, 197 columna de ventas, 196 las cifras de crecimiento de ventas, 198

re

destinos de datos

barra de herramientas del Editor de consultas, 112

panel de consultas de libro, 111 (relaciones de la tabla ver Tabla recuperación relaciones) de datos, 189

Bing Maps de servicios vía web

pasos, 194 filas atadas

implementaciones de código, 241

salida final, 241 documento JSON, 239-240 objetivo, 238

implementaciones de código, 202

mesa de salida final, 201 datos de entrada, 199

pasos, 239

objetivo, 199

valor de la distancia del viaje, 241

243 www.it-ebooks.info



índice

Recuperación de datos ( cont.)

fuentes de datos OData

OData entidad, 47

Web raspado de datos previsión del tiempo

servicio web, 46

implementaciones de código, 237 delimitador de diálogo, 235

principio de funcionamiento, 46

columna de pronóstico, 237

Búsqueda de datos públicos, 38-39

HP6 de código postal, 233

hace referencia a las consultas enteras, 43

objetivo, 230

ficha de consulta, 45

códigos postales, 231, 235

botón de libro, 44 panel de consultas de libro, 44

Editor de consultas, 234 la columna de división, 234

bases de datos relacionales

pasos, 232

conectando con el servidor SQL, 17

valores de la tabla, 236

botón de base de datos, 18

de diálogo web, 232

DimDate mesa, 23 tabla DW, 22 expandir diálogo / agregado, 24-25 ampliar icono, 23 FactInternetSales mesa, 25

página web, 232 Fuentes de datos los datos de Active Directory, 53

valor archivos separados por comas (CSV)

y páginas de códigos, 60-61

la conexión de base de datos Microsoft SQL, 19

definición, 27

panel del navegador, 21

editor de consultas, 28

panel de navegación del Editor de consultas, 23

botones de texto, 27

objeto de registro, 26

datos mediante la consulta de potencia

funciones escalares / funciones con valores de tabla, 21

diálogo de autenticación de SQL Server, 20

búsqueda en línea, 38 configuración de diálogo, 58

Duplicación, consultas 45 archivos de Excel que trabajan

tipos, 26 servicios web RESTful, 38 la reutilización de las fuentes de datos recientes, 56

caja desplegable, 57

panel del navegador, 32

fuentes de diálogo, 57

los datos de ventas, 31

libro de Excel

SAP BusinessObjects

tabla de Excel, 34

modificar editor de consultas, 56

Excel Web App, 47

panel del navegador, 55

universo, 55

feed OData, 48 editor de consultas, 35

pestaña de búsqueda, 42

salestable, 34

consultas compartidas y datos de la organización

Facebook diálogo de conexión, 54

resultados de la búsqueda en línea, 41

botón compartida, 41

de mesa y registrar los enlaces, 54

filtros, 43 carpetas y varios archivos, 32 HDFS y HDInsight de trabajo, 52

panel de consultas compartidas, 42

sesión botón, 40 Las listas de SharePoint

panel del navegador, 49 editor de consultas, 49

importancia de la configuración regional, 58

caja desplegable, 59 Inglés local, 60

archivos de texto de trabajo, 28

Francés (Francia) local, 60

página web, 35

trabajando, 48

panel del navegador, 37

probando diferentes lugares, 59 los valores individuales de las consultas, 45

editor de consultas, 38

tabla de datos, 36

archivos JSON de trabajo editor de consultas, 31

Ventanas de almacenamiento burbuja azul y mesa

el uso de los servicios web, 30

la gestión de credenciales, 57

de trabajo de almacenamiento, 52

Windows Marketplace azul

microsoft Exchange

conjuntos de datos, 50-51

panel del navegador, 53

conjuntos de datos en panel del navegador, 52

editor de consultas, 54

varios archivos de texto, 33

cuenta de Windows Azure mercado clave, 51

nueva opción de consulta, 45

trabajando, 49

244 www.it-ebooks.info

■ índice columna de coste de envío, 98

archivos XML datos de los empleados hacia abajo perforado, 30

ordenar una tabla, 79

datos de los empleados expandidas, 30

dividir columnas

editor de consultas, 29

botón delimitador, 67

archivo XML típica, 29

delimitador de diálogo, 67

diálogo posición, 68 dividir y fusionado, 66

transformación de datos, 63 la agregación de valores

caja desplegable, 89

transposición de mesa, 93

grupo de diálogo, 88

el texto se transforma, 81

Editor de consultas, 89-90

unpivoting columnas a filas, 91 persona de ventas, 93

incorporado en las columnas personalizadas

UNPIVOT botón, 92

estadísticas cuadro desplegable, 96

los datos no girados, 92

valores a través de las columnas, 96

Tipo de cambio, 72

palabra transformar, 82

crear columnas personalizadas, 94

datos XML almacenados en celdas de la tabla, 82

con columnas personalizadas

cálculos M, 97 transformadas de fecha / hora / duración, 84



caja desplegable, 83

Excel.CurrentWorkbook (), 119

cuadro desplegable llenar

Modelo de datos de Excel, 21

mi

Los valores de texto vacíos, 87

ventajas, 106-107 definición, 103

enviar a la ciudad y el buque a otro, 87

Las conexiones existentes de diálogo, 104

valores de los países, 86

filtrando las filas con errores, 79

modelo de datos de libro existente, 104

filas de filtrado

Ventana pivotante de potencia, 105-106

cuadro de auto-filtro, 72-73

diálogo filas de filtro, 74 menú de iconos, 77



filtros número, 73

FactInternetSales mesa, 25

número filtra el menú, 74

Barra de formulas, 8

barra de herramientas del editor de consultas, 77

funciones definición, 138 expresiones, 139, 142 función MultiplyThenAddOne (), 141

por rango, 76 eliminar de diálogo filas alternas, 78 filtros de texto, 74

F, G

opción de configuración regional, 72

valor del parámetro, 139

fusionar columnas

función de las consultas, 140

diálogo, 70

función recursiva, 143 Table.TransformColumns (), 140

opción y botón, 69 barra de herramientas del Editor de consultas, 70

columnas móviles, 66

nombrar columnas Columnas botones, 66

• H, I, J, K Hadoop Distributed File System (HDFS), 52

paso fuente, sesenta y cinco

número transforma, 83

consultas y pasos, 63 ChangedType, 64

• L Listas, registros y tablas

FirstRowAsHeader, sesenta y cinco

datos de la tabla de áridos, 127

Fuente paso, 64 Quitar, columnas 66

tabla de ventas, 130

Table.Group () de salida, 129, 131

la eliminación de valores duplicados, 78

Table.rowCount (), 128

sustituir los valores de botón, 80

lista de valores agregados, 125

sustituir los valores de diálogo, 81

definición, 124 DimDate mesa, 135

ajuste de la columna de tipo de datos, 70

tipos de datos, 71

listas de filtrado, 125

245 www.it-ebooks.info

■ índice

Listas, registros y tablas ( cont.) tablas de filtrado, 132 Lista funcional, 124 pivotante y unpivoting tablas, 133 claves primarias y externas, 135

hoja de Excel, 149 combinar la rápida opción, 156

Fórmula Firewall permisos de edición, 155

error, 156

listas de registros, 126

pronta consulta de base de datos nativa, 155

instrucción SELECT, 154

hace referencia a los campos de registros, 137

botón de fusionar

hace referencia a las listas de artículos, 136 hace referencia a las tablas de filas, 136

ApplesProfit mesa, 162-163

hace referencia a tablas de valores, 137

diálogo, 162

Lista de clasificación, 125

filas no coincidentes, 164

tablas de clasificación, 131

barra de herramientas del Editor de consultas, 164

función table.column (), 134

cinta, 161 función Table.AddJoinColumn (), 165 función Table.Join (), 165

creación de tablas, 127 trabajando, 124

función Table.NestedJoin (), 164-165



consulta con parámetros

METRO

fuentes de datos, 150

filtros, 150-152 ProductQuery, 151

M idioma ventana de editor avanzado, 116 creación de consultas en blanco, 117

conceptos de, 118 expresiones, valores y permiten declaraciones, 118



barra de formulas, 115

panel del navegador, 8

funciones ( ver Funciones) encabezado HTTP, 144 listas, registros y tablas ( ver Listas, registros,



y tablas) consulta plegable ( ver plegado de consulta) de la biblioteca estándar, 119

NO

PAG

BI poder servicios en la nube, 2 Los complementos de Excel, 1

función table.selectrows (), 120 tipos, 120

aplicación de BI móvil, 4 Mapa de potencia, 3

comentarios, 121

Pivote de potencia y Excel 2013 Modelo de Datos, 2-3

lógica condicional, 123

Potencia para realizar consultas, 2

fecha, fecha y hora, DateTimeZone, o

Power View, 3 SharePoint, 3 proceso de trabajo, 4-5

duración, 121 errores de captura, 122 servicios web de trabajo, 143 aplicación de BI móvil, 4

Catálogo de potencia de BI de datos, 167

fuentes de datos

consultas múltiples

anexar consultas diálogo, 159-160

salida, 160

pestaña de fuentes de datos, 175

metadatos, 176-177 Steward datos cálculo / transformación, 180

ventana Editor de consultas, 159

consultas de certificados, 178-179

cinta, 158 función Table.Combine (), 160-161

funciones, 180

interfaz de usuario, 158

Tareas, 177-178

libros de Excel

la configuración de privacidad de datos

Centro de administración, 181, 185

DimDate mesa, 152

configuración, 184

niveles de diálogo, 153

diálogo de conexiones, 182

Organizativo, 154 Privado, 154 Público, 154

datos de opción de actualización, 186-187

Editor de consultas, 153

permitiendo, 181

fuentes de datos, 180 Asistente de origen de datos, 183

246 www.it-ebooks.info

■ índice

consultas de intercambio

Active Directory grupos de seguridad, 171



pestaña de análisis, 174

libros de Excel, 171

las relaciones entre tablas

fuentes de datos, 110

Mi sitio de alimentación de BI, 173-174

clave externa, 108

Potencia del enlace sitio de BI, 173

panel del navegador, 109

ficha Consulta poder, 168 Compartir diálogo Consulta, 170

Sesión de diálogo, 168

updation, 172 URL, 170

Pivote ventana de energía, 110

Table.UnPivotOtherColumns (), 133 tabla de transformación, 189 tabla de fechas

implementaciones de código, 222

panel de libro de consultas, 169

expresión de columna personalizada, 221

Mapa de potencia, 3

fechas a años de conversión, 220

consulta de potencia, 1

mesa de diálogo, 219

agregación / filtro, 14

función List.Dates (), 218 objetivo, 217 salida, 222

automatizar las cargas de datos, 14

botón CSV, 7 fuentes de datos, 14

pasos, 218

editar una consulta existente

nuevos, perdidos, y los clientes que regresan

volar fuera del menú, 11

anexar operación, 211

resultado, 13 cuadro de diálogo filas superiores, 12

implementaciones de código, 216

panel de libro de consultas, 10

expresión de columna personalizada, 215

libros de Excel, 15 instalación, 6

clientes distintos, 212 salida final, 216 columna lastyear, 215

modelo de licencia, 5-6

tabla resultante de la fusión, 212

menú de cinta de Excel, 7

Microsoft Excel, 13

fusión, 213

El poder de BI ( ver BI potencia) la

filas no están en blanco, 214

objetivo, 210

ventana del Editor de consultas, 8

pasos, 211

consulta. Desafortunadamente, 6

columna de año, 214

y transformaciones la creación de cálculos, 14

mesa de una sola columna a una tabla de varias columnas

implementaciones de código, 210

hoja de cálculo, 9

columna CustomerID 209

Power View, 3



T, U, V, W, X, Y, Z

función Table.Distinct (), 135

columna de índice añadió, 207

objetivo, 205

Q

mesa pivotada, 209 fila de identificación de tipo, 208

barra de herramientas del Editor de consultas, 8

pasos, 206

plegado consulta

precio de mercado

monitoreo de consulta en el servidor SQL, 145

implementaciones de código, 230

operaciones

datos, 224

características avanzadas, 148

filtración, 228 salida final, 229

sentencias SQL personalizadas, 147

eliminar filas con errores, 147

grupo de diálogo, 226

evitar que el código, 146 panel Configuración de la consulta, 8

mantener el diálogo filas superior, 229



la clasificación por orden descendente, 229

agrupación local, 227 objetivo, 223

R, S

pasos, 224

panel de resultados, 8

247 www.it-ebooks.info

Consulta de energía para la fuente de BI y Excel

Chris Webb

www.it-ebooks.info

Consulta de energía para la fuente de BI y Excel

Copyright © 2014 por Chris Webb Esta obra está sujeta a derechos de autor. Todos los derechos están reservados por el editor, si la totalidad o parte del material se refiere, en concreto los derechos de traducción, reimpresión, la reutilización de las ilustraciones, la recitación, la radiodifusión, la reproducción en microfilm o en cualquier otra forma física, y transmisión o almacenamiento de información y la recuperación, la adaptación electrónico, software informático, o por metodología similar o no conocido actualmente o desarrollado en el futuro. Exentos de esta reserva legal son breves extractos en relación con los exámenes o análisis académico o materiales suministrados específicamente con el fin de ser introducido y ejecutado en un sistema informático, para su uso exclusivo por parte del comprador de la obra. La duplicación de esta publicación o partes de los mismos se permite solamente bajo las disposiciones de la Ley de Propiedad Intelectual sobre la ubicación del Editor, en su versión actual, y el permiso para su uso siempre debe ser obtenido de Springer. Permisos de uso se pueden obtener a través de Rightslink en el Centro de Copyright Clearance. Violaciónes deben ser procesados ​bajo la respectiva Ley de Propiedad Intelectual.

ISBN-13 (PBK): 978-1-4302-6691-4 ISBN-13 (electrónico): 978-1-4302-6692-1 nombres de marcas registradas, logotipos y las imágenes pueden aparecer en este libro. En lugar de utilizar un símbolo de marca con cada ocurrencia de un nombre, logotipo o imagen de marca registrada que utilizamos los nombres, logotipos e imágenes solamente de forma editorial y para el beneficio del propietario de la marca, sin intención de infracción de la marca. La utilización de esta publicación de nombres comerciales, marcas registradas, marcas de servicio y términos similares, aunque no se identifican como tal, no debe ser tomado como una expresión de opinión en cuanto a si son o no están sujetos a derechos de propiedad.

Mientras que el asesoramiento y la información en este libro se cree que son verdadera y exacta a la fecha de publicación, ni los autores ni los editores ni el editor puede aceptar ninguna responsabilidad legal por cualquier error u omisión que se pueden hacer. El editor no ofrece ninguna garantía, expresa o implícita, con respecto al material contenido en el presente documento.

Editorial: Heinz Weinheimer plomo Editor: Jonathan Gennick Técnico Evaluador: Jen Underwood Consejo Editorial: Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Louise Corrigan, Jim DeWolf,

Jonathan Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Mateo Moodie, Jeff Olson, Jeffrey pimienta, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade, Steve Weiss Coordinación Editor: Jill Balzano correctores : Ann y James Dickson Fraleigh Compositor: SPI Global paso a paso: SPI Global Artista: SPI Global

Cover Designer: Anna Ishchenko Distribuido a la industria del libro en todo el mundo por Springer Science + Business Media Nueva York, 233 Spring Street, 6th Floor, New York, NY 10013. Teléfono 1-800-Springer, fax (201) 348 a 4.505, e-mail [email protected] , o visitar www.springeronline.com . Apress Media, LLC es una LLC de California y el único miembro (propietario) es Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc es una corporación de Delaware. Para obtener información sobre traducciones, envíe un correo electrónico [email protected] , o visitar www.apress.com . Apress y amigos de los libros ED se pueden comprar en grandes cantidades para uso académico, corporativo o de promoción. versiones de libros electrónicos y licencias también están disponibles para la mayoría de los títulos. Para obtener más información, hacer referencia a nuestra página web de licencias a granel Ventas-libro electrónico en especial www.apress.com/bulk-sales .

Cualquier código fuente u otro material complementario al que hace referencia el autor en este texto están disponibles para los lectores en www.apress.com . Para obtener información detallada acerca de cómo localizar el código fuente de su libro, vaya a

www.apress.com/source-code/ .

www.it-ebooks.info

Para Helen, Natasha, y Mimi. YAVNANSB!

www.it-ebooks.info

Contenido

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

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

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

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

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

Potencia para realizar consultas y el poder de BI ������������������������������������������

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

la concesión de licencias ����������������������������������������������������������

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

Conceptos �������������������������������������������������������������

Tutorial ������������������������������������������������������������

consultas sencillas �����������������������������������������������������������������������������

consulta existente �����������������������������������������������������������������������������

Poder de consulta? ����������������������������������������������������������������������

Resumen ��������������������������������������������������������������

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

Consulta de bases de datos relacionales ���������������������������������������������

Servidor ���������������������������������������������������������������������������������� 7 Navegando a través de una base de datos en la consulta

Editor �����������������������������������������������������������������

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

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

archivos ����������������������������������������������������������������������������������

archivos ���������������������������������������������������������������������

vii www.it-ebooks.info

■ Contenido

Trabajar con archivos XML �������������������������������������������������������������������������� 9 Trabajar con archivos JSON

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

archivos �����������������������������������������������������������������������������������

Carpetas y varios archivos �������������������������������������������������������

Los datos del sistema de archivos de Windows ����������������������������������������������������������

La combinación de datos desde archivos de texto múltiples �������������������������������������������������������������� 3 Trabajar con datos

desde el actual libro de Excel ��������������������������������������������������������

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

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

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

Buscar el poder de consulta en línea ���������������������������������������������������������������������

Consultas como orígenes de datos ����������������������������������������������������

Las consultas enteras �������������������������������������������������������������������������������

consultas ���������������������������������������������������������������������������������

Valores individuales de consultas ���������������������������������������������������������������������������

Fuentes de datos ���������������������������������������������������������

Trabajar con genéricos OData Servicios Web ���������������������������������������������������������������������

Trabajar con datos de Excel los libros almacenados en SharePoint �����������������������������������������������������������

Trabajo con datos de listas de SharePoint ����������������������������������������������������������������������

Trabajo con datos del mercado de Windows Azure ������������������������������������������������������������������

Trabajo con datos de Windows Azure Blob y depósitos de almacenamiento de tablas ���������������������������������������������������

Trabajo con datos de HDFS y HDInsight ���������������������������������������������������

Trabajo con datos de Active Directory ����������������������������������������������������

Trabajo con datos de Microsoft Exchange ��������������������������������������������������

Trabajo con datos de Facebook �������������������������������������������������������

Trabajo con datos de SAP BusinessObjects �������������������������������������������������

La reutilización de Fuentes de Datos recientes �������������������������������������

Administración de credenciales ����������������������������������������������������

Lugar ����������������������������������������

www.it-ebooks.info

viii

www.it-ebooks.info

■ Contenido

Establecimiento de una configuración regional predeterminada �������������������������������������������������������� 9 CSV archivos y páginas de códigos

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

Resumen ��������������������������������������������������������������

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

Pasos ������������������������������������������������������

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

nombrando Columnas ������������������������������������������������������������������ 5 mover Columnas

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

columnas ��������������������������������������������������������������������������������

columnas ���������������������������������������������������������������������������������

columnas �����������������������������������������������������������������������������������

Tipo de datos de una columna ���������������������������������������������������������������������������

Tipos y variantes regionales ���������������������������������������������������������������������������

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

Filas de filtrado Uso de Auto-Filter ����������������������������������������������������� 2

Filas de filtrado usando el número, texto y Filtros de fecha ����������������������������������������������������������� 3 Filas de filtrado por

Distancia ������������������������������������������������������������������������

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

errores ��������������������������������������������������������������������������

Mesa ����������������������������������������������������������

Cambio de valores en una tabla ����������������������������������������������������

Sustitución de los valores con otros valores ������������������������������������������������������������������

Transformadas de texto ���������������������������������������������������������������������������

Transforma números ����������������������������������������������������������������������������

Transforma Fecha / Hora / Duración ���������������������������������������������������������������������

Llenar Arriba y Abajo para reemplazar los valores perdidos ������������������������������������������������������������ 6 La agregación de valores

������������������������������������������������������������������� Unpivoting columnas a

filas ����������������������������������������������������������������� transposición una

Mesa ����������������������������������������������������������������

ix www.it-ebooks.info

■ Contenido

Crear columnas personalizadas ������������������������������������������������������

�������������������������������������������������������������������������������� 4 Columnas personalizadas con cálculos M

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

Resumen �����������������������������������������������������������������

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

99 La elección de un destino para sus datos ������������������������������������������

Hoja de cálculo ������������������������������������

Utilizando la tabla de salida por defecto Excel �������������������������������������������������������������������

Las tablas propias de Excel �������������������������������������������������������������������������

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

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

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

Potencia para realizar consultas y relaciones entre tablas ������������������������������������������������������������ 0 7 Los cambios de última hora

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

consultas ���������������������������������������������������������������

consultas manualmente �������������������������������������������������������������������������������

Actualizar los datos ������������������������������������������������������������������������

Resumen �����������������������������������������������������������������

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

Escribir M en el Editor de consultas �����������������������������������������������������

����������������������������������������������������������������������������������������� 1 1 5 los Avanzado ventana del editor

����������������������������������������������������������������������������������������� 116 Creación una Blanco

Consulta �������������������������������������������������������������������������������������

Conceptos M Idioma ������������������������������������������������������������

Dejar declaraciones ������������������������������������������������������������������������������� 8 1 Escritura METRO

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

X

www.it-ebooks.info

■ Contenido

Listas, Registros y tablas �������������������������������������������������������

Liza ������������������������������������������������������������� 3 2

Archivos ������������������������������������������������������������������������������� 6 2

Mesas ����������������������������������������������������������������������������� 2 7 Selecciones y Proyecciones

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

funciones ��������������������������������������������������������������

Definición de funciones Dentro de una consulta �����������������������������������������������������������������

cada expresiones ������������������������������������������������������������������������������

Las consultas como funciones ������������������������������������������������������������������������

dejar Las expresiones en la definición de funciones ���������������������������������������������������������������

Funciones recursivas ����������������������������������������������������������������������������

Funciones importadas de fuentes de datos �������������������������������������������������������������������

El trabajo con los Servicios Web ����������������������������������������������������

plegable consulta ����������������������������������������������������������

Monitoreo de Peticiones plegable en SQL Server ����������������������������������������������������������������

La prevención de consulta plegable en el Código ����������������������������������������������������������������

Otras operaciones que el plegamiento puede prevenir la consulta ���������������������������������������������������������

Resumen ��������������������������������������������������������������

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

El uso de una consulta como fuente para otro ����������������������������������������������

en codigo ������������������������������������������������������������������� 4 9 Creación parametrizado

consultas ��������������������������������������������������������������������������������

Las fuentes de datos externas ��������������������������������������

ajustes ����������������������������������������������������������������������������������

firewall ��������������������������������������������������������������������������������

combinar Opción ������������������������������������������������������������������������

Al añadir datos de una consulta a otra �������������������������������������������������

Al añadir las consultas en la interfaz de usuario �����������������������������������������������������������������

Al añadir en M �������������������������������������������������������������������������������

La fusión de dos consultas ������������������������������������������������������

xi www.it-ebooks.info

■ Contenido

La fusión de las consultas en la interfaz de usuario ����������������������������������������������������������� 6 1 La fusión en M

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

Resumen �����������������������������������������������������������������

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

Compartir y Uso de consultas compartidas en el poder de consultas ��������������������������������������

������������������������������������������������������������������������������� 6 7 El consumo de consultas compartidas

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

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

Catálogo de potencia de datos de BI ������������������������������������������������

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

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

Analítica �������������������������������������������������������������������������������������

fuentes �����������������������������������������������������������������������������������

Mayordomo ���������������������������������������������������������������

Steward datos? ���������������������������������������������������������������������������������

consultas ����������������������������������������������������������������������������������

Las consultas deben ser compartidos? �������������������������������������������������������������������������

funciones �������������������������������������������������������������������������������

BI energía para la oficina 365 de actualización de datos ����������������������������������������

Fuentes ����������������������������������������������������������������������� 8 0 Activación programada

Refrescar ���������������������������������������������������������

Resumen �����������������������������������������������������������������

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

cálculos �����������������������������������������������������������������

Porcentaje de participación de Gran Total ����������������������������������������������������������������������� 8 9 El crecimiento en porcentaje

Ventas

desde

la

Anterior

Día

���������������������������������������������������������� 193 Rangos atadas

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

El número de clientes Distintos ���������������������������������������������������������������������������

xii www.it-ebooks.info

■ Contenido

Transformaciones de mesa ������������������������������������������������������

Columna de tabla a una tabla de varias columnas ���������������������������������������������������������������� 0 5 Encontrar nuevas, Perdido, y el regreso

Clientes ���������������������������������������������������������������������������������� 210 generadora una Fecha

Mesa �����������������������������������������������������������������������������������

Datos de la bolsa encima de un valor dado? ������������������������������������������������������������������

Trabajo con datos de la Web �����������������������������������������������������

Pronóstico de datos ���������������������������������������������������������������������������� 3 0 Obtención de la distancia de conducción entre dos Ubicaciones utilizando el servicio web de Bing Mapas de Ruta ���������������������� 238

Resumen ��������������������������������������������������������������

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

xiii www.it-ebooks.info

Sobre el Autor

Chris Webb es un consultor independiente y formador en el Reino Unido. Tiene más de 15 años de experiencia con la pila de Microsoft BI y ha trabajado en una variedad de roles (incluyendo varios años con Microsoft Consulting Services) a través de una amplia gama de industrias. Un ponente habitual en los grupos de usuarios y conferencias en todo el mundo, tales como la Cumbre del paso y de la Conferencia Business Analytics PASS, que es también uno de los organizadores de la conferencia SQLBits en el Reino Unido y ha recibido el premio Profesional más valioso de Microsoft durante nueve años consecutivos . Él es el co-autor de varios libros, tales como Soluciones MDX, Experto Cubo desarrollo con SQL Server Analysis Services, y Analysis

Services de SQL Server 2012: El modelo tabular BISM, y los blogs sobre Microsoft BI en

http://cwebbbi.wordpress.com . Más información acerca de su asesoría se puede encontrar en www.crossjoin.co.uk

y sobre su curso de formación en www.technitrain.com .

xvi www.it-ebooks.info

Sobre el revisor técnico Jen Underwood tiene casi 20 años de experiencia práctica en el almacenamiento de datos, inteligencia empresarial, la presentación de informes, y la industria de análisis predictivo. Antes del lanzamiento de Impacto Analytix, que era un administrador de Microsoft Global Business Intelligence técnica del producto responsable de la comercialización de productos técnicos y la preparación de campo para una suite de mercado de $ 10 + mil millones de análisis de las ofertas que atraviesan a través de Microsoft SQL Server, Office y SharePoint. También ocupó cargos como una plataforma de Especialista de datos empresariales, Tableau Tecnología Evangelista, y un Consultor Business Intelligence para Grandes 4 de Integración de Sistemas. A lo largo de la mayor parte de su carrera ha estado investigando, diseñando e implementando soluciones analíticas a través de una variedad de código abierto, nicho, y paisajes de proveedores de empresas como Microsoft, Oracle, IBM y SAP.

Como analista experimentado la industria, el presentador, autor, blogger, y el entrenador, Jen es muy activo en la comunidad técnica mundial. Recientemente fue honrado con una calidad de miembro de Boulder BI Brain Trust (BBBT), un premio Tableau Maestro Zen (MVP), un papel de liderazgo en el mundo PASS Excel BI capítulo, y un MVP de Dun & Bradstreet. Ella escribe artículos para BeyeNetwork, Pro Server SQL, y otros canales de comunicación de la industria.

Jen tiene una licenciatura en Administración de Empresas de la Universidad de Wisconsin, Milwaukee y un certificado de postgrado en Ciencias de la Computación-minería de datos de la Universidad de California, San Diego.

xvii www.it-ebooks.info

Expresiones de gratitud

Me gustaría agradecer a las siguientes personas por su ayuda (ya sea directa o indirectamente) en el aprendizaje de la energía de consulta y escribir este libro: Jen Underwood, Melissa Coates, Andrea Uggetti, Faisal Mohamood, Miguel Llopis, Reza Rad, Douglas Day, Ron Pihlgren, Steve Wright, Matt Masson, Teresa Palmer-Boroski, Steve Peters, Jimmy Haley, Bob Phillips, John White, Lee Hawthorne, Curt Hagenlocher, Oliver Engels, Mateo Roche, Kasper de Jonge, Jamie Thomson, Andrew Fox, Zafar Abbas, y Marco Russo.

xix www.it-ebooks.info

Prefacio

Es fácil crear demostraciones sorprendentes con la nueva suite de BI de energía de Microsoft de herramientas. Si usted ha comprado este libro probablemente ya has visto más que su parte justa: Ver millones de valores sumados en un segundo pivote usando la energía! Mira las burbujas rebotan alrededor de un gráfico con Power View! Un zoom sobre un paisaje 3D con Power Mapa! Hablar con sus datos en Q & A! Muy a menudo la energía de consulta sólo se muestra durante unos segundos, si se muestra en absoluto. Y sin embargo ... a cualquiera que intente hacer ningún trabajo real con la energía BI pronto se da cuenta de que la energía de consulta es la verdadera estrella de la pila. Me enamoré de él tan pronto como vi las primeras versiones experimentales, de vuelta cuando se llamaba Explorador de datos, y lo he visto crecer y convertirse en una herramienta inmensamente capaz - una que es a la vez fácil de usar para el principiante analista y extremadamente flexible para el consultor de BI con experiencia.

Una de las mejores cosas de la energía de consulta, sin embargo, presenta un gran problema para este libro. Durante el último año, el equipo de desarrollo han sido la liberación de nuevas versiones de Power consulta de casi todos los meses: nueva funcionalidad se ha añadido, la interfaz de usuario ha sido alterado y mejorado, y errores han sido corregidos. Este libro fue escrito en los primeros meses de 2014 y 2014 utiliza la versión de mayo (versión 2.11.3660.141) como su línea de base. Sin embargo por el momento de leer esto, es cierto que se han aparecido nuevas versiones de Power de consulta y como resultado algunas partes de este libro va a estar fuera de fecha. Esta es la consecuencia inevitable de la nueva política de ciclos de liberación acelerada de Microsoft y espero que no se descomponga su disfrute de este libro. Incluso si las nuevas características se han añadido y la interfaz de usuario ha cambiado, Este libro está estructurado de la siguiente manera. Capítulo 1 proporciona una visión general de Energía de consulta, lo que hace y cómo se inserta en la pila de energía más amplia de BI; También introduce la interfaz de usuario de energía de consulta. Los capítulos 2, 3 y 4 a continuación, entrar en más detalles acerca de la funcionalidad disponible en la interfaz de usuario, que cubre cómo extraer datos de todas las fuentes de datos soportadas, la transformación de los datos, y la carga de esos datos en la hoja de cálculo o el modelo de datos de Excel. Juntos estos capítulos tratan de casi toda la funcionalidad que necesitará siempre el usuario ocasional de consultas de energía. Capítulo 5 tiene un enfoque más técnico: se trata de la lengua en que se basa M Poder consulta. Aprender M es necesario si se quiere ir más allá de los conceptos básicos de alimentación de consulta, pero si no se siente cómodo con la programación entonces usted lo desea, puede deslizarse sobre este capítulo. Capítulo 6 analiza cómo trabajar con varias consultas, y si bien tiene una cierta cantidad de código M en que usted no necesita un conocimiento profundo de M para muchos de los temas que discute. El capítulo 7 muestra cómo compartir consultas de consulta de alimentación a través de un sitio de alimentación de BI y sólo es relevante si ha adquirido una suscripción de energía BI. Por último, el capítulo 8 se compone de una serie de ejemplos paso a paso que muestra cómo se puede solucionar problemas comunes de negocios usando la energía de consultas y se recomienda para todos los lectores.

xxi www.it-ebooks.info