Answered by:
Appending rows with adjacent time periodes.

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
All replies
-
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 -
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 -
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-2017Friday, 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