Inteligencia de Tiempo con Power Query

El Caso

En DAX (El lenguaje de Power Pivot), tenemos funciones como DATEADD que nos brinda las fechas de un previo o próximo año/mes/día. También tenemos otras funciones similares como SAMEPERIODLASTYEAR que nos brinda el período de fechas pero del año anterior. También tenemos otras funciones como TOTALMTD, TOTALQTD & TOTALYTD que nos pueden dar un valor acumulativo basado en un año a la fecha, trimestre hasta la fecha o incluso mes a la fecha. El objetivo de este patrón es mostrar cómo crear esas funciones de una manera elegante y simple, en Power Query con codificación mínima y un gran valor añadido gracias a su interfaz fácil de usar.

Descarga el archivo

Nuestro objetivo

La imagen anterior nos da una idea de lo que queremos lograr. Nota unas cuantas cosas:

  • Sólo tenemos una sola tabla fuente
  • Necesitamos las siguientes calculaciones
    • Obtener los valores de años anteriores
    • Calcular el total acumulado de ventas del mes a la fecha
      • inclusive obtener el mismo valor del año pasado para dicha combinación de día/mes

Hay muchas maneras de lograr esto y este patrón te mostrará algunos ejemplos de cómo hacerlo.

Preparando nuestra Tabla

Antes de saltar en algo, tenemos que asegurarnos de algunas cosas para que nuestro patrón se pueda implementar de forma óptima. He aquí una pequeña lista de las cosas que tenemos que verificar:

  • La Tabla tiene una columna de Fechas
  • La Tabla tiene valores únicos en la columna de Fechas
  • La columna de Fechas necesita llamarse”OrderDate”
    • esta es opciona ya que podemos cambiar el código más adelante para que sea el nombre de la columna que tengas en tu tabla

Si estás teniendo problemas con el segundo punto donde necesitas valores únicos para la columna de fecha, entonces puedes usar el siguiente patrón que te puede ayudar a agrupar las filas utilizando la columna de Fechas.

Agrupar por o Resumir Datos

Utilizando la navegación de Registros de Tabla para obtener Registros específicos

Una de las maneras más fáciles de hacer inteligencia de tiempo es simplemente hacer las operaciones de navegación de tabla. Utilicemos los datos de la tabla Origen y agreguemos una columna personalizada utilizando el siguiente código:

try #"Tipo cambiado"{[OrderDate=Date.AddYears([OrderDate],-1)]} otherwise null

Entendamos este código:

try ‘y’ otherwise = son el equivalente de utilizar SI.ERROR en Power Query donde intentamos hacer una operación donde esperamos que todo salga bien, de otra manera (otherwise ) nos devuelve un valor que definamos. En nuestro caso ese valor en caso de errores es null

#”Tipo cambiado” = es el nombre del paso anterior. Este paso es realmente una tabla, por lo que estamos utilizando una tabla aquí

{ } = anillos definen una lista

[ ] = corchetes definen un registro

Date.AddYears = es una función de Power Query function que agrega o substrae el valor del año de una fecha. Así como esta función tenemos muchas otras como Date.AddDays, Date.AddWeeks, Date.AddMonths y muchas otras más que nos ayudan a movernos en el tiempo ya sea hacia el pasado o hacia el futuro.

Resumiendo, lo que está haciendo esta fórmula es básicamente hacer una lectura de toda la tabla en el paso #”Tipo cambiado” y buscando fila por fila el registro que coincide con el criterio que hemos definido. El criterio es:

  • Bríndame el registro del año anterior
    • Ejemplo: Si en una fila la fecha es 4/Marzo/2014, entonces la lectura irá buscando la fecha 4/Marzo/2013 (gracias a la operación de Date.AddYears)

El resultado de esto es un registro que luce de la siguiente manera: PreviousYearY lo que necesitamos hacer ahora es simplemente expandir dicha columna para obtener el campo Sales, tal vez aplicar un ordenamiento de filas y cambiar el nombre de algunas columnas y nuestro resultado final lucirá así: FinalResultTableNavigation

Replicar el Contexto de Filtro de Power Pivot con Table.SelectRows

En Power Pivot, usando DAX, tenemos la habilidad de poder crear nuestras propias fórmulas de Inteligencia de Tiempo al jugar con el Contexto de Filtro. Vamos a utilizar este concepto de Contexto de FILTRO pero en Power Query.

¿Qué es el Contexto de Filtro? en una respuesta corta y simple, se trata de los filtros que se aplican a una tabla. Vamos a utilizar una función sencilla que nos ahorrará mucho tiempo. Para crear esta nueva función simplemente vamos a Power Query, crear una consulta en Blanco y, en el editor avanzado, simplemente pegamos el siguiente código:

(MyTable,Input) let Step = Table.SelectRows(MyTable, each Number.From([OrderDate]) <= Number.From(Input)) in Step

Clic en Listo cuando termines pegar el código y entonces puedes llamar a esta consulta como quieras. En nuestro caso, optamos por nombrarlo MiFunción.   Lo que esta función está haciendo es bastante simple.

  • Tiene 2 argumentos o parámetros.
  • El primer parámetro es la tabla donde se encuentran los datos
  • El segundo parámetro es la columna de fecha que actuará como un filtro sobre la tabla definida en el primer parámetro.

La operación que sucede en el código, específicamente en la parte:

Step = Table.SelectRows(MyTable, each Number.From([OrderDate]) <= Number.From(Input))

es simplemente un filtro sobre la tabla. Filtra la tabla utilizando la columna [OrderDate] de dicha tabla donde solo devolverá los registros de esa tabla donde [OrderDate] es menor o igual que el valor en el segundo parámetro.

¿Sabías que…Table.SelectRows es la misma operación que sucede al dar clic en el ícono de filtro en una columna?Filtering

Aplicando la Función para un Análisis TotalMTD o Total del mes a la fecha

Ahora que tenemos la función, podemos utilizar en un escenario real. Queremos crear un análisis de Total del Mes a la fecha, pero tenemos muchos meses combinados en nuestra tabla principal. ¿Qué podemos hacer? Podemos agruparlos! Antes de agruparlos, necesitamos unas nuevas columnas que nos ayuden a poder agrupar las fechas. He decidido agregar una columna para Año y Mes simplemente dándole clic a la columna Date, luego yendo a la cinta de Agregar Columna y simplemente dejar que Power Query haga todo el trabajo pesado por mi. He aquí como dicho proceso y su resultado lucen: Ahora que tenemos las columnas que nos pueden ayudar a agrupar las fechas. vamos a seguir adelante y agrupar las filas:    y el resultado de dicha operación nos dará una tabla que luce de la siguiente manera: TableGrouped

Podrás notar que en la columna GroupedRows tenemos todas las filas agrupadas para el respectivo valor de Año y Mes. Lo que ves aquí es un escenario de tablas anidadas donde tenemos una tabla (o una columna de tablas) dentro de una tabla. Lo que necesitamos hacer es simplemente aplicar la función (MiFunción) en dicha columna de tablas. Necesitamos agregar una nueva columna dentro de cada una de dichas tablas. Da clic en el botón de Agregar Columna Personalizada y utiliza el la siguiente fórmula para crear la nueva columna:

=Table.AddColumn( [GroupedRows], "Cume", each MiFunción(#"Filas agrupadas"{[Year=[Year],Month=[Month]]}[GroupedRows], [OrderDate]))

Probablemente podrás ver que también utilizamos la navegación de tablas dentro de dicha fórmula para poder resolver este escenario de tablas anidadas. El resultado de ello debe de lucir de la siguiente manera: CumeColumn

Nuestros próximos pasos

Vamos a seguir adelante y hacer lo siguiente:

  • Quitar la columna GroupedRows
  • Expandir la Columna Personalizada para solo obtener los siguientes campos
    • OrderDate
    • Sales
    • Cume

El resultado debe lucir como la siguiente tabla: Priortoaggegatecume

Agregar las Columnas

Probablemente hayas notado que en cada uno de esos valores tabla, en el campo Cume, tenemos un conjunto de registros donde las fechas iguales o menores a la fecha que tenemos en la columna OrderDate (sin pasarnos a un mes anterior). Estamos acostumbrados a Expandir una columna, pero muy rara vez utilizamos la operación de Agregar los datos que nos brinda Power Query. Vamos a tratar de agregar los datos de las columnas como Power Query sugiere: Como puedes ver, podemos hacer MUCHO…y quiero decir MUCHÍSIMAS operaciones sobre estas columnas. Podríamos hacer un promedio sobre todos los registros de estas fechas o tal vez el equivalente a AVERAGEMTD, MAXMTD, MINMTD, MEDIANMTD o un simple conteo. Hemos decidido hacer solo una Suma de la columna Sales y luego de hacer un ordenamiento, junto con unos cambios de nombres, nuestro resultado luce de la siguiente manera: TotalMTDComo puedes ver, nuestro TotalMTD o Acumulado de Mes a Fecha está trabajando correctamente.

Final Scenario: Combine all the concepts learned

Combinemos los conceptos de la primera consulta y de la segunda. Démosle un clic derecho a la última consulta y seleccionemos la opción de “referenciar” para crear una nueva consulta que va a referenciar a este seleccionada. A mi consulta anterior la nombré Acumulada y esto es lo que verás en la barra de fórmula como tu primer paso para tu nueva consulta referenciada.

Ahora, en vez de solo hacer eso, envolvamos ese nombre, Acumulada, dentro de la función Table.Buffer para optimizar el rendimiento de la consulta. Así nuestro primer paso de esta nueva consulta lucirá de la siguiente manera en la barra de fórmula:

= Table.Buffer(Accumulate)

Ahora, como ya conoces, el nombre de nuestro primer paso en esta nueva consulta es Origen. 

Vamos a crear una nueva columna personalizado con el nombre Last Year MTD (MTD del año pasado) y agreguemos la siguiente fórmula para la creación de esta columna:

try Origen{[OrderDate=Date.AddYears([OrderDate], -1)]}[MTD] otherwise null

y he aquí el resultado: FinalResult Nota: estamos extrayendo un registro y luego navegando dentro de él hasta llegar a un campo específico, dentro de ese registro, llamado MTD y es por eso que el resultado de la fórmula es un valor y no un registro (Record). Podíamos haber escrito el código sin el [MTD] al final y así tener un registro completo y extraer otros campos de dicho registro. Inténtalo!

Patrones para Power Query
Taller de Power Query

este trabajo ha sido compartido 0 veces
 400
Sobre

 Power Query Training

  (13 Artículos)

Somos un equipo comprometido en ofrecer el mejor contenido para los nuevos y experimentados usuarios de una herramienta en la que creemos: Power Query. Estamos firmemente convencidos de que esta es la mejor herramienta que ha sido agregada a Microsoft Excel en un largo tiempo.

  • Arturo Orantes

    que sucede si tengo dos criterios para sumar las ventas anteriores , el mes y el id del producto ademas si deseo poner las vtas del periodo pasado en una nueva columna para que sea el saldo inicial, que dedo hacer

    • Hola Arturo!

      En ese caso sería necesario agrupar por ambos campos en el paso de agrupar por.

      SaludOs!

  • eladio gonzalez bardelli

    Cual es la diferencia en objetivos entre powerquery vs powerpivot ?

    • Hola Eladio!

      Power Query = para extracción, transformación, consolidación, fusión y ciertas agregaciones simples de datos.
      Power Pivot = creación de un modelo analítico en forma tabular para hacer agregaciones de todo tipo hasta muy complejas.

      Espero esto te ayude!

      Saludos

  • Christian Calderon

    Buenos dias, quisiera saber como aplicar en PowerQuery una fórmula como la siguiente =SI(Y(L2=”Activo”,Q2=HOY()-365),”No Activo”,SI(Y(L2=”Retirado”,U2=HOY()-365),”Activo”,L2))))

    • Hola Christian,

      Puedes utilizar la característica de “Columna Condicional” dentro de Power Query. Para el escenario que planteas, necesitarías crear una columna personalizada para poder tener mayor control sobre las condiciones que necesitas. La fórmula que debes de crear debería de utilizar las funciones “if” y “and” de Power Query.

      La fórmula luciría algo como esto:

      if [columna1] = “Activo” and [columna2] <= DateTime.LocalNow()-365 then "Activo" else if …. . . … . … then "Retirado"

      Te recomiendo descargar el siguiente PDF creado por el equipo de Microsoft para conocer más sobre estas funciones http://pqreference.azurewebsites.net/PowerQueryFormulaLanguageSpecificationAugust2015.pdf
      Lamentablemente no existe material oficial de Microsoft para este tipo de escenarios en ningún idioma que no sea el Inglés, pero este pdf contiene todo lo que necesitas saber sobre dichas funciones.

      • Christian Calderon

        Muchas gracias estimado Miguel Angel