Forum Discussion
Consecutively dates. Merge multiple lines to one based on multiple criterias
i have an issue that i can't solve myself.
My data table looks like this. I have a lot of rows that determines the work period for each employee. There are scenarios where an employee has several rows, but the dates are consecutive - so in these cases i want to tell my data model that these should be combined into 1 row, that indicates the start and end date for that trip.
Data Table:
Illustration of my issue / Request:
Hi micjensen ,
According to your description, my suggestion is to implement it in power query.
(1)This is my test data.
(2)Add the following code in the advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndHNCsIwDADgVxk9D9pm/T2qLyB4HDsUV7zMDjp9fzPcTw9Oth4KCSEfTVLXxBptaSUqZUhJzj68ilNoo3cDpjcXWh8fLraYcHySgqbAADA2VHzDpjyoGCpXhNtMxVI7K0uYgXC2KtxkMFNr8hlg2QzwhJHZQ4FKmJyhRgY3w8XsjGtKIaYEQkJqrF36+PRd0fUBk2vXD3cX3xOilraSaMohg8C26vel9wp/rnyA2LrwXmJzn80H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Payroll No." = _t, #"First Name" = _t, Surname = _t, #"Sum of Count" = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Sum of Count", Int64.Type}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type2",{{"Start Date", type date}, {"End Date", type date}}), test = Table.TransformColumnTypes(#"Changed Type1",{{"Payroll No.", type text}, {"First Name", type text}, {"Surname", type text}, {"Sum of Count", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}), Custom1 = Table.Group(test,{"Payroll No.","First Name","Surname"},{"test",(x)=>List.Sort(x[Start Date] & List.Transform(x[End Date],(y)=>Date.AddDays(y,1))) }), #"Expanded test" = Table.ExpandListColumn(Custom1, "test"), #"Added Custom" = Table.AddColumn(#"Expanded test", "Count", (x)=>Table.RowCount(Table.SelectRows(#"Expanded test",(y)=> y[test]=x[test] and y[#"Payroll No."]=x[#"Payroll No."] )) ), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Count] = 1)), aa = Table.RemoveColumns(#"Filtered Rows",{"Count"}), Custom2 = Table.AddColumn(Table.Group( aa,{"Payroll No.","First Name","Surname"},{{"test",(x)=> List.Alternate(x[test],1,1,1) } ,{"test2",(x)=>List.Transform(List.Alternate(x[test],1,1),(y)=>Date.AddDays(y,-1)) }} ) ,"tt",(x)=> Table.FromColumns({x[test],x[test2]}) ), #"Removed Columns" = Table.RemoveColumns(Custom2,{"test", "test2"}), #"Expanded tt" = Table.ExpandTableColumn(#"Removed Columns", "tt", {"Column1", "Column2"}, {"Start Date", "End Date"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded tt",{{"Start Date", type date}, {"End Date", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", (x)=>List.Sum(Table.SelectRows(#"Changed Type1",(y)=>y[Start Date]>=x[Start Date] and y[End Date]<=x[End Date] and y[#"Payroll No."]=x[#"Payroll No."] )[Sum of Count] ) ) in #"Added Custom1"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Community
i have an issue that i can't solve myself.
My data table looks like this. I have a lot of rows that determines the work period for each employee. There are scenarios where an employee has several rows, but the dates are consecutive - so in these cases i want to tell my data model that these should be combined into 1 row, that indicates the start and end date for that trip.
Data Table:Illustration of my issue / Request:
Hi micjensen ,
According to your description, my suggestion is to implement it in power query.
(1)This is my test data.
(2)Add the following code in the advanced editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndHNCsIwDADgVxk9D9pm/T2qLyB4HDsUV7zMDjp9fzPcTw9Oth4KCSEfTVLXxBptaSUqZUhJzj68ilNoo3cDpjcXWh8fLraYcHySgqbAADA2VHzDpjyoGCpXhNtMxVI7K0uYgXC2KtxkMFNr8hlg2QzwhJHZQ4FKmJyhRgY3w8XsjGtKIaYEQkJqrF36+PRd0fUBk2vXD3cX3xOilraSaMohg8C26vel9wp/rnyA2LrwXmJzn80H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Payroll No." = _t, #"First Name" = _t, Surname = _t, #"Sum of Count" = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type2" = Table.TransformColumnTypes(Source,{{"Sum of Count", Int64.Type}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type2",{{"Start Date", type date}, {"End Date", type date}}), test = Table.TransformColumnTypes(#"Changed Type1",{{"Payroll No.", type text}, {"First Name", type text}, {"Surname", type text}, {"Sum of Count", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}), Custom1 = Table.Group(test,{"Payroll No.","First Name","Surname"},{"test",(x)=>List.Sort(x[Start Date] & List.Transform(x[End Date],(y)=>Date.AddDays(y,1))) }), #"Expanded test" = Table.ExpandListColumn(Custom1, "test"), #"Added Custom" = Table.AddColumn(#"Expanded test", "Count", (x)=>Table.RowCount(Table.SelectRows(#"Expanded test",(y)=> y[test]=x[test] and y[#"Payroll No."]=x[#"Payroll No."] )) ), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Count] = 1)), aa = Table.RemoveColumns(#"Filtered Rows",{"Count"}), Custom2 = Table.AddColumn(Table.Group( aa,{"Payroll No.","First Name","Surname"},{{"test",(x)=> List.Alternate(x[test],1,1,1) } ,{"test2",(x)=>List.Transform(List.Alternate(x[test],1,1),(y)=>Date.AddDays(y,-1)) }} ) ,"tt",(x)=> Table.FromColumns({x[test],x[test2]}) ), #"Removed Columns" = Table.RemoveColumns(Custom2,{"test", "test2"}), #"Expanded tt" = Table.ExpandTableColumn(#"Removed Columns", "tt", {"Column1", "Column2"}, {"Start Date", "End Date"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded tt",{{"Start Date", type date}, {"End Date", type date}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", (x)=>List.Sum(Table.SelectRows(#"Changed Type1",(y)=>y[Start Date]>=x[Start Date] and y[End Date]<=x[End Date] and y[#"Payroll No."]=x[#"Payroll No."] )[Sum of Count] ) ) in #"Added Custom1"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
