cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Split delimited date into new rows

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 numberLocationEmployee IDStart DateEnd date 
1001Houston2525/11/201720/01/2018 
1050China9901/10/201620/03/2019 
      
      
      
final table     
Trip numberLocationEmployee IDStart DateEnd datepart no
1001Houston2525/11/201731/12/20171
1001Houston2501/01/201820/01/20182
1050China9901/10/201631/12/20161
1050China9901/01/201731/12/20172
1050China9901/01/201831/12/20183
1050China9901/01/201920/03/20194
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: 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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: 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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




johnmu Frequent Visitor
Frequent Visitor

Re: Split delimited date into new rows

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