Split delimited date into new rows

Anonymous

Hi, is not the best solution but will give you a start way.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYtNC8IwDIb/Ssl50GQytWcnKHjTW+mhuoIF147aHfz3JmWXN3neD2vhUeKi0jo/Q4EObvnla8yJ3/O8fPIvBHUdme7Vl6pGX4NEaVKTvK6zQIjE3iWv39qG/dBEE+ke6SCAGhsct8WA7J7eMXm+xrBwTiiV/dbfCRhw7g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Trip number", Int64.Type}, {"Location", type text}, {"Employee ID", Int64.Type}, {"Start Date", type date}, {"End date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Dates", each List.Dates([Start Date],Duration.Days([End date]-[Start Date])+1,#duration(1, 0, 0, 0))),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Inserted Year" = Table.AddColumn(#"Expanded Dates", "Year", each Date.Year([Dates]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Year",{"Start Date", "End date"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Trip number", "Location", "Employee ID", "Year"}, {{"StartDate", each List.Min([Dates]), type date}, {"EndDate", each List.Max([Dates]), type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Trip number"},#"Grouped Rows",{"Trip number"},"Grouped Rows",JoinKind.LeftOuter),
    #"Aggregated Grouped Rows" = Table.AggregateTableColumn(#"Merged Queries", "Grouped Rows", {{"StartDate", List.Min, "Min of StartDate"}}),
    #"Added Custom1" = Table.AddColumn(#"Aggregated Grouped Rows", "Part no", each Date.Year([StartDate]) - Date.Year([Min of StartDate]) + 1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Min of StartDate"})
in
    #"Removed Columns1"

Regards

Victor