Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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"
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 @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.
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 | |||
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 |
@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"
@Shruthi96 did you get a chance to look into this?
@smpa01 , It just groups based on assignment group, not meeting the expected result. Thanks for providing your input.
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
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.
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |