Power Query order by groups

[gargamalebarbosa]

Hi,
I have a list in column A. I want to add order numbers like column B with powery query. Is that possible ?

Thank you,

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    tbl1 = Table.AddColumn(tbl, "Custom", each try if [Name]<> tbl[Name]{[Index]-1} then true else false otherwise true),
    tbl2 = Table.Group(tbl1, {"Name"}, {{"All", each _}}),
    tbl3 = Table.TransformColumns(tbl2, {"All", each Table.AddIndexColumn(_, "Index1", 1, 1, Int64.Type)}),
    CountTrue = (tbl as table, col as text, idx as number) as number => List.Count(List.Select(List.FirstN(Table.Column(tbl, col), idx), each _=true)),
    tbl4 = Table.TransformColumns(tbl3, {"All", each Table.AddColumn(_, "Order", (x)=> CountTrue(_, "Custom", x[Index1]))}),
    tbl5 = Table.ExpandTableColumn(tbl4, "All", {"Index", "Order"}),
    tbl6 = Table.Sort(tbl5,{{"Index", Order.Ascending}}),
    Result = Table.RemoveColumns(tbl6,{"Index"})
in
    Result

Book1
	A	B	C	D	E
1	Table1		Query Output
2
3	Name		Name	Order
4	A		A	1
5	A		A	1
6	A		A	1
7	B		B	1
8	B		B	1
9	A		A	2
10	A		A	2
11	C		C	1
12	C		C	1
13	C		C	1
14	A		A	3
15	A		A	3
16	A		A	3
17	B		B	2
18	B		B	2
19	B		B	2
20
Sheet1

 

[gargamalebarbosa]

@JGordon11 thank you very much..

 

[JGordon11]

another approach using List.Accumulate

let
    names = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Name],
    lst = List.Accumulate(List.Skip(names), {{names{0},1}}, (s,c)=> s &
        (let LLs = List.Last(s) in 
            if c = LLs{0} then 
                {{c, LLs{1}}} 
            else 
                let LSs = List.Select(s, each _{0} = c) in 
                    if List.Count(LSs)= 0 then 
                        {{c,1}}
                    else 
                        {{c, List.Last(LSs){1}+1}})),
    Result = Table.FromRows(lst, {"Name", "Order"})
in
    Result