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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Nagasekharbi
New Member

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

RAW TABLE

GROUP NAMETICKET NUMERPRIORITYLINE
AINC1111SI
AINC1122SI
AINC1134MS
BINC2222CLIENT
BINC2213SI
DINC0002MS
CINC3313DD
CINC3321MS
CINC3334GH


I NEED LIKE THIS

GROUP NAMETICKET NUMERPRIORITYLINE
AINC1111SI
AINC1122SI
AINC1134MS
A3  
BINC2222CLIENT
BINC2213SI
B2  
DINC0002MS
D1  




1 ACCEPTED SOLUTION
danextian
Super User
Super User

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"

danextian_0-1683513614092.png

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

1 REPLY 1
danextian
Super User
Super User

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"

danextian_0-1683513614092.png

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.