Forum Discussion
Grouping 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. Please let me know if any one from this group can help with simple solution. Many thanks in advance.
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"
21 Replies
Hi Shruthi96
Here is one way, assume it sorts by Start datetime, not elegant...but does the job
use dummy data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc87DoAgEEXRrRhqEubDFLoVQmFFqTFx/1IZ4cWpyMm7xVBK2EMM7bhP7i8nTkLCy7qRgWv8rZnA31qm1cBvffVcx1kI7OYG9nIlsPNRNbBTZwIPp+RpNnCtDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Task = _t, Group = _t, Start = _t, End = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task", type text}, {"Group", type text}, {"Start", type datetime}, {"End", type datetime}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "minStart", each if [Index]=0 then 1 else if [Group]=#"Added Index"[Group]{[Index]-1} then 0 else 1), #"Added Custom1" = Table.AddColumn(#"Added Custom", "maxEnd", each try if [Group]=#"Added Index"[Group]{[Index]+1} then 0 else 1 otherwise 1), StartTable = Table.AddIndexColumn( Table.SelectColumns( Table.SelectRows(#"Added Custom1", each ([minStart] = 1)),{"Group", "Start"}),"Index", 0, 1, Int64.Type), EndTable = Table.AddIndexColumn( Table.SelectColumns( Table.SelectRows(#"Added Custom1", each ([maxEnd] = 1)),{"Group", "End"}),"Index", 0, 1, Int64.Type), #"Merged Queries" = Table.NestedJoin(StartTable, {"Group", "Index"}, EndTable, {"Group", "Index"}, "EndTable", JoinKind.LeftOuter), #"Expanded EndTable" = Table.ExpandTableColumn(#"Merged Queries", "EndTable", {"End"}, {"End"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded EndTable",{"Index"}) in #"Removed Columns"hey Anonymous , you are awesome.
Can you help me with one more thing, it is possible to include Task also in that table.
I understand it is done through index. 🙂 I am still in process of learning path, can you kindly include Task along with Group, start and end. This will really help.
Hi Anonymous , Many thanks for reply.
I observed one thing,
End of first row should be start of the second row.
