none
Appending rows with adjacent time periodes. RRS feed

  • Question

  • I have data with a start and end date. I need to merge overlapping periods and rows where a period begins immediately after another period has ended eg 31-12-2017 and starts 1-1-2018 so they become one row. Only rows for the same ID, name and item is to be joined.

    The desired transformation is illustrated by an example below.

    Example:

    The data below:

    ID    Name    Item          DateStart        DateEnd

    111  Jane      Car            22-12-2017    31-12-2017

    111  Jane      Car            01-01-2018    10-01-2018

    111  Jane      Apartment  01-05-2017    10-01-2018

    222  Alec      Golf cart      01-06-2017    06-06-2017

    222  Alec      Golf cart      04-06-2017    10-06-2017

    222  Alec      Golf cart      12-06-2017    15-05-2017

    should be transformed to:

    ID    Name    Item          DateStart        DateEnd

    111  Jane      Car            22-12-2017    10-01-2018

    111  Jane      Apartment  01-05-2017    10-01-2018

    222  Alec      Golf cart      01-06-2017    10-06-2017

    222  Alec      Golf cart      12-06-2017    15-05-2017

    I think the right place for the transformation is in the query (M language) but a solution in DAX is also welcome.

    Friday, August 16, 2019 8:11 AM

Answers

  • I would recommend this approach:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJR8krMSwVSzolFQNLISN/QSN/IwNAcyDE2hHNidbAqNzDUByKgCgsgx9AAzsFQ7liQWFSSm5pXAtVkCrMDTZORkRFIdU5qMpByz89JU0gGaoRqMoNpArJgHPyaTJA0gWwiRhPQy0iaTBGaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Item = _t, DateStart = _t, DateEnd = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Item", type text}, {"DateStart", type date}, {"DateEnd", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "AllDates", each {Number.From([DateStart])..Number.From([DateEnd])}),
        #"Grouped Rows" = Table.Group(#"Added Custom", {"ID", "Item"}, {{"All", each _, type table [ID=number, Name=text, Item=text, DateStart=date, DateEnd=date, AllDates=list]}, {"Name", each List.Min([Name]), type text}}),
        UnionAllDates = Table.AddColumn(#"Grouped Rows", "GroupDates", each List.Union([All][AllDates])),
        CreateAdjacentLists = 
            Table.AddColumn(
                UnionAllDates, 
                "DividedList", 
                each List.Skip(List.Accumulate(
                    [GroupDates],
                    {{[GroupDates]{0}}},
                    (state, current) =>
                        if current - List.Last(List.Last(state)) = 1 
                        then List.FirstN(state, List.Count(state)-1) & {List.Last(state) & {current}} 
                        else List.FirstN(state, List.Count(state)) & {{current}} ))),
        #"Expanded DividedList" = Table.ExpandListColumn(CreateAdjacentLists, "DividedList"),
        StartEndRecord = Table.AddColumn(#"Expanded DividedList", "Custom", each [DateStart = List.First([DividedList]), DateEnd = List.Last([DividedList])]),
        #"Expanded Custom" = Table.ExpandRecordColumn(StartEndRecord, "Custom", {"DateStart", "DateEnd"}, {"DateStart", "DateEnd"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"DateStart", type date}, {"DateEnd", type date}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"All", "GroupDates", "DividedList"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by soerensj Monday, August 19, 2019 6:13 AM
    Friday, August 16, 2019 11:19 AM
    Moderator

All replies

  • Friday, August 16, 2019 9:11 AM
  • I would recommend this approach:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJR8krMSwVSzolFQNLISN/QSN/IwNAcyDE2hHNidbAqNzDUByKgCgsgx9AAzsFQ7liQWFSSm5pXAtVkCrMDTZORkRFIdU5qMpByz89JU0gGaoRqMoNpArJgHPyaTJA0gWwiRhPQy0iaTBGaYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Item = _t, DateStart = _t, DateEnd = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Item", type text}, {"DateStart", type date}, {"DateEnd", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "AllDates", each {Number.From([DateStart])..Number.From([DateEnd])}),
        #"Grouped Rows" = Table.Group(#"Added Custom", {"ID", "Item"}, {{"All", each _, type table [ID=number, Name=text, Item=text, DateStart=date, DateEnd=date, AllDates=list]}, {"Name", each List.Min([Name]), type text}}),
        UnionAllDates = Table.AddColumn(#"Grouped Rows", "GroupDates", each List.Union([All][AllDates])),
        CreateAdjacentLists = 
            Table.AddColumn(
                UnionAllDates, 
                "DividedList", 
                each List.Skip(List.Accumulate(
                    [GroupDates],
                    {{[GroupDates]{0}}},
                    (state, current) =>
                        if current - List.Last(List.Last(state)) = 1 
                        then List.FirstN(state, List.Count(state)-1) & {List.Last(state) & {current}} 
                        else List.FirstN(state, List.Count(state)) & {{current}} ))),
        #"Expanded DividedList" = Table.ExpandListColumn(CreateAdjacentLists, "DividedList"),
        StartEndRecord = Table.AddColumn(#"Expanded DividedList", "Custom", each [DateStart = List.First([DividedList]), DateEnd = List.Last([DividedList])]),
        #"Expanded Custom" = Table.ExpandRecordColumn(StartEndRecord, "Custom", {"DateStart", "DateEnd"}, {"DateStart", "DateEnd"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"DateStart", type date}, {"DateEnd", type date}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"All", "GroupDates", "DividedList"})
    in
        #"Removed Columns"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by soerensj Monday, August 19, 2019 6:13 AM
    Friday, August 16, 2019 11:19 AM
    Moderator
  • Out of curiosity, how does something end before it starts?

    222  Alec      Golf cart      12-06-2017    15-05-2017

    Friday, August 16, 2019 12:29 PM
  • Should have been 15-06-2017
    Friday, August 16, 2019 1:22 PM
  • Another possibility?

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"Item", type text}, {"DateStart", type date}, {"DateEnd", type date}}),
        groupedRows = Table.Group(changedType, {"ID", "Name", "Item"}, {{"Table", each Table.Split(_, 2), type list}}),
        expandedTable = Table.ExpandListColumn(groupedRows, "Table"),
        replacedStartDate = Table.TransformColumns(expandedTable, {"Table", (i) => Table.ReplaceValue(i, each [DateStart], List.Min(i[DateStart]), Replacer.ReplaceValue, {"DateStart"})}),
        replacedEndDate = Table.TransformColumns(replacedStartDate, {"Table", (i) => Table.ReplaceValue(i, each [DateEnd], List.Max(i[DateEnd]), Replacer.ReplaceValue, {"DateEnd"})}),
        distinctTables = Table.TransformColumns(replacedEndDate, {"Table", each Table.Distinct(_)}),
        combinedTables = Table.Combine(distinctTables[Table])
    in
        combinedTables

    Friday, August 16, 2019 5:07 PM