cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shruthi96
Helper I
Helper I

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_0-1638269926081.png

 

1 ACCEPTED 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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

21 REPLIES 21
Vera_33
Super User
Super User

Hi @Shruthi96 

 

Here is one way, assume it sorts by Start datetime, not elegant...but does the job

use dummy data

Vera_33_0-1638328554247.png

 

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 @Vera_33 ,  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 @Vera_33 ,  Many thanks for reply. 

 

I observed one thing, 

End of first row should be start of the second row. 

Shruthi96_0-1638339547129.png

 

Hi @Shruthi96 

 

Good catch, so the datetime is continuous. Can you provide some sample data in a format which people can copy? No need to have lots of rows, but please include at least 2 Task, I will see how to work from there

Hey @Vera_33 , I dint realise i can paste the table. See if this works. Thanks. 

Input
TaskAssignment groupStartEnd
Ticket#12345Assignmentgroup116-07-2021 07:21:2016-07-2021 07:21:51
Ticket#12345Assignmentgroup216-07-2021 07:21:5116-07-2021 07:40:55
Ticket#12345Assignmentgroup216-07-2021 07:40:5516-07-2021 07:49:29
Ticket#12345Assignmentgroup116-07-2021 07:49:2916-07-2021 11:31:28
Ticket#12345Assignmentgroup116-07-2021 11:31:2821-07-2021 09:51:03
Ticket#12345Assignmentgroup321-07-2021 09:51:0321-07-2021 10:02:00
Ticket#12345Assignmentgroup321-07-2021 10:02:0021-07-2021 10:05:46
Ticket#12345Assignmentgroup321-07-2021 10:05:4621-07-2021 11:19:14
Ticket#12345Assignmentgroup321-07-2021 11:19:1422-07-2021 09:14:20
Ticket#12345Assignmentgroup422-07-2021 09:14:2023-07-2021 12:27:34
Ticket#12345Assignmentgroup323-07-2021 12:27:3423-07-2021 13:38:41
Ticket#12345Assignmentgroup523-07-2021 13:38:4123-07-2021 17:56:29

 

 

Output 
TaskAssignment groupStartEnd
Ticket#12345Assignmentgroup116-07-2021 07:21:2016-07-2021 07:21:51
Ticket#12345Assignmentgroup216-07-2021 07:21:5116-07-2021 07:49:29
Ticket#12345Assignmentgroup116-07-2021 07:49:2921-07-2021 09:51:03
Ticket#12345Assignmentgroup321-07-2021 09:51:0322-07-2021 09:14:20
Ticket#12345Assignmentgroup422-07-2021 09:14:2023-07-2021 12:27:34
Ticket#12345Assignmentgroup323-07-2021 12:27:3423-07-2021 13:38:41
Ticket#12345Assignmentgroup523-07-2021 13:38:4123-07-2021 17:56:29

@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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

@Shruthi96  did you get a chance to look into this?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@smpa01 , It just groups based on assignment group, not meeting the expected result. Thanks for providing your input. 

Hi @Shruthi96 

 

try the way from @wdx223_Daniel, more elegant

wdx223_Daniel_0-1638345693424.png

 

Hi @wdx223_Daniel , Thanks for reply. 

I am not able to replace the source, which I get from Azure SQL DB. 

Hi @wdx223_Daniel , 

 

Let me know where i made mistake. 

 

= #table(Sql.Database("*****.database.windows.net,1433", "DB name", [Query="select * from [dbo].[tablename] where Task in ('Ticket1','Ticket2');"]),List.Accumulate(Table.ToRows(Source),{},(x,y)=>if x={} them {y} else let a = List.Last(x) in if a{0}=y{0} and a{1}=y{1} and a{3} = y{2} them List.RemoveLastN(x,1)&{List.RemoveLastN(a,1)&{y{3}}} else x&{y}))

there is no need to change your source code. just insert my code after the step which output your sample data table. and replace "Source" with the name of previous step name.

It is possible to paste the "advance editor" code. 

Hi @wdx223_Daniel ,  

 

In the advance editor, I have below information :  and I get error message : 

Expression.Error: The import Changed Type matches no exports. Did you miss a module

 

Expression.Error: The name #"Changed Type" wasn't recognized. Make sure it's spelled correctly. reference?

===========================
let
Source = Sql.Database("***.database.windows.net,1433", "DB Name", [Query="select * from [dbo].[Table Name] where Task in ('Ticket 1','Ticket 2');"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}})
=#table(Table.ColumnNames(#"Changed Type"),List.Accumulate(Table.ToRows(#"Changed Type"),{},(x,y)=>if x={} then {y} else let a = List.Last(x) in if a{0}=y{0} and a{1}=y{1} and a{3} = y{2} then List.RemoveLastN(x,1)&{List.RemoveLastN(a,1)&{y{3}}} else x&{y}))
in
#"Changed Type"

===========================

Hi @Shruthi96 

 

You can use Advanced Editor or formula bar, the step is a custom step shown in the screenshot

 

let
Source = Sql.Database("***.database.windows.net,1433", "DB Name", [Query="select * from [dbo].[Table Name] where Task in ('Ticket 1','Ticket 2');"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime}}),
custom
=#table(Table.ColumnNames(#"Changed Type"),List.Accumulate(Table.ToRows(#"Changed Type"),{},(x,y)=>if x={} then {y} else let a = List.Last(x) in if a{0}=y{0} and a{1}=y{1} and a{3} = y{2} then List.RemoveLastN(x,1)&{List.RemoveLastN(a,1)&{y{3}}} else x&{y}))
in
custom

 

recommend to read his blog to understand M better

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

 

Thanks @Vera_33 

 

I am able to fix the error, but it is not meeting the solution.  It is showing all the data as original it is not grouping based on group, start and end date. 

Hi @Shruthi96 

 

It is because your Start and End are different data types now, can't compare, you can go

let
Source = Sql.Database("***.database.windows.net,1433", "DB Name", [Query="select * from [dbo].[Table Name] where Task in ('Ticket 1','Ticket 2');"]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type datetime},{"End",type datetime}}),
custom
=#table(Table.ColumnNames(#"Changed Type"),List.Accumulate(Table.ToRows(#"Changed Type"),{},(x,y)=>if x={} then {y} else let a = List.Last(x) in if a{0}=y{0} and a{1}=y{1} and a{3} = y{2} then List.RemoveLastN(x,1)&{List.RemoveLastN(a,1)&{y{3}}} else x&{y}))
in
custom

or

let
Source = Sql.Database("***.database.windows.net,1433", "DB Name", [Query="select * from [dbo].[Table Name] where Task in ('Ticket 1','Ticket 2');"]),
custom=#table(Table.ColumnNames(Source),List.Accumulate(Table.ToRows(Source),{},(x,y)=>if x={} then {y} else let a = List.Last(x) in if a{0}=y{0} and a{1}=y{1} and a{3} = y{2} then List.RemoveLastN(x,1)&{List.RemoveLastN(a,1)&{y{3}}} else x&{y}))
in
custom

@Vera_33 , Sorry if I keep asking. 🙂

 

this solution works even if there are multiple rows same group ?, example which i shared have details about 3 hops or 4 hops.. in real scenario if I have 20 hops with start and end dates it works. ? or do i need add "or" conditions.. 

Hi @Shruthi96 

 

You can test the code, it looks at Task, Group, Start and End. You can also try the way from @smpa01 which is grouping I was trying to do.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors