¿Cómo extraer números de meses con años entre la fecha de inicio y la fecha de finalización?

Hola tios,

Quería extraer los números de mes con años (en power query, no en DAX) entre una fecha de inicio y una fecha de finalización determinadas y no quiero las fechas intermedias, sino solo el mes y los años extraídos juntos.

Por ejemplo, me han dado startdate = «07/01/2018» enddate = «» 06/30/2021 «(tengo muchas otras entradas como esta pero con otras combinaciones)

Inicialmente pensé en usar Date.Month (Startdate) .. Date.Month (enddate), pero luego me di cuenta de que esta función solo funciona cuando los años de las fechas de inicio y las fechas de finalización son los mismos. Entonces, cuando los años cambian como en mi, por ejemplo, esta función deja de funcionar.

Entonces, para extraer meses usando esta función, tuve que crear cuatro columnas diferentes y también usé la función list.transform para agregar el año con el valor del mes. Se ha hecho de la siguiente manera:

columna 1: extrajo los meses y el año (2018) entre la fecha de inicio y el final del año 2018

columna 2 – extraído los meses y el año (2019) entre el 1 de enero de 2019 y el 31 de diciembre de 2019

columna 3 – extraído los meses y el año (2020) entre el 1 de enero de 2020 y el 31 de diciembre de 2020

columna 4: extrajo los meses y el año (2021) entre el 1 de enero de 2021 y la fecha de finalización

La función utilizada aquí es (este código es para la columna 1, de manera similar escribí para otras columnas codificando el valor de las fechas) –

List.Transform (
{Fecha.Mes ([StartDate0]) .. Fecha.Mes (Fecha.EndOfYear ([StartDate0]))},
cada Number.ToText (_) & «/ 2018»)

También utilicé muchas condiciones if-else con cada una de ellas para dar cuenta de otras combinaciones. Luego, finalmente combiné todas las listas en una y luego amplié la lista final.

Por el momento, mi trabajo está hecho y funciona, pero sé que esta solución es temporal, ya que implica una gran cantidad de codificación y solo puede tener en cuenta entradas específicas.

Entonces, ¿hay alguna otra forma mejor de resolver este problema? ¡He estado luchando con esto desde los últimos días!

Cualquier tipo de ayuda será muy apreciada.

Gracias,

Anchal

Hola @ anchal5335

Gracias a la solución de ChrisMendoza, hice una prueba y descubrí que puede cumplir con sus requisitos:

1.Extraiga los números de mes con años (en Power query no DAX) entre una fecha de inicio y una fecha de finalización determinadas

2. por ejemplo, para un investigador, startdate = «07/01/2018» enddate = «» 06/30/2021 «, solo obtenga 36 filas para este investigador.

Atentamente

Maggie

Hola @ChrisMendoza y Maggie,

Como también soy nuevo en la codificación, todavía no puedo entender algunos pasos. Entiendo que primero extrajimos las fechas intermedias y luego expandimos esa columna, pero lo que está sucediendo después de eso no me queda muy claro. Para ser precisos, los pasos que no entendí son:

#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),
#"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Mo-Yr"}, {"Mo-Yr"}),
Custom1 = Table.Group(#"Expanded Count1",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),
Custom2 = Table.Join(#"Expanded Count1","Researcher",#"Custom1","Researcher"),

Sería genial si también pudiera explicarme el propósito de estos pasos para que yo también pueda aplicar en consecuencia. ¡Muchas gracias a toda tu ayuda!

Saludos,

Anchal

@ anchal5335,

Claro, dejé el código en un lío, ¿no? Creo que la parte que me confunde es que yo expandido y luego agrupado de nuevo. # «Expanded Count1» en realidad no es necesario y el nuevo código podría mostrar un poco mejor lo que realmente está sucediendo.

Espero que esta explicación ayude.

    // At #Removed Duplicates we have 1 table with 49 rows
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

    // #Grouped Rows" takes the 1 table of 49 rows and makes 3 tables by "Researcher"https://community.powerbi.com/t5/Desktop/How-to-extract-month-numbers-with-years-between-start-date-and/td-p/#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),

    // Custom1 is essentially doing the same thing as #"Grouped Rows" however, this time it is counting the number of records
    // Notice that I'm using the #"Removed Duplicates" step - where 1 table with 49 rows 
    Custom1 = Table.Group(#"Removed Duplicates",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),

    // Custom2 is then joining #"Grouped Rows" and "Custom1"
    // This is so I can get the row count for each "Researcher" to later do the division in the row context
    Custom2 = Table.Join(#"Grouped Rows","Researcher",#"Custom1","Researcher"),

Código completo en Spoiler:

Revelación

dejar
Consulta1 = #tabla (
tabla de tipos
[
#»Researcher» = text,
#»StartDate» = date,
#»EndDate»= date,
#»Budget» = number
],
{
{«Persona 1», # fecha (2018,7,1), # fecha (2021,6,30), 5000},
{«Persona 2», # fecha (2018,7,1), # fecha (2018,7,26), 5000},
{«Persona 3», # fecha (2018,6,1), # fecha (2019,5,31), 5000}
}
),
DatesBetween = Table.AddColumn (Consulta1, «DatesBetween», cada List.Transform ({Number.From ([StartDate]) .. Número.Desde ([EndDate])}, cada Fecha. Desde (_))),
# «Expanded DatesBetween» = Table.ExpandListColumn (DatesBetween, «DatesBetween»),
# «Changed Type» = Table.TransformColumnTypes (# «Expanded DatesBetween», {{«DatesBetween», type date}}),
# «Agregado personalizado» = Table.AddColumn (# «Tipo cambiado», «Mes-año», cada Text.Combine ({Text.From (Number.From (Date.Month ([DatesBetween]))), Texto.Desde (Número.Desde (Fecha.Año ([DatesBetween])))}, «-«)),
# «Removed Other Columns» = Table.SelectColumns (# «Agregado personalizado», {«Investigador», «Presupuesto», «Mes-año»}),

// En #Removed Duplicates tenemos 1 tabla con 49 filas
# «Duplicados eliminados» = Table.Distinct (# «Otras columnas eliminadas»),

// #Grouped Rows «toma la tabla 1 de 49 filas y crea 3 tablas por» Investigador «
# «Filas agrupadas» = Table.Group (# «Duplicados eliminados», {«Investigador», «Presupuesto»}, {{«Recuento», cada _, tipo tabla}}),

// Custom1 esencialmente hace lo mismo que # «Grouped Rows», sin embargo, esta vez está contando el número de registros
// Observe que estoy usando el paso # «Duplicados eliminados», donde 1 tabla con 49 filas
Custom1 = Table.Group (# «Duplicados eliminados», {«Investigador»}, {{«CountOfPeriods», cada Table.RowCount (Table.Distinct (_)), tipo número}}),

// Custom2 se une a # «Filas agrupadas» y «Custom1»
Custom2 = Table.Join (# «Filas agrupadas», «Investigador», # «Custom1», «Investigador»),

# «Agregado Custom1» = Table.AddColumn (Custom2, «Presupuesto asignado», cada [Budget]/[CountOfPeriods]),
# «Changed Type1» = Table.TransformColumnTypes (# «Custom1 agregado», {{«Presupuesto asignado», número de tipo}}),
# «Columnas eliminadas» = Table.RemoveColumns (# «Tipo1 modificado», {«Presupuesto», «CountOfPeriods»}),
# «Recuento expandido» = Table.ExpandTableColumn (# «Columnas eliminadas», «Recuento», {«Mes-Yr»}, {«Count.Mo-Yr»})
en
# «Recuento expandido»

@ anchal5335,

Sin embargo, no estoy seguro de si este es el resultado deseado, puede hacer lo siguiente, que parece capaz de manejar diferentes StartDates y EndDates:

let
    Query1 = #table(
 type table
    [
        #"StartDate" = date, 
        #"EndDate"= date
    ], 
 {
  {#date(2018,7,1),#date(2021,6,30)},
  {#date(2018,7,1),#date(2018,7,26)}
 }
),
    DatesBetween = Table.AddColumn(Query1, "DatesBetween", each List.Transform({Number.From([StartDate]) .. Number.From([EndDate])}, each Date.From(_))),
    #"Expanded DatesBetween" = Table.ExpandListColumn(DatesBetween, "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Mo-Yr", each Text.Combine({Text.From(Number.From(Date.Month([DatesBetween]))),Text.From(Number.From(Date.Year([DatesBetween])))},"-"))
in
    #"Added Custom"

Hola ChrisMendoza,

Muchas gracias por tu respuesta. ¡Apreciado!

También pensé en esta salida, pero el problema aquí es que no quiero que cada fila se divida en fechas diferentes. Esto es porque-

a) También tengo dax y otros cálculos de consulta de energía que ya se están realizando sobre la base de que las filas se dividen en valores mensuales. Si divido cada fila en un número de fechas intermedias, las otras fórmulas también serán complejas

b) Mi conjunto de datos es grande, por lo que dividir cada fila en un número de fechas intermedias lo haría aún más grande

Entonces, ¿no hay alguna forma de extraer directamente mes y año sin dividir en fechas?

@ anchal5335,

También lo es la expectativa de que terminaría con 36 filas en mi conjunto de datos de ejemplo; valores únicos para cada combinación de mes y año? Supongo que no entiendo completamente qué es lo que estás tratando de lograr.

Sí, tiene usted razón. Me gustaría dividirlo en 36 filas dependiendo solo del no. de meses entre la fecha de inicio y la de finalización. Entonces, en ese caso, cada fila debe contener el número de mes único con el año para esa entrada en particular.

¿Hay alguna forma de lograrlo?

@ anchal5335,

¿Hay otros datos en esta consulta / tabla en los que he realizado esta expansión o esta consulta / tabla es un conjunto de datos independiente? Mi suposición es que debe haber (otros datos) ya que eliminar los duplicados y las otras columnas no resolvió su problema.

Comparta esos otros detalles para que la comunidad pueda intentar ayudar.

Entonces, es como si cada investigador que visita mi organización tuviera una fecha de inicio y una fecha de finalización con un presupuesto determinado asignado a cada uno para la duración de su estadía. Hay aproximadamente 100 de estos investigadores. Entonces, si dividimos en fechas intermedias, se dividirá para cada investigador individualmente. Ahora, algunos de estos investigadores provienen del 07/2018 al 06/2021 (el ejemplo inicial que escribí), del 06/2018 al 05/2019, etc. (básicamente, esta duración de tiempo varía para cada investigador). Así que quería dividir la fecha de inicio y la fecha de finalización en meses / año para cada investigador.

@ anchal5335,

Entonces, quitando duplicados de [Mo-Yr] & Quitar las columnas auxiliares adicionales no es una opción?

Parece que mi solución es dinámica con solo codificar de forma rígida el inicio y el final de los períodos para cada uno de los que deben rastrearse y luego dejar que Power Query haga el trabajo, si lo anterior es una opción.

Desafortunadamente, no sé cómo mejorar su situación si lo anterior no es una opción.

Para divertirme, asigné dinámicamente un [Budget] sobre el número de períodos que se generaron para cada [Researcher]. Por ejemplo,

let
    Query1 = #table(
 type table
    [
        #"Researcher" = text,
        #"StartDate" = date, 
        #"EndDate"= date,
        #"Budget" = number
    ], 
 {
  {"Person 1",#date(2018,7,1),#date(2021,6,30),5000},
  {"Person 2",#date(2018,7,1),#date(2018,7,26),5000},
  {"Person 3",#date(2018,6,1),#date(2019,5,31),5000}
 }
),
    DatesBetween = Table.AddColumn(Query1, "DatesBetween", each List.Transform({Number.From([StartDate]) .. Number.From([EndDate])}, each Date.From(_))),
    #"Expanded DatesBetween" = Table.ExpandListColumn(DatesBetween, "DatesBetween"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded DatesBetween",{{"DatesBetween", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Mo-Yr", each Text.Combine({Text.From(Number.From(Date.Month([DatesBetween]))),Text.From(Number.From(Date.Year([DatesBetween])))},"-")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Researcher", "Budget", "Mo-Yr"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Researcher", "Budget"}, {{"Count", each _, type table}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Mo-Yr"}, {"Mo-Yr"}),
    Custom1 = Table.Group(#"Expanded Count1",{"Researcher"}, {{"CountOfPeriods", each Table.RowCount(Table.Distinct(_)),type number}}),
    Custom2 = Table.Join(#"Expanded Count1","Researcher",#"Custom1","Researcher"),
    #"Added Custom1" = Table.AddColumn(Custom2, "Allocated Budget", each [Budget]/[CountOfPeriods]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Allocated Budget", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Budget", "CountOfPeriods"})
in
    #"Removed Columns"

La «Persona 1» tiene períodos de 36 meses a año, por lo que [Allocated Budget] debe ser 138,889 por período

«Persona 2» tiene un período de 1 mes-año, por lo que [Allocated Budget] debería ser 5000 por período

«Persona 3» tiene un período de 12 meses-año, por lo que [Allocated Budget] debería ser 416,667 por período

Por supuesto, Power Query hizo el trabajo real por mí.

Entradas relacionadas

Deja un comentario Cancelar respuesta