Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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"
Proud to be a 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"
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
88 | |
77 | |
69 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |