Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 th...
Tips and Tricks
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    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

johnmu's avatar
johnmu
Icon for Helper I rankHelper I
8 years ago

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