Appending rows with adjacent time periodes.

All replies

• 

  

  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.

  • Edited by Aleksei Zhigulin Friday, August 16, 2019 9:14 AM

  Friday, August 16, 2019 9:11 AM
• 

  

  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
• 

  

  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
• 

  

  0

  Sign in to vote

  Should have been 15-06-2017

  Friday, August 16, 2019 1:22 PM
• 

  

  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