请帮助我在Power Query中创建一个函数。
结果，在查询的其中一个步骤中，我得到了一个日期列表。有些顺序地走，有些分开地走。数量不固定。
示例（MM.DD.YYYY）：
{01/01/2019,01/02/2019,01/03/2019,01/05/2019,01/06/2019,01/08/2019}
我需要确定连续日期的所有间隔，并反映这些间隔的列表。时间间隔由开始日期和结束日期设置。如果有一个连续的日期，那么它就是开始和结束。
来自先前数据的示例：
{{01/01/2019,01/03/2019},{01/05/2019,01/06/2019},{01/08/2019,01/08/2019}}
请帮助我编写一个函数来解决此问题。
在我的数据中，大约有10,000行，每行都有一个长达365天的列表。希望该功能快速运行。
感觉list.generate可以提供帮助，但是我对这个功能不太了解。
此函数（我称为解析日期）应该执行以下操作：
(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",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","End"},"End"}),#"Added Custom3" = Table.AddColumn(#"Expanded Tabled","Custom",each List.Dates([Start],Number.From([End]-[Start])+1,#duration(1,0))),#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom3",{"Custom"})
in
#"Removed Other Columns1"
我用这个调用它：
let
Source = #"Parse Dates"(#"Dates List")
in
Source
...针对此列表，我将其称为日期列表：
...获得此结果：
我设法弄清楚如何使用List.Generate函数来解决此问题。 这个功能对我来说快一点。 我称它为fn_ListOfDatesToDateRanges。 要调用它，必须在每一行中传递一个列，该列中有一个日期列表。 Information from the KenR blog helped me with development. 为了比较性能，我使用了大约25万行的数组。速度增加了45秒，而速度增加了1分钟〜（-33％）
Test file with used functions is here
(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,0),1) then null else CurrentRowDate),End_Date=(
if NextRowDate = null then CurrentRowDate else
if CurrentRowDate=Date.AddDays(Replacer.ReplaceValue(NextRowDate,-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,"Column1",{"Start_Date","End_Date"},"End_Date"}),{{"Start_Date",each List.RemoveNulls([Start_Date]),{"End_Date",each List.RemoveNulls([End_Date]),each Table.FromColumns({[Start_Date],[End_Date]},"End_Date"})),"End_Date"})
in
DateRanges