I NEED TO CREATE SUBGROUP AT THE END GROUPNAME(dimension)

[Nagasekharbi]

RAW TABLE

GROUP NAME	TICKET NUMER	PRIORITY	LINE
A	INC111	1	SI
A	INC112	2	SI
A	INC113	4	MS
B	INC222	2	CLIENT
B	INC221	3	SI
D	INC000	2	MS
C	INC331	3	DD
C	INC332	1	MS
C	INC333	4	GH

I NEED LIKE THIS

GROUP NAME	TICKET NUMER	PRIORITY	LINE
A	INC111	1	SI
A	INC112	2	SI
A	INC113	4	MS
A	3
B	INC222	2	CLIENT
B	INC221	3	SI
B	2
D	INC000	2	MS
D	1

[danextian · Accepted solution]

Hi @Nagasekharbi ,

This can be done in Power Query. Add a custom step referencing  the previous step, remove all columns  but Group Name, remove duplicates and add a custom column called TICKET NUMBER with value of "3" then append this step  to the step prior the custom step. See sample M script below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfL0czY0NAQyQDjYUylWB0ncCMgwwiJuDGSYALFvMFjcCSJuZART7+zj6eoXgioHMt8YYRZcHCRmijDLGSJubAxT7+KCKm4EdSu6epib3D2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"GROUP NAME" = _t, #"TICKET NUMER" = _t, PRIORITY = _t, LINE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GROUP NAME", type text}, {"TICKET NUMER", type text}, {"PRIORITY", Int64.Type}, {"LINE", type text}}),
    Custom1 = #"Changed Type",
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"GROUP NAME"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "TICKET NUMER", each "3", type text),
    Custom2 = Table.Combine({#"Changed Type",#"Added Custom"}),
    #"Sorted Rows" = Table.Sort(Custom2,{{"GROUP NAME", Order.Ascending}, {"TICKET NUMER", Order.Descending}})
in
    #"Sorted Rows"