Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shruthi96
Helper III
Helper III

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

21 REPLIES 21
Vera_33
Resident Rockstar
Resident Rockstar

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors