Convertir fechas que abarcan varios meses en varios registros

[Andrew17]

Hola

Tengo algunos datos que muestran la fecha de inicio y finalización de un proceso, pero me preguntaba si había una manera de dividir estas fechas en intervalos mensuales. Para poder conseguir una línea por cada mes.

Al igual que con los datos de muestra a continuación:

ID único	Fecha de inicio	Fecha de finalización
AA	2020-05-05	2020-06-05
Desactivado	2020-04-03	2020-06-19
Ca	2020-05-15	2020-05-25

Se convertiría en:

ID único	Fecha de inicio	Fecha de finalización
AA	2020-05-05	2020-05-31
AA	2020-06-01	2020-06-05
Desactivado	2020-04-03	2020-04-30
Desactivado	2020-05-01	2020-05-31
Desactivado	2020-06-01	2020-06-19
Ca	2020-05-15	2020-05-25

Me preguntaba si hay una facilidad para hacer esto en Power Query o DAX? Quiero tratar de evitar hacer esto en Excel o VBA.

[Fowmy]

@Andrew17

Por favor, encuentre adjunto el archivo que divide la fecha en rangos según lo requiera.

Puede descargar el archivo: HERE

He creado una función para proporcionar la fecha de inicio y finalización, a continuación, le dará el rango en el medio, entonces se trata de expandirse en filas.
Más sobre la división de fechas en mi blog: https://excelfort.com/allocate-amount-monthly-excel-powerquery/

let
    Source = (pStart as date, pEnd as date) =>
let

Source = List.Generate(
                ()=> [fDate = pStart, fDay= if Date.EndOfMonth(fDate) < pEnd then Date.EndOfMonth(fDate) else pEnd ],      
                each [fDate] <= pEnd,
                each 
                let EoM = Date.EndOfMonth( Date.AddMonths([fDate],1)) in 
                [fDate= Date.AddDays(Date.EndOfMonth([fDate]),1) , fDay= if EoM > pEnd then pEnd else Date.EndOfMonth(fDate)]       
        ),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"fDate", type date}, {"fDay", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"fDate", "Date Start"}, {"fDay", "Date End"}})
    in
        #"Renamed Columns"
in

Source

________________________

¿He respondido a tu pregunta? Marque este post como una solución, esto ayudará a otros!.

Haga clic en el icono Thumbs-Up a la derecha si le gusta esta respuesta 🙂

YoutubeLinkedin

[ImkeF]

Hola @Fowmy

no parece que sus resultados sean iguales a la solicitud.

Para @Andrew17 ,

He creado una función hace algún tiempo que crea este tipo de intervalos: https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-...

Solo devolveré la fecha de finalización de cada mes (u otro intervalo que necesite).

Pero las otras columnas se pueden resolver con bastante facilidad:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyMjAy0DUwBSI4xwzEidUBKnCCi5noGhgjKTC0hChwRjLB0BSJYwQ0IRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Start Date" = _t, #"End Date " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Start Date", type date}, {"End Date ", type date}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnDatesBetwen", each fnDatesBetwen([Start Date], [#"End Date "], "Month")),
    #"Expanded fnDatesBetwen" = Table.ExpandListColumn(#"Invoked Custom Function", "fnDatesBetwen"),
    AddStartOfMonth = Table.AddColumn(#"Expanded fnDatesBetwen", "StartOfMonth", each Date.StartOfMonth([fnDatesBetwen])),
    #"Inserted Latest" = Table.AddColumn(AddStartOfMonth, "Start", each List.Max({[StartOfMonth], [Start Date]}), type date),
    #"Inserted Earliest" = Table.AddColumn(#"Inserted Latest", "End", each List.Min({[#"End Date "], [fnDatesBetwen]}), type date),
    Cleanup = Table.SelectColumns(#"Inserted Earliest",{"Unique ID", "Start", "End"})
in
    Cleanup

También encontrará el archivo adjunto.

[Fowmy · Accepted solution]

Gracias, @ImkeF por resaltarlo, de hecho, he aprendido mucho de ti.
De alguna manera me perdí verificarlo a fondo.

He creado esta función para traer el intervalo de fechas y funciona bien. De hecho, https://excelfort.com/allocate-amount-monthly-excel-powerquery/ he https://excelfort.com/allocate-amount-monthly-excel-powerquery/

let
    Source = (pStart as date, pEnd as date) =>
let

Source = List.Generate(
                ()=> [fDate = pStart, fDay= if Date.EndOfMonth(fDate) < pEnd then Date.EndOfMonth(fDate) else pEnd ],      
                each [fDate] <= pEnd,
                each 
                let EoM = Date.EndOfMonth( Date.AddMonths([fDate],1)) in 
                [fDate= Date.AddDays(Date.EndOfMonth([fDate]),1) , fDay= if EoM > pEnd then pEnd else Date.EndOfMonth(fDate)]       
        ),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"fDate", "fDay"}, {"fDate", "fDay"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"fDate", type date}, {"fDay", type date}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"fDate", "Date Start"}, {"fDay", "Date End"}})
    in
        #"Renamed Columns"
in

Source

Gracias de nuevo

[Greg_Deckler]

@Andrew17 - Claro, puede GENERATESERIES y UNION para hacerlo en DAX. Estoy seguro de que hay una manera de hacerlo en Power Query demasiado @ImkeF .