PQ - Numbering duplicates in ascending order
Numbering duplicates in ascending order
| Raw |
____________ |
Result |
| 2323-2323-2323-1115 |
|
2323-2323-2323-0000 / 000 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-0000 / 001 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-0000 / 002 |
| 2323-2323-2323-1112 |
|
2323-2323-2323-0000 / 003 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1111 / 000 |
| 2323-2323-2323-1112 |
|
2323-2323-2323-1111 / 001 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1111 / 002 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1111 / 003 |
| 2323-2323-2323-0000 |
|
2323-2323-2323-1111 / 004 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1111 / 005 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1111 / 006 |
| 2323-2323-2323-1112 |
|
2323-2323-2323-1112 / 000 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1112 / 001 |
| 2323-2323-2323-1112 |
|
2323-2323-2323-1112 / 002 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1112 / 003 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1112 / 004 |
| 2323-2323-2323-1112 |
|
2323-2323-2323-1115 / 000 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1115 / 001 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1115 / 002 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1115 / 003 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1115 / 004 |
| 2323-2323-2323-0000 |
|
2323-2323-2323-1115 / 005 |
| 2323-2323-2323-1115 |
|
2323-2323-2323-1115 / 006 |
| 2323-2323-2323-0000 |
|
2323-2323-2323-1115 / 007 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1115 / 008 |
| 2323-2323-2323-0000 |
|
2323-2323-2323-1115 / 009 |
| 2323-2323-2323-1111 |
|
2323-2323-2323-1115 / 010 |
Code:
// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Group(Source, {"Raw"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "List", each [Count][Raw]),
Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
Divisor = Table.AddColumn(Extract, "Divisor", each List.Count(Text.Split([List],","))),
ListNumber = Table.AddColumn(Divisor, "Number", each {0..[Divisor]-1}),
Expand = Table.ExpandListColumn(ListNumber, "Number"),
TypeText = Table.TransformColumnTypes(Expand,{{"Number", type text}}),
TPS = Table.AddColumn(TypeText, "TPS", each Text.PadStart([Number], 3, "0")),
Mrg = Table.CombineColumns(TPS,{"Raw", "TPS"},Combiner.CombineTextByDelimiter(" / ", QuoteStyle.None),"Result"),
SortAsc = Table.Sort(Mrg,{{"Result", Order.Ascending}}),
TSC = Table.SelectColumns(SortAsc,{"Result"})
in
TSC