Power Query Count Occurrence

[ajw5173]

Hi, I am looking to create a column that shows how many times a value has occurred so far in the table.

For example

Style Occurence
abc | 1
bcd | 1
edg | 1
abc | 2
abc | 3
bcd | 2

 

[Alex Blakenburg]

Are you sure you aren't happy with a pivot table solution ?
Doing it in Power Query involves quite a few steps:-

Here is Ken Puls doing it as a Sum. It would be the same process for Count.
Create Running Totals in Power Query

 

[Bagnon]

This works once it's sorted:

let
Source = #table({"Name"}, {{"a"},{"a"},{"a"},{"b"},{"b"},{"b"},{"c"}}),
List = Source[Name],
List2 = List.Generate (
()=> [Counter = 1, Occ = 1],
each _[Counter] <= List.Count(List),
each [Counter = _[Counter] + 1, Occ = if List{_[Counter]} = List{_[Counter]-1} then _[Occ] + 1 else 1],
each _[Occ]
),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Occurance", each List2{[Index]}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
#"Removed Columns"

 

[JGordon11]

let
    lst = List.Buffer(Excel.CurrentWorkbook(){[Name="Table4"]}[Content][Style]),
    lst1 = List.Accumulate(List.Skip(lst), {{lst{0},1, 2}}, (s,c)=> let ll = List.Last(s){2} in s & {{c, List.Count(List.Select(List.FirstN(lst, ll), each _ = c)), ll +1}}),
    Result = Table.FromColumns(List.FirstN(List.Zip(lst1),2), {"Style","Occurence"})
in
    Result

Book1
	A	B	C	D	E
1	Style		Style	Occurence
2	B		B	1
3	B		B	2
4	C		C	1
5	E		E	1
6	D		D	1
7	C		C	2
8	C		C	3
9	A		A	1
10	B		B	3
11	E		E	2
12	E		E	3
13	E		E	4
14	A		A	2
15	D		D	2
16	A		A	3
17	B		B	4
18	D		D	3
19	A		A	4
20	D		D	4
21	C		C	4
22
Sheet4

 

[jdellasala]

let
    lst = List.Buffer(Excel.CurrentWorkbook(){[Name="Table4"]}[Content][Style]),
    lst1 = List.Accumulate(List.Skip(lst), {{lst{0},1, 2}}, (s,c)=> let ll = List.Last(s){2} in s & {{c, List.Count(List.Select(List.FirstN(lst, ll), each _ = c)), ll +1}}),
    Result = Table.FromColumns(List.FirstN(List.Zip(lst1),2), {"Style","Occurence"})
in
    Result

Book1
	A	B	C	D	E
1	Style		Style	Occurence
2	B		B	1
3	B		B	2
4	C		C	1
5	E		E	1
6	D		D	1
7	C		C	2
8	C		C	3
9	A		A	1
10	B		B	3
11	E		E	2
12	E		E	3
13	E		E	4
14	A		A	2
15	D		D	2
16	A		A	3
17	B		B	4
18	D		D	3
19	A		A	4
20	D		D	4
21	C		C	4
22
Sheet4

@JGordon11 I love your M Code, but it is REALLY obscure. I would love to see your code as above, and also a step by step using the UI as much as possible. Still, the code is amazing!