Mostrando entradas con la etiqueta Power BI. Mostrar todas las entradas
Mostrando entradas con la etiqueta Power BI. Mostrar todas las entradas

viernes, 11 de septiembre de 2015

Editor de Consultas IV

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


La pestaña Vista

Contiene el grupo de opciones Mostrar que permiten ver y modificar las fórmulas de la consulta.



 Opción Configuración de la consulta: Permite ocultar o mostrar el panel Configuración de la consulta.



En el panel de Configuración de la consulta podemos ver y modificar el nombre de la consulta y los pasos aplicados.



Cada paso de la consulta se corresponde con una fórmula de Power Query. Esas fórmulas las podemos crear o modificar manualmente o a través de las herramientas de los distintos menús del Editor de consultas de Power Query.

Para poder construir manualmente las fórmulas necesitamos el Editor avanzado o la Barra de fórmulas y el lenguaje de fórmulas de Power Query.

Opción Editor avanzado: Muestra el Editor avanzado




Al abrir el Editor avanzado podemos ver y modificar las fórmulas utilizadas en la consulta.



Opción Barra de fórmulas: Permite mostrar u ocultar la barra de fórmulas.




La barra de fórmulas se encuentra situada sobre el panel de resultados y muestra por defecto la última fórmula que hemos utilizado. Cuando abrimos el Editor de consulta nos muestra la que aparece al final de los PASOS APLICADOS el panel de Configuración de la consulta.

En la barra de fórmulas podemos modificar el paso seleccionado, para ello debemos conocer el Lenguaje de formulación de Power Query.



 

Lenguaje de Formulación de Power Query

El lenguaje de fórmulas de Power Query (informalmente conocido como "M") es un poderoso lenguaje optimizado para la construcción de consultas que integran y reutilizan  datos de multiples orígenes.
Se trata de un lenguaje funcional (basado en funciones), en su mayoría puro (solo se pueden utilizar funciones), de orden superior, tipado dinámicamente (la comprobación de tipificación se realiza durante su ejecución en vez de durante la compilación), parcialmente perezoso (retrasa el cálculo de una expresión hasta que su valor sea necesario y evita repetir la evaluación en caso de que sea necesaria posteriormente) y case sensitive,  similar a F#, que puede ser utilizado con Power Query en Excel y Power BI Designer.

Podemos encontrar  una introducción al lenguaje en  msdn en el siguiente enlace: Introducción al lenguaje de fórmulas de Power Query

El lenguaje posee un conjunto amplio de funciones agrupadas por categorías que podemos consultar en msdn en el enlace: Categorías de funciones

Veamos un ejemplo del uso del lenguaje en el Editor avanzado.

Abriremos el editor de consultas y seleccionemos una de las consultas que aparecen en el panel de navegación. En el panel de resultados podemos ver una tabla con dos columnas.



En el panel de configuración se muestran los pasos para obtener ese resultado: Origen y Navegación. Si abrimos el Editor avanzado podemos ver las expresiones correspondientes.



Analicemos las expresiones que aparecen en el editor.

En la primera línea aparece la expresión let que nos permite calcular un conjunto de valores, asignarles nombres y luego usarlos para formar nuevas y más complejas expresiones.

La segunda linea contiene el primer valor calculado  Origen que  se obtiene como  resultado de evaluar la función  Sql.Database. Esta función pertenece a la categoría  Accessing data y tiene dos parámetros: el primero para escribir el nombre de la instancia del servidor de base de datos SQL Server y el segundo para el nombre de la base de datos que queremos utilizar. La función devuelve como resultado una tabla que contiene todas las tablas de la base de datos.

En el ejemplo a la función Sql.Database(".", "Futbol") le hemos pasado como parámetros la instancia por defecto de SQL Server y la base de datos Futbol.

Si cerramos el editor avanzado y seleccionamos Origen en el panel de Configuración de la consulta podremos ver en el panel de resultados todas las tablas que contiene la base de datos Futbol, así como sus características.



La tercera línea del Editor avanzado contiene el segundo valor calculado que denominamos #"dbo_'Jornadas 2014 - 2015$'" y al que le asignamos la tabla Jornadas 2014 - 2015$.

La cuarta línea corresponde a la palabra reservada in, que indica que termina la construción de expresiones.

En la quinta línea devolvemos la expresión resultante. En este caso, devolvemos el valor calculado en la linea 3:  #"dbo_'Jornadas 2014 - 2015$'"


Crear una consulta de forma manual en la Barra de fórmulas.

Lo primero será crear una consulta en blanco. Para ello en el menú Power Query de la cinta de opciones seleccionaremos la opción Desde otros origenes | Consulta en blanco



Se muestra el Editor de consultas, en el panel de resultados no se observa ningún valor y la barra de fórmulas está vacía, sin embargo en el panel de Configuración de la consulta contiene un paso llamado Origen.



Si abrimos el Editor avanzado podemos ver se ha asignado la cadena vacia al valor Origen y la expresión que se devuelve es Origen.



 Cerremos el Editor avanzado y a continuación escribamos en la Barra de fórmulas la expresión:
= Text.Combine({"Power", "Query"}, " ")



y oprimimos la tecla Enter.



Veremos que en el panel de resultados se muestra el texto Power Query.

Si abrimos el Editor avanzado podemos ver que se ha modificado, a Origen se le asignado la expresión que escribimos en la Barra de fórmulas.

La función Combine pertenece a la categoría de funciones Text functions  y permite enlazar la lista de textos pasados en el primer parámetro usando como separador el segundo parámetro.



 Crear una consulta de forma manual en el Editor avanzado

Vamos a modificar la consulta. Sustituiremos ahora la expresión que aparece después de let con las expresiones:
x = 1,
y = 2,
z = x+ y

Sustituiremos en la parte del in la expresión que aparece por la expresión

x + y + z

como se puede apreciar en la imagen.



Chequearemos que esté sintacticamente bien escrita y oprimiremos el botón Listo. En el panel de resultados se mostrará el valor 6 que es el resultado de evaluar la expresión.



Por último veamos un ejemplo de consulta que conecta con una base de datos.
Para ello crearemos otra consulta en blanco en el menú Power Query de la cinta de opciones.

A continuación en la pestaña Inicio del Editor de consultas dentro en el grupo Nueva consulta seleccionaremos la opción Nuevo Origen | Base de datos | SQL Server.



Se mostrará una ventana solicitando el nombre del servidor de bases de datos, el nombre de la base de datos (opcional) y  una consulta T-SQL (opcional).



Una vez introducidos los datos y presionado el botón Aceptar se muestra una nueva ventana solicitando las credenciales para acceder al servidor de base de datos.



Después de especificar las credenciales y presionar el botón conectar y en el caso de no haber escrito ninguna instrucción sql se mostrará una ventana con la lista de tablas disponibles. Debemos seleccionar la tabla que utilizaremos.



En este caso hemos escogido la tabla 'Jornadas 2014 - 2015$',  que se abrirá el editor de consulta mostrando su contenido en el panel de resultados.



 Si abrimos el Editor avanzado podemos ver las fórmulas empleadas.



Por último podemos cerrar el editor y cargar los datos en una hoja de cálculo  y/o en el modelo de datos.



En este ejemplo hemos seleccionado la hoja de calculo.



Al oprimir el botón Cargar se abrirá la hoja con los resultados de la consulta.

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.