Forum Discussion
Shruthi96
Helper III
Helper IIIGrouping option
Hi Team, need help. I am looking for the solution , where it can group based on the assignment group , start and end. I have attached the snap shot of my problem and what I am looking as a solution....
Shruthi96 can you try this
let Source = Csv.Document(File.Contents("C:\Users\user\Desktop\source\Book3.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), CT = Table.TransformColumnTypes(#"Promoted Headers",{{"Task", type text}, {"Assignment group", type text}, {"Start", type datetime}, {"End", type datetime}}), src=CT[Assignment group], L2 = List.Generate( ()=>[i=0, j=src{i}, k=try src{i-1} otherwise j, l=1], each [i]<List.Count(src), each [i=[i]+1,j=src{i}, k=try src{i-1} otherwise j, l=if k<>j then [l]+1 else [l]], each [l] ), ColNames1 = Table.ColumnNames(CT), L1 = Table.ToColumns(CT), ColNames2 = List.Combine({ColNames1,{"Grouping"}}), Custom1 = Table.FromColumns(L1&{L2},ColNames2), #"Grouped Rows" = Table.Group(Custom1, {"Task", "Assignment group", "Grouping"}, {{"ad", each _, type table [Task=nullable text, Assignment group=nullable text, Start=nullable datetime, End=nullable datetime, Grouping=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Start", each let x =[ad] in List.Min(x[Start])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "End", each let x =[ad] in List.Max(x[End])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ad"}) in #"Removed Columns"
Shruthi96Helper III
Helper IIIHey Anonymous , I dint realise i can paste the table. See if this works. Thanks.
| Input | |||
| Task | Assignment group | Start | End |
| Ticket#12345 | Assignmentgroup1 | 16-07-2021 07:21:20 | 16-07-2021 07:21:51 |
| Ticket#12345 | Assignmentgroup2 | 16-07-2021 07:21:51 | 16-07-2021 07:40:55 |
| Ticket#12345 | Assignmentgroup2 | 16-07-2021 07:40:55 | 16-07-2021 07:49:29 |
| Ticket#12345 | Assignmentgroup1 | 16-07-2021 07:49:29 | 16-07-2021 11:31:28 |
| Ticket#12345 | Assignmentgroup1 | 16-07-2021 11:31:28 | 21-07-2021 09:51:03 |
| Ticket#12345 | Assignmentgroup3 | 21-07-2021 09:51:03 | 21-07-2021 10:02:00 |
| Ticket#12345 | Assignmentgroup3 | 21-07-2021 10:02:00 | 21-07-2021 10:05:46 |
| Ticket#12345 | Assignmentgroup3 | 21-07-2021 10:05:46 | 21-07-2021 11:19:14 |
| Ticket#12345 | Assignmentgroup3 | 21-07-2021 11:19:14 | 22-07-2021 09:14:20 |
| Ticket#12345 | Assignmentgroup4 | 22-07-2021 09:14:20 | 23-07-2021 12:27:34 |
| Ticket#12345 | Assignmentgroup3 | 23-07-2021 12:27:34 | 23-07-2021 13:38:41 |
| Ticket#12345 | Assignmentgroup5 | 23-07-2021 13:38:41 | 23-07-2021 17:56:29 |
| Output | |||
| Task | Assignment group | Start | End |
| Ticket#12345 | Assignmentgroup1 | 16-07-2021 07:21:20 | 16-07-2021 07:21:51 |
| Ticket#12345 | Assignmentgroup2 | 16-07-2021 07:21:51 | 16-07-2021 07:49:29 |
| Ticket#12345 | Assignmentgroup1 | 16-07-2021 07:49:29 | 21-07-2021 09:51:03 |
| Ticket#12345 | Assignmentgroup3 | 21-07-2021 09:51:03 | 22-07-2021 09:14:20 |
| Ticket#12345 | Assignmentgroup4 | 22-07-2021 09:14:20 | 23-07-2021 12:27:34 |
| Ticket#12345 | Assignmentgroup3 | 23-07-2021 12:27:34 | 23-07-2021 13:38:41 |
| Ticket#12345 | Assignmentgroup5 | 23-07-2021 13:38:41 | 23-07-2021 17:56:29 |
smpa01
Community Champion
Community ChampionShruthi96 can you try this
let
Source = Csv.Document(File.Contents("C:\Users\user\Desktop\source\Book3.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
CT = Table.TransformColumnTypes(#"Promoted Headers",{{"Task", type text}, {"Assignment group", type text}, {"Start", type datetime}, {"End", type datetime}}),
src=CT[Assignment group],
L2 = List.Generate(
()=>[i=0, j=src{i}, k=try src{i-1} otherwise j, l=1],
each [i]<List.Count(src),
each [i=[i]+1,j=src{i}, k=try src{i-1} otherwise j, l=if k<>j then [l]+1 else [l]],
each [l]
),
ColNames1 = Table.ColumnNames(CT),
L1 = Table.ToColumns(CT),
ColNames2 = List.Combine({ColNames1,{"Grouping"}}),
Custom1 = Table.FromColumns(L1&{L2},ColNames2),
#"Grouped Rows" = Table.Group(Custom1, {"Task", "Assignment group", "Grouping"}, {{"ad", each _, type table [Task=nullable text, Assignment group=nullable text, Start=nullable datetime, End=nullable datetime, Grouping=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Start", each let x =[ad]
in List.Min(x[Start])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "End", each let x =[ad]
in List.Max(x[End])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"ad"})
in
#"Removed Columns"