Ayúdame a crear una función en Power Query.
En uno de los pasos de la consulta, como resultado, obtengo una lista de fechas. Algunos van secuencialmente, algunos por separado. La cantidad no es fija.
Ejemplo (MM.DD.YYYY):

{01/01/2019, 
 01/02/2019,
 01/03/2019,
 01/05/2019,
 01/06/2019,
 01/08/2019}

Necesito determinar todos los intervalos de fechas consecutivas y reflejar la lista de dichos intervalos. El intervalo se establece por las fechas de inicio y finalización. Si hay una fecha continua, entonces es el principio y el final.
Un ejemplo de los datos anteriores:

{{01/01/2019, 01/03/2019},
 {01/05/2019, 01/06/2019},
 {01/08/2019, 01/08/2019}}

Por favor, ayúdame a escribir una función para resolver este problema.

En mis datos, hay alrededor de 10,000 líneas, cada una de las cuales tiene una lista adjunta hasta 365 días. Es deseable que la función funcione rápidamente.
Parece que list.generate puede ayudar, pero no entiendo muy bien esta función.

0
Den 3 dic. 2019 a las 23:55

2 respuestas

La mejor respuesta

Esta función, que llamé Fechas de análisis , debería hacerlo:

(dateList) =>
let
#"Converted to Table" = Table.FromList(dateList, Splitter.SplitByNothing(), {"Dates"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Dates", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
#"Added Start" = Table.AddColumn(#"Added Index", "Start", each try if #"Added Index"{[Index]-1}[Dates] = Date.AddDays([Dates],-1) then null else [Dates] otherwise [Dates]),
#"Added End" = Table.AddColumn(#"Added Start", "End", each try if #"Added Start"{[Index]+1}[Dates] = Date.AddDays([Dates],1) then null else [Dates] otherwise [Dates]),
#"Added Custom1" = Table.AddColumn(#"Added End", "Group", each "Group"),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Group"}, {{"Start", each List.RemoveNulls([Start]), type anynonnull}, {"End", each List.RemoveNulls([End]), type anynonnull}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Tabled", each Table.FromColumns({[Start],[End]},{"Start","End"})),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Tabled"}),
#"Expanded Tabled" = Table.ExpandTableColumn(#"Removed Other Columns", "Tabled", {"Start", "End"}, {"Start", "End"}),
#"Added Custom3" = Table.AddColumn(#"Expanded Tabled", "Custom", each List.Dates([Start],Number.From([End]-[Start])+1,#duration(1,0,0,0))),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Custom"})
in
#"Removed Other Columns1"

Lo invoqué con esto:

let
Source = #"Parse Dates"(#"Dates List")
in
Source

... contra esta lista, a la que llamé Lista de fechas :

enter image description here

... para obtener este resultado:

enter image description here enter image description here enter image description here enter image description here

1
Marc Pincince 4 dic. 2019 a las 17:42

Logré descubrir cómo usar la función List.Generate para resolver este problema. Esta función funciona un poco más rápido para mí. Lo llamé fn_ListOfDatesToDateRanges. Para invocarlo, debe pasar una columna en cada fila de la cual hay una lista de fechas. La información del blog de KenR me ayudó con el desarrollo. Para comparar el rendimiento, utilicé una matriz con aproximadamente 250 mil líneas. El aumento en la velocidad fue de 45 segundos versus 1 minuto ~ (-33%)

El archivo de prueba con funciones usadas está aquí

    (Dates)=>
let
InputData = List.Transform(List.Sort(Dates,Order.Ascending), each DateTime.Date(DateTime.From(_, "en-US"))),
DateRangesGen = List.Generate(
    ()=> [Date=null, Counter=0], 
    each [Counter]<=List.Count(InputData), 
    each [
Date =
  let
    CurrentRowDate = InputData{[Counter]},
    PreviousRowDate = try InputData{[Counter]-1} otherwise null,
    NextRowDate = try InputData{[Counter]+1} otherwise null,

    MyDate = [Start_Date=
                (if PreviousRowDate = null then CurrentRowDate else
                    if CurrentRowDate = Date.AddDays(Replacer.ReplaceValue(PreviousRowDate,null,0),1) then null else CurrentRowDate), 
             End_Date=(
                        if NextRowDate = null then CurrentRowDate else
                            if CurrentRowDate=Date.AddDays(Replacer.ReplaceValue(NextRowDate,null,0),-1) then null else CurrentRowDate)
             ]
  in
MyDate,
Counter=[Counter]+1],
    each [Date]),
DateRanges = Table.ExpandTableColumn(Table.SelectColumns(Table.AddColumn(Table.Group(Table.AddColumn(Table.ExpandRecordColumn(Table.FromList(DateRangesGen, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"Start_Date", "End_Date"}, {"Start_Date", "End_Date"}), "Group", each "Group"), "Group", {{"Start_Date", each List.RemoveNulls([Start_Date]), type anynonnull}, {"End_Date", each List.RemoveNulls([End_Date]), type anynonnull}}), "Tabled", each Table.FromColumns({[Start_Date],[End_Date]},{"Start_Date","End_Date"})),{"Tabled"}), "Tabled", {"Start_Date", "End_Date"}, {"Start_Date", "End_Date"})
in
DateRanges
1
Den 14 dic. 2019 a las 16:34