Sign in
 

none
Appending rows with adjacent time periodes. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Gil Raviv described some techniques on this matter recently:

    https://datachant.com/2019/07/22/find-overlapping-time-periods-using-powerquery/

    https://datachant.com/2019/07/31/find-overlapping-time-periods-using-powerquery-3/

    Hope, this helps.

    Friday, August 16, 2019 9:11 AM
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    Should have been 15-06-2017
    Friday, August 16, 2019 1:22 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |