Curso Online de Power Query y PowerPivot

miércoles, julio 26, 2017

Excel ha ido desarrollándose a lo largo de los años en un proceso de permanente adaptación a las necesidades de los usuarios. Esto ha convertido a Excel en la herramienta indispensable en un amplio abanico de actividades, economistas, contadores, ingenieros e incluso científicos. Casi todas las ofertas de empleos en estas áreas incluyen el requisito de un buen dominio de Excel.

Hasta la aparición del Power Query y el PowerPivot, un usuario de Excel de buen nivel  era aquel que tenía un buen dominio de las funciones, en particular de extracción de datos como BUSCARV e INDICE, capacidad de generar reportes con tablas dinámicas y ciertos conocimientos de Vba (macros) .

Más de 20 años después de la última gran innovación, las tablas dinámicas, Microsoft hace una nueva revolución en Excel incluyendo dos nuevas herramientas:
  • Power Query: la herramienta ETL (extract-transform-load) para extraer y transformar datos de casi toda fuente de datos imaginable;
  • PowerPivot: las tablas dinámicas llevadas a una nueva dimensión con la capacidad de relacionar distintas  tablas, crear medidas y más.

Todo usuario de Excel que quiera considerarse avanzado debe conocer y dominar estas dos herramientas. Y nada mejor que aprender con el autor del más popular de los manuales de Power Query, el MVP Miguel Escobar, el co-autor de "M is for (Monkey) Data".

Miguel ha montado un curso on line en español que recomiendo para todo usuario que quiera progresar en su capacidad de manejar datos con Excel


Powered Solutions

Los invito a conocer los detalles del curso haciendo un clic sobre el icono.

Seguir leyendo...

Cálculo de lapsos con Power Query

lunes, junio 19, 2017

Comencé a publicar posts sobre Power Query hace casi cuatro años (este fue mi primer post sobre el tema). Desde entonces no sólo he publicado artículos sobre esta herramienta (indispensable para todo quien trabaje con masas de datos) sino que también se ha convertido en mi herramienta principal. Puedo decir que hoy en día la mayoría de las soluciones que desarrollo en Excel se basan, total o parcialmente, en el Power Query.

Hoy voy a mostrar como calcular lapsos, el tiempo transcurrido entre dos instantes, usando el Power Query. Supongamos que tenemos esta serie de mediciones y queremos saber cuánto tiempo ha transcurrido entre cada medición


Podemos hacerlo usando fórmulas de Excel, por supuesto. Pero supongamos que tenemos una gran cantidad de datos (en el caso real que traté había más de 50 mil mediciones) y que tenemos que realizar otras transformaciones por lo que Power Query será la herramienta ideal para el caso.

Como casi todas las notas de este blog ésta está dirigidas al usuario medio o medio-avanzado. Es decir, voy a prescindir de tecnicismos y pondré el énfasis en los aspectos prácticos.

Observando detenidamente el ejemplo podemos ver que hay dos tablas: una contiene una sola fila con la fecha y hora del comienzo de las mediciones y la segunda con las mediciones. Antes de crear las consultas consideremos lo siguiente: el cálculo de cada lapso es el tiempo transcurrido entre una medición y la inmediata anterior; pero el primer lapso es entre la primera medición de la tabla y el punto inicial. Éste dato se encuentra en otra tabla. Para poder usar este dato vamos a hacer lo siguiente:

Cargamos la tabla del punto inicial a una consulta

Con un clic del mouse abrimos la opcíón "Rastrear..." (drill down)

con este resultado


En la ventanilla Propiedades de la consulta cambiamos el nombre a "Inicio" y cerramos la consulta con la opción "sólo crear conexión". Luego cargamos la tabla de mediciones, sobre la cual haremos todos los cálculos


Nótese los diferentes iconos en las consultas, lo que nos indica que Power Query las está tratando de forma distinta. De hecho, la segunda es una tabla (como lo indica el icono) mientras que “Inicio” es una especia de variable (podemos cambiar el valor en la hoja de Excel y el cambio se reflejará en la consulta).

Ahora vamos a editar la consulta de las mediciones donde haremos nuestras cálculos. Empezamos por agregar una columna Índice con base cero

Cambiamos el nombre del último paso a "AgregInd" (nemotécnica para Indice Agregado)


Ésto nos permite trabajar con más facilidad en los próximos pasos.

Ahora agregamos una columna personalizada con esta fórmula


con este resultado

Debemos prestar atención a estos detalles en nuestra fórmula

if [Índice]=0 then[Medicion]-Inicio else [Medicion]-AgregInd[Medicion]{[Índice]-1}

  • usamos directamente Inicio para referirnos a la consulta que contiene el punto de partida, como si estuviéramos usando una variable (o constante a los efectos de la consulta);
  • para referirnos a la fila anterior anteponemos el nombre del paso anterior (AgregInd) al nombre de la columna sobre la cual queremos operara (Medicion, en nuestro caso) y usamos la expresión Indice - 1 entre corchetes para indicar que queremos referirnos a la fila anterior (no voy a entrar aquí en explicaciones sobre los objetos de Power Query, tablas y listas).
Dos detalles par finalizar:

  • eliminamos la columna Indice
  • cambiamos el tipo de dato de la columna Lapso usando la opción Transformar - Detectar tippo de datos
Todo lo que nos queda por hacer es cargar la consulta a una tabla, si así lo queremos, o dejarla como "sólo conexión".

Seguir leyendo...

Cálculo de fecha de finalización de curso

martes, junio 13, 2017

Supongamos que programamos un curso de programación dividido en 10 encuentros/clases. Las clases se dictarán todos los lunes, miércoles y viernes. La pregunta es: ¿cuál es la fecha del último encuentro?

Podemos resolver este problema con facilidad usando la función DIA.LAB.INTL (Excel 2010 y versiones posteriores) pero tomando en cuenta un parámetro oscuramente explicado en la ayuda en línea de la función.


Empezamos por montar nuestro modelo


En las columnas D a H marcamos con el 0 (cero) los días en que se dictarán las clases y con 1 los días de la semana en los que no se dictarán las clases del curso. Ésto parece ir contra la intuición ya que, siendo usuarios consuetudinarios de Excel, asociamos el 0 con el valor FALSO y el 1 con el valor VERDADERO. Pero, como veremos más adelante, la función DIA.LAB.INTL requiere que usemos estos valores.
Como podemos ver, el curso de Programación comprende 10 encuentros (columna C) y se dicta los días Martes y Jueves.

Ahora agregaremos la columna con el cálculo de la fecha de cierre del curso


La fórmula en la celda J3 es =DIA.LAB.INTL(I3,C3-1,CONCAT(D3:H3)&"11")

Antes de seguir adelante aclaremos que la función CONCAT está disponible solamente para los usuarios de Excel 2016 (en Office 365). Usuarios de versiones aneriores deberán usar la función CONCATENAR.

Expliquemos la fórmula



El primer argumento es la fecha inicial; el segundo es el número de días que en nuestro caso es el número de encuentros. El tercer argumento es un valor textual compuesto de 1 y 0 que indican cuales son los días laborales de la semana y cuales no. Este valor textual debe tener siempre siete símbolos (uno por cada día siguiendo el orden de los días de la semana). 
El "truco" en esta fórmula es definir sólo los días en que se dicta el curso como días laborales. 

Opcionalmente podemos agregar una lista de feriados, el cuarto argumento, en caso de ser necesario.

También podemos cambiar el formato de las fechas para que muestren el día de la semana 


Otra mejora posible es usae Formato Numérico Personalizado para mostrar "si" en lugar de 0 y "no" en lugar de 1



con esta definición



o mejor aún con Formato Condicional


definido de esta manera


Seguir leyendo...

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP