Split delimited date into new rows

[Anonymous]

Hello,

I need to split my data so that I can easily use it in my reports.

I have a trip table where some long term expats can have a trip that will last through some years. I need to split this trip generating new lines for it trip / year, any suggestion?

initial table
Trip number	Location	Employee ID	Start Date	End date
1001	Houston	25	25/11/2017	20/01/2018
1050	China	99	01/10/2016	20/03/2019
final table
Trip number	Location	Employee ID	Start Date	End date	part no
1001	Houston	25	25/11/2017	31/12/2017	1
1001	Houston	25	01/01/2018	20/01/2018	2
1050	China	99	01/10/2016	31/12/2016	1
1050	China	99	01/01/2017	31/12/2017	2
1050	China	99	01/01/2018	31/12/2018	3
1050	China	99	01/01/2019	20/03/2019	4

[johnmu]

Hey, I don't think I found the most elegant solution, but it'll work.

First I Invoked a Date Table to cover the range of dates you need, and set a column with the start of the year for the associated date. You didn't specify, but that's where it looks like you want to generate new rows right? When the start of year is between the start date end the end date?

Then I joined the trip data with that invoked date table

Joined Table 2 = CROSSJOIN('InvokedDate',Table1)

I made the following column to identify rows that you want to keep, because they are either the start of a year or the start date

Split = IF(OR([Date]=[Start Date],[Date]=[Start of Year]),1,BLANK())

Then I got ride of dates outside the relevant range

Outside Range = IF(OR([Date]<[Start Date],[Date]>[End Date]),1,0)

Then I numbered the sequene using DATEDIFF

Partno = 


IF([Start Date]=[Date],1,
    IF((DATEDIFF([Start Date],[Date],YEAR))>0,
    (DATEDIFF([Start Date],[Date],YEAR))+1,0)
)

If you filter so that the Split is 1, Outside Range is 0, and Partno is not 0, you should get the table you want.

See the .pbix file here 

https://drive.google.com/open?id=1HYvuF0hapfo3LdEU-oBh71PbZjJI63cD 

[Vvelarde · Accepted solution]

@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