Forum Discussion
Merging records that have continuous dates
Hi,
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date from", type date}, {"Date to", type date}}, "en-GB"), #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Employee", Order.Ascending}, {"Date from", Order.Ascending}}), Index = Table.Buffer(Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)), #"Added Custom" = Table.AddColumn(Index, "ModIndex", each try if Index[Employee]{[Index]-1}=[Employee] and Index[#"Reason code"]{[Index]-1}=[#"Reason code"] and Index[#"Date to"]{[Index]-1}=Date.AddDays([#"Date from"],-1) then [Index]-1 else [Index] otherwise [Index]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Employee", "Reason code", "ModIndex"}, {{"Date from", each List.Min([Date from]), type nullable date}, {"Date to", each List.Max([Date to]), type nullable date}}) in #"Grouped Rows"You can sort the table by name and start date. Then add index column, add custom column which cheks if previous row has the same name, reason and date to is one day smaller then date from. If yes get previous row idex else current row index. Then gropu by modyfied index column and get min and max from date field respectively.
Hope this will help.
Artur
Hi,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date from", type date}, {"Date to", type date}}, "en-GB"),
#"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Employee", Order.Ascending}, {"Date from", Order.Ascending}}),
Index = Table.Buffer(Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)),
#"Added Custom" = Table.AddColumn(Index, "ModIndex", each try if Index[Employee]{[Index]-1}=[Employee] and Index[#"Reason code"]{[Index]-1}=[#"Reason code"] and Index[#"Date to"]{[Index]-1}=Date.AddDays([#"Date from"],-1) then [Index]-1 else [Index] otherwise [Index]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Employee", "Reason code", "ModIndex"}, {{"Date from", each List.Min([Date from]), type nullable date}, {"Date to", each List.Max([Date to]), type nullable date}})
in
#"Grouped Rows"You can sort the table by name and start date. Then add index column, add custom column which cheks if previous row has the same name, reason and date to is one day smaller then date from. If yes get previous row idex else current row index. Then gropu by modyfied index column and get min and max from date field respectively.
Hope this will help.
Artur
Thanks very much Artur. This seems to be what i'm looking for. I get an idea of what the code is doing, but really struggling to understand it so that I can use it in practice.
This bit of the code i am really struggling to understand. Is there a name for this particular function or technique that I can google in order to find out more. Just not clear on what this part is doing.
Index[Employee]{[Index]-1}=[Employee] and Index[#"Reason code"]{[Index]-1}=[#"Reason code"] and Index[#"Date to"]{[Index]-1}=Date.AddDays([#"Date from"],-1)
Thanks
-Jocky
Index is the name of previous step, [Employee] is the name of the column in previous step between curly brackets you can choose record number (zero based) from which you want to get the data. In this case {[Index]-1} in courrent row value in column Index has value ie. 2 so PQ calculates that you want data from row number 1 from column [Employee] of the previous step. If you just call column name without giving table and row number you get values from current row.
I hope it's more clear now.
Artur
Thanks very much.