Mostrando entradas con la etiqueta Office. Mostrar todas las entradas
Mostrando entradas con la etiqueta Office. Mostrar todas las entradas

jueves, 3 de septiembre de 2015

Editor de Consulta III

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


La pestaña Agregar Columna

Permite añadir nuevas columnas a nuestra tabla. Las columnas pueden ser creadas usando las fórmulas del lenguaje, transformando los valores de otras columnas, duplicando una columna, etc.

La cinta de opciones de la pestaña Agregar Columna

Se divide en cuatro grupos: General, De texto, De número  y De fecha y hora.



Grupo General



Permite añadir nuevas columnas personalizadas,  de índice y duplicadas.





Opción Agregar Columna Personalizada:

Esta opción nos permite añadir una nueva columna usando las columnas existentes y el lenguaje de Power Query que nos ofrece una amplia variedad de fórmulas que podemos utilizar para construir expresiones complejas.



En el ejemplo tenemos una tabla con los datos de los equipos de futbol entre los que se encuentra el año de fundación. Vamos a añadir una columna, Años de fundado, que obtendremos calculando la diferencia en años entre la fecha actual y el año de fundación. Para conocer la fecha de hoy utilizaremos la función DateTime.LocalNow() y para  el año la función Date.Year().




La nueva columna aparece al final, pero podemos moverla para que se muestre a continuación de la columna Año fundación.



Opción Agregar columna de índice:

Permite crear una columna de índice, de tipo entero, que puede comenzar desde el valor 0, desde el valor 1 o desde cualquier otro valor.



En el ejemplo tenemos una tabla con la lista de jugadores de futbol, la tabla no tiene una columna de índice, la crearemos con la opción Agregar columna de índice | Desde 1.



La columna índice se muestra al final de la lista.



Podemos crear un índice personalizado seleccionando la opción Agregar columna índice | Personalizado...



Debemos especificar el valor inicial y el incremento para el índice.



Opción Columna duplicada:

Permite crear una nueva columna a partir de otra seleccionada previamente.




El ejemplo muestrala tabla de resultados de la segunda jornada de la liga BBVA 2015 - 2016. Seleccionaremos la columna Column1 que contiene el día y el mes del partido y la duplicaremos.



La columna creada se muestra al final de la lista y como nombre toma el de la columna original con el sufijo " - Copia"





Grupo De Texto

Permite crear y transformar columnas de tipo texto.



Opción Extraer:

A partir de una columna de texto podemos crear  una nueva columna.

En el ejemplo seleccionaremos la columna duplicada Column1 - Copia y crearemos dos nuevas columnas.

Una con los 2 primeros caracteres para el día del partido.




y otra con los 2 últimos caracteres para el mes del partido.



En ambos casos debemos especificar el número de caracteres a conservar.





Finalmente renombramos las columnas: Día y Mes



Opción Combinar columnas

Permite crear una columna a partir de dos columnas existentes.

En el ejemplo seleccionaremos las columnas Día y Mes y las combinaremos



Debemos escoger un separador y un nombre para la nueva columna. El separador es opcional y el nombre de columna por defecto es Combinada.



La columna se muestra al final de la tabla.



Opción Formato

Permite crear una nueva columna aplicando transformaciones a la columna original.
En el ejemplo seleccionaremos la columna Equipo H y seleccionaremos la opción Formato | MAYÚSCULAS



La columa resultante se llamará Uppercase y será la última de la tabla.



Opción Analizar

Permite crear una nueva columna a partir de otra seleccionada previamente y cuyos datos estén en formato XML o JSON.

En nuestro ejemplo seleccionaremos Columna2 que los datos que contiene están en formato JSON y analizaremos el contenido.



Se creará una nueva columna, Json, que debemos expandir





Al expandir la columna se mostrarán 2 columnas  firstName y lastName.



Grupo De número


Permite crear columnas de tipo númerico a partir de otras columnas del mismo tipo haciendo transformaciones.



Opción Información 

Permite crear una columna a partir de la información obtenida del valor de la columna original, si es par o no, si es impar o no y el signo del número.

En el ejemplo seleccionamos de la tabla de equipos la columna Capacidad del estadio que es de tipo entero y analizaremos si es par.



Como resultado obtenemos la columna IsEven de tipo booleano.



Opción Estadísticas

Permite formar nuevas columnas a partir de otras utilizando las funciones estadísticas.
En el ejemplo seleccionaremos las columnas Jornada y DurationDays y la opción Estadísticas | Suma.



Como resultado se obtiene la columna Sum con el reultado de la suma.



La opción Científico permite obtener nuevas columnas a partir de utilizar funcioes matemáticas como logaritmo, factorial, etc.

En el ejemplo utilizaremos la columna Jornada (por ser de tipo entero) y la opción Científico | Factorial.



El resultado que se obtiene es el factorial del valor correspondiente de la jornada y se guarda en la columna Factorial.



La opción Estándar permite crear nuevas columnas utilizando las funciones matemáticas básicas de suma, resta, multiplicación, etc.

En el ejemplo utilizaremos la columna DurationDays y la opción Estándar | Modulo.



Obtenemos la columna Módulo insertado.



Grupo De Fecha y hora 


Permite crear columnas de tipo Fecha y hora.



Opción Fecha  Permite obtener nuevas columnas con información sobre la fecha como el año, el mes el día, etc.

En el ejemplo  se muestra una tabla con el calendario del Barça para la temporada 2015 - 2016. Seleccionamos la columna Fecha y a continuacion la opción  Fecha | Año.




Obtenemos la columna Year que contiene el año correspondiente al partido.




También podemos crear una nueva columna que contendrá el tiempo transcurrido (o por transcurrir) entre la fecha contenida en una columna y la fecha actual.

En el ejemplo seleccionamos nuevamente la columna Fecha y la opción Fecha | Antigüedad



Obtenemos la columna AgeFromDate que nos devuelve un valor de tipo Duración, positivo si la fecha ya ha transcurrido o un negativo si está por transcurrir.

En el ejemplo, en la primera fila la fecha es 23/08/2015 y la fecha en que se realizó el calculo es 02/09/2015 han transcurrido 10 días desde la fecha como se puede apreciar en la columna AgeFromDate.



Opción Hora Permite crear una columna de tipo Hora a partir de una columna.

En el ejemplo la columna Fecha es de tipo Cualquiera, la seleccionamos y a continuación la opción Hora | Analizar.



Como resultado obtenemos la columna ParseTime que nos devuelve un valor de tipo Hora. 



Si seleccionamos la columna ParseTime y la opción Hora | Minuto



Obtendremos la columna Minute con los minutos de la hora correspondiente.




Opción Duración devuelve el tiempo transcurrido en una sola unidad de tiempo: días, horas, minutos, segundos.

En el ejemplo seleccionamos la columna AgeFromDate y la opción Duración | Días.



Se crea entonces la columna DurationDays mostrando la Duración en días.

lunes, 26 de enero de 2015

Editor de Consultas II

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


La pestaña Transform

Permite dar forma a conjuntos de datos a nivel de tabla y/o columna.


La cinta de opciones de la pestaña Transform

Se divide de acuerdo a su funcionalidad en 5 grupos de herramientas: Table, Any Column, Text Column, Number Column y Date & Time Column.



Grupo Table

Permite modificar la estructura de la tabla



Opción Group By:

Agrupa los valores de las filas en dependencia de la columna seleccionada. Esta opción ya la estudiamos en la cinta de opciones de la pestaña Home.

Opción Use First Row As Header:

Asigna a cada columna como nombre el valor correspondiente de la primera fila de resultados. Esta opción la estudiamos en la cinta de opciones de la pestaña Home.

Opción Transpose

Intercambia filas con columnas.

Las fuentes de datos que usaremos para mostrar esta herramienta son:
http://www.espndeportes.com/futbol/posiciones?liga=esp.1&season=2010
http://www.espndeportes.com/futbol/posiciones?liga=esp.1&season=2011
http://www.espndeportes.com/futbol/posiciones?liga=esp.1&season=2012
http://www.espndeportes.com/futbol/posiciones?liga=esp.1&season=2013

A partir de varias transformaciones a las consultas originales hemos obtenido la tabla Resumen, cuyos nombres de columnas son: EQUIPO, Atribute y Value. Utilizaremos la opción Transpose para intercambiar las filas y las columnas de la tabla.




Esta transformación le asigna nombres genéricos a las columnas, Column1, Column2,..., que podemos modificar.



Opción Reverse Rows

Invierte el ordenamiento del conjunto de resultados.

En el ejemplo, deshacemos la última transformación realizada para devolver la tabla a su estado anterior. los resultados se muestran ordenados ascendentemente por el nombre del Equipo.



Al seleccionar la opción Reverse Rows, teniendo seleccionada la columna EQUIPO,  las filas de la tabla se ordenarán de manera descendentemente respecto al nombre de Equipo.



Opción Count Rows

Esta opción nos devuelve como resultado el número de filas de la tabla de resultados.



En el ejemplo de demostración tenemos 108 filas de resultados



Grupo Any Column



Permite modifica las propiedades de las columnas de la tabla de resultados.


Opción Data Type

Modifica el tipo de dato de la columna seleccionada.  Esta opción ya la estudiamos en la cinta de opciones de la pestaña Home.
Opción Replace Values

Reemplaza todas las ocurrencias de un valor dado en las columnas seleccionadas, por otro especificado.  Esta opción ya la estudiamos en la cinta de opciones de la pestaña Home.
Opción Replace Errors

Sustituye todos los errores en las columnas seleccionadas por un valor especificado.

En el siguiente ejemplo podemos observar que hay 2 filas en las que las 3 últimas columnas contienen errores.



En este caso sustituiremos los errores con el valor 0



De esta manera la tabla resultante solo contendrá valores numéricos, no contendrá errores.



Opción Fill:

Permite llenar las celdas vacías de una tabla con un valor dado.

Para este ejemplo hemos utilizado como fuente de datos la tabla Customer de la base de datos Northwind.

Podemos observar que la columna Region de dicha tabla aparece con valor null en la mayoria de los casos.

Seleccionemos las columnas Fax y Region y acontinuación  la opción Fill y dentro de ella la opción Up, que rellenará las celdas vacías de las columnas de abajo hacia arriba, es decir, en cada columna seleccionada las celdas vacías serán rellenadas usando el primer valor  que esté por debajo de ella.



En el ejemplo:
La primera celda vacía en la columna Fax  es el correspondiente a la  fila 3 y se ha rellenado con el valor de la  fila 4.
La primera celda no vacía en la columna Region es la correspondiente a la fila 10 y su valor es BC, las nueve filas anteriores se rellenan usando este valor.



Opción Pivot Column

La opción Pivot Column utiliza los valores de la columna seleccionada para formar nuevas columnas.
En el ejemplo usaremos la columna Attribute que contiene los años 2010, 2011, 2012 y 2013 para los nombres de las nuevas columnas.



Una vez seleccionada la opción Pivot Column se muestra una ventana donde debemos escoger la columna de valores que usaremos para crear la agregación, así como el tipo de agregación.

En el ejemplo seleccionaremos la columna Value y la función de agregación Suma.



La tabla resultante tiene una columna con los nombres de los equipos y  4 columnas, una por cada año,  con los puntos obtenidos por cada equipo en cada uno de los años.




Opción Unpivot Column

La opción Unpivot Column realizará la operación inversa a Pivot Column.

Unpivot Column nos ofrece dos posibilidades:
Unpivot Columns: que usaremos para desagrupar las columnas seleccionadas.
Unpivot Other Columns: que usaremos para preservar el valor de la columna seleccionada y desagrupar el resto de columnas.

En el ejemplo seleccionamos la columna Equipo y escogemos la opción Unpivot Other Columns.




La tabla resultante mantiene la columna Equipo y convierte las 4 columnas correspondientes a cada año en el par Attribute - Value. La tabla vuelve al estado anterior a la transformación Pivot Column.



Opción Expand

Esta opción nos permite adicionar nuevas columnas a la tabla de resultados, expandiendo las columnas que contienen tablas, listas o registros. Estas columnas se pueden identificar facilmente por el icono de flecha doble que aparece a la derecha del encabezado ( ).

En el siguiente ejemplo vamos a utilizar la tabla OrderDetails de la base de datos Northwind. La fuente de datos es odata: http://diana-hp/NorthwindService/Northwind.svc/Order_Details 



Al seleccionar la opción Odata del menú Otras fuentes se muestra la siguiente ventana donde escribiremos el url de la fuente de los datos:



A continuación se abre el editor de consulta con los datos. La tabla tiene 6 columnas, las primeras cinco corresponden con la tabla Order_Details y la sexta corresponde a la tabla Orders.



Si seleccionamos la Opción Expand aparece una ventana con la lista de columnas disponibles de la segunda tabla.




Podemos escoger las columnas que deseamos incluir. Esta opción es equivalente a hacer clic en el icono de flecha doble que aparece a la derecha del encabezado NewColumn ( ).




Como resultado obtenemos una tabla que contiene todas las columnas de la primera tabla y las seleccionadas de la segunda tabla. Los nombres de las columnas de la segunda tabla aparecen precedidos con Orders. lo que podemos modificar.




Opción Agregate

Permite realizar calculos de valores a partir de las estructuras de datos anidadas.

Para este ejemplo vamos a utilizar la tabla Orders de la base de datos Northwind. La fuente de datos es odata: http://diana-hp/NorthwindService/Northwind.svc/Orders 




La última columna de la tabla corresponde a la tabla Order_Details. si seleccionamos esta columna y a continuación la opción Aggregate


Se abre una ventana donde podemos seleccionar las columnas y el tipo de función   utilizar.




Seleccionando la columna Productid y la función Cuenta, obtendremos una nueva columna que nos muestra la cantidad de productos correspondientes a cada orden.



Grupo Text Column



Este grupo de opciones permite hacer transformaciones a nivel de columna. El tipo de datos de la columna debe ser Text.



Opción Split Column:

Divide una columna en 2 o más columnas. Esta opción la estudiamos dentro del grupo Transform de la pestaña Home.


Opción Format:

Formatea la columna de texto seleccionada.

En el ejemplo seleccionaremos la columna ShipCountry y a continuación la opción UPPERCASE de Format.



Los nombres de los paises quedarán escritos en mayúsculas.



Opción Merge Columns

Permite concatenar columnas de la tabla utilizando delimitadores. Como resultado obtendremos una nueva columna.

En el ejemplo concatenaremos las columnas ShipAddress y ShipCity.



Utilizaremos como delimitador la coma y nombraremos la nueva columna como Address.



Podemos observar que se han eliminado las columnas ShipAddress y ShipCity. En su lugar se muestra la columna Address.


Opción Length:

Sustituye el texto por su longitud


Seleccionemos la columna Address y a continuación la opción Lenght




Los valores de la columna serán sustituidos por la longitud de la cadena de caracteres.




Opción Parse


El origen de datos para este ejemplo es una fuente publica: haremos una búsqueda usando la expresión XML-RPC




Seleccionaremos la fuente Data types - XML-RPC



La tabla se cargará en el editor de consultas.



Eliminaremos la primera y las dos últimas filas.;



Seleccionaremos la opción Parse y dentro de esta XML.





Si expandimos la columna y seleccionamos la columna Text.


Podemos visualizar eol contenido de la columna sin las etiquetas XML.



Grupo Number Column



Este grupo nos permite realizar transformaciones en columnas numéricas.



Opción Statistics:

Permite realizar calculos utilizando las funciones estadísticas de máximo, mínimo, promedio, etc.



En este caso la función Min devuelve como resultado la menor cantidad de puntos obtenida por un equipo.



Permite realizar calculos utilizando las funciones matemáticas de suma, resta, multiplicación y división.



En el ejemplo dividimos los valores de la columna Ptos entre 100.


Como resultado los valores de la columna Pts son todos menor o igual que 1.



Opción Scientific:

Permite realizar calculos utilizando funciones matemáticas complejas como potencia, raíz cuadrada, factorial, exponencial, logaritmo.



En el ejemplo los valores de la columna Ptos los elevaremos a la 4ta potencia.





Opción Trigonometry:

Permite realizar calculos utilizando las funciones trigonométricas: seno, coseno, tangente, arco seno, arco coseno, arco tangente.



En el ejemplo sustituiremos los valores de la columna Pts por su arco coseno.



Opción Rounding:

Permite redondear números decimales especificando el número de lugares deseados.



En el ejemplo redondearemos los valores de la columna Pts a dos lugares decimales.





Opción Information:

Retorna información acerca del número seleccionado, si es par, impar o su signo.



En el ejemplo hemos utilizado la función Sign para saber el signo de los valores de la columna Pts.



Grupo Date & Time Column



Este grupo permite transformar datos de tipo Fecha y Hora.




Opción Date:

Permite transformar columnas de tipo Fecha/ Hora devolviendo la fecha, el año, el día, el mes, etc.



Opción Time:

Permite transformar columnas de tipo Fecha/ Hora devolviendo el número de horas, minutos o segundos.




Option Duration:

Permite transformar columnas de tipo Duration devolviendo los dias, horas, minutos, segundos, etc.