topic Re: Grouping option in DAX Commands and Tips

Grouping option

Shruthi96 — Tue, 30 Nov 2021 10:59:02 GMT

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.

Re: Grouping option

smpa01 — Tue, 30 Nov 2021 15:53:36 GMT

Shruthi96 can you please provide the data in atable format here ?

Re: Grouping option

Anonymous — Wed, 01 Dec 2021 03:13:57 GMT

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"

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 05:49:37 GMT

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.

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 06:19:51 GMT

Hi Anonymous , Many thanks for reply.

I observed one thing,

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

Re: Grouping option

Anonymous — Wed, 01 Dec 2021 06:56:34 GMT

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

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 07:19:33 GMT

Hey 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

Re: Grouping option

wdx223_Daniel — Wed, 01 Dec 2021 08:01:41 GMT

Re: Grouping option

Anonymous — Wed, 01 Dec 2021 08:20:23 GMT

Hi Shruthi96

try the way from wdx223_Daniel, more elegant

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 08:21:46 GMT

Hi wdx223_Daniel , Thanks for reply.

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

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 08:28:27 GMT

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}))

Re: Grouping option

wdx223_Daniel — Wed, 01 Dec 2021 08:38:01 GMT

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.

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 08:42:23 GMT

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

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 13:12:00 GMT

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"

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

Re: Grouping option

Anonymous — Wed, 01 Dec 2021 13:01:50 GMT

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-expressions-let

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 13:36:20 GMT

Thanks Anonymous

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.

Re: Grouping option

Anonymous — Wed, 01 Dec 2021 13:44:49 GMT

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

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

Re: Grouping option

Shruthi96 — Wed, 01 Dec 2021 14:16:51 GMT

Anonymous , 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..

Re: Grouping option

smpa01 — Wed, 01 Dec 2021 15:42:43 GMT

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"

Re: Grouping option

Anonymous — Thu, 02 Dec 2021 04:34:39 GMT

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.