Forum Discussion

CSRiotech's avatar
CSRiotech
Frequent Visitor
3 years ago
Solved

Split rows based on start & end date

Suppose I have following data: 

ProductStart DateEnd Date
131 October 202125 December 2021
231 October 202110 January 2022
321 October 202115 February 2023

 

But I want to split the rows in which time period between 'Start Date' & 'End Date' spans more than one year(They are recognised Date variables). The table above would then be changed to:

 

ProductStart Date End Date
131 October 202125 December 2021
231 October 202131 December 2021
21 January 202210 January 2022
321 October 202131 December 2021
31 January 202231 December 2022
31 January 202315 February 2023

 

How can I do this in powerBI? Any help would be much appriciated! 

Best regards

Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    CSRiotech You can use this:

     

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WMlTSUTI2VPBPLslPSi1SMDIwAokYmSq4pCan5sKFYnWilYywKjU0UPBKzCtNLKoEiRiBVRqDjMBUaarglppUBFNqrBQbCwA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Product = _t, #"Start Date" = _t, #"End Date" = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        { { "Product", Int64.Type }, { "Start Date", type date }, { "End Date", type date } }
    ),
    Result = Table.AddColumn (
        Source,
        "Repeat",
        ( CurrentRow ) =>
            let
                YearCount = 
                    Date.Year ( CurrentRow[End Date] )
                        - Date.Year ( CurrentRow[Start Date] )
                        + 1,
                GenerateDates = 
                    List.Generate (
                        () =>  [
                            Start = CurrentRow[Start Date],
                            End   = List.Min ( { CurrentRow[End Date], Date.EndOfYear ( Start ) } ),
                            Stop  = CurrentRow[End Date]
                        ],
                        each [Start] <= [Stop],
                        each [
                            Start = Date.StartOfYear ( Date.AddYears ( [Start], 1 ) ),
                            End   = List.Min ( { [Stop], Date.EndOfYear ( Start ) } ),
                            Stop  = [Stop]
                        ],
                        each { CurrentRow[Course], [Start], [End]  }
                    ),
                Result = 
                    Table.FromRows ( 
                        GenerateDates, 
                        type table [Course = text, Start Date = date, End Date = date] 
                    )
            in
                Result,
        type table
    ),
    Combined = Table.Combine ( Result[Repeat] )
in
    Combined

     

     

     

1 Reply

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    CSRiotech You can use this:

     

    let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WMlTSUTI2VPBPLslPSi1SMDIwAokYmSq4pCan5sKFYnWilYywKjU0UPBKzCtNLKoEiRiBVRqDjMBUaarglppUBFNqrBQbCwA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [ Product = _t, #"Start Date" = _t, #"End Date" = _t ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        { { "Product", Int64.Type }, { "Start Date", type date }, { "End Date", type date } }
    ),
    Result = Table.AddColumn (
        Source,
        "Repeat",
        ( CurrentRow ) =>
            let
                YearCount = 
                    Date.Year ( CurrentRow[End Date] )
                        - Date.Year ( CurrentRow[Start Date] )
                        + 1,
                GenerateDates = 
                    List.Generate (
                        () =>  [
                            Start = CurrentRow[Start Date],
                            End   = List.Min ( { CurrentRow[End Date], Date.EndOfYear ( Start ) } ),
                            Stop  = CurrentRow[End Date]
                        ],
                        each [Start] <= [Stop],
                        each [
                            Start = Date.StartOfYear ( Date.AddYears ( [Start], 1 ) ),
                            End   = List.Min ( { [Stop], Date.EndOfYear ( Start ) } ),
                            Stop  = [Stop]
                        ],
                        each { CurrentRow[Course], [Start], [End]  }
                    ),
                Result = 
                    Table.FromRows ( 
                        GenerateDates, 
                        type table [Course = text, Start Date = date, End Date = date] 
                    )
            in
                Result,
        type table
    ),
    Combined = Table.Combine ( Result[Repeat] )
in
    Combined