# Help with Current and highest streaks

#### Scott T

##### Well-known Member
Are all your letters the same with no extra spaces?

If you have "W" in one cell and "W " (W with a space after it) the formula will not see them as the same and will not count it as a streak.

#### Telman86

##### New Member
OK, I've just cleared all the data from my cells B2:P5 and re-entered them. Now it works perfectly! That must have been the problem as I copied and pasted the table from Bo_Ry's post earlier rather than enter the data.

Thank you so much guys for all your help!

#### lrobbo314

##### Well-known Member
No VBA, but how about a Power Query solution.

Book1
ABCDEFGHIJKLMNOPQRS
1NameWEEK 1WEEK 2WEEK 3WEEK 4WEEK 5WEEK 6WEEK 7WEEK 8WEEK 9WEEK 10WEEK 11WEEK 12WEEK 13WEEK 14WEEK 15Longest W StreakLongest L StreakCustom
2JohnWWXWWLXLWWWL42L1
3PeterXWLLWXXWXWWW52W5
4GeorgeXLLXLWXLXLXL13L3
Sheet1

Code for Longest Streak Table
Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
Reorder = Table.ReorderColumns(Index0,{"Name", "Index", "WEEK 1", "WEEK 2", "WEEK 3", "WEEK 4", "WEEK 5", "WEEK 6", "WEEK 7", "WEEK 8", "WEEK 9", "WEEK 10", "WEEK 11", "WEEK 12", "WEEK 13", "WEEK 14", "WEEK 15"}),
Remove0 = Table.RemoveColumns(Reorder,{"Name"}),
Unpivot = Table.UnpivotOtherColumns(Remove0, {"Index"}, "Attribute", "Value"),
Index1 = Table.AddIndexColumn(Unpivot, "Index.1", 0, 1),
FilterX = Table.SelectRows(Index1, each ([Value] <> "X")),
Group0 = Table.Group(FilterX, {"Index", "Value"}, {{"Count",
each Table.AddIndexColumn( Table.Sort(_, {{"Index.1", 0} } ) , "rowindexinsubset" ,1,1 ),
type table}},GroupKind.Local),
GetMax = Table.AddColumn(Group0, "Custom", each List.Max(Table.Column([Count],"rowindexinsubset"))),
Group1 = Table.Group(GetMax, {"Index", "Value"}, {{"Count", each List.Max([Custom]), type number}}),
Pivot = Table.Pivot(Group1, List.Distinct(Group1[Value]), "Value", "Count"),
Rename = Table.RenameColumns(Pivot,{{"W", "Longest W Streak"}, {"L", "Longest L Streak"}}),
Remove1 = Table.RemoveColumns(Rename,{"Index"})
in
Remove1``````
Code for Latest Streak Table
Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
Reorder = Table.ReorderColumns(Index0,{"Name", "Index", "WEEK 1", "WEEK 2", "WEEK 3", "WEEK 4", "WEEK 5", "WEEK 6", "WEEK 7", "WEEK 8", "WEEK 9", "WEEK 10", "WEEK 11", "WEEK 12", "WEEK 13", "WEEK 14", "WEEK 15"}),
Remove0 = Table.RemoveColumns(Reorder,{"Name"}),
Unpivot = Table.UnpivotOtherColumns(Remove0, {"Index"}, "Attribute", "Value"),
Index1 = Table.AddIndexColumn(Unpivot, "Index.1", 0, 1),
FilterX = Table.SelectRows(Index1, each ([Value] <> "X")),
Group0 = Table.Group(FilterX, {"Index", "Value"}, {{"Count",
each Table.AddIndexColumn( Table.Sort(_, {{"Index.1", 0} } ) , "rowindexinsubset" ,1,1 ),
type table}},GroupKind.Local),
GetMax = Table.AddColumn(Group0, "Custom", each List.Max(Table.Column([Count],"rowindexinsubset"))),
Index2 = Table.AddIndexColumn(GetMax, "Index.1", 0, 1),
Group1 = Table.Group(Index2, {"Index"}, {{"Count", each _, type table}}),
GetLast = Table.AddColumn(Group1, "Custom", each List.Last(Table.Column([Count],"Value")) & Text.From(List.Last(Table.Column([Count],"Custom")))),
Remove1 = Table.RemoveColumns(GetLast,{"Index", "Count"}),
Rename = Table.RenameColumns(Remove1,{{"Custom", "Current Streak"}})
in
Rename``````

#### lrobbo314

##### Well-known Member
Was able to consolidate the M code to just 1 table.

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index0 = Table.AddIndexColumn(Source, "Index", 0, 1),
Remove0 = Table.RemoveColumns(Index0,{"Name"}),
Unpivot = Table.UnpivotOtherColumns(Remove0, {"Index"}, "Attribute", "Value"),
FilterX = Table.SelectRows(Unpivot, each ([Value] = "L" or [Value] = "W")),
Index1 = Table.AddIndexColumn(FilterX, "Index.1", 0, 1),
Group0 = Table.Group(Index1, {"Index", "Value"}, {{"Count",
each Table.AddIndexColumn( Table.Sort(_, {{"Index.1", 0} } ) , "subsetIndex" ,1,1 ),
type table}},GroupKind.Local),
GetMax = Table.AddColumn(Group0, "Custom", each List.Max(Table.Column([Count],"subsetIndex"))),
Group1 = Table.Group(GetMax, {"Index"}, {{"Count", each _, type table}}),
wStreak = Table.AddColumn(Group1, "Longest W Streak", each List.Max(Table.SelectRows([Count], each [Value]="W")[Custom])),
lStreak = Table.AddColumn(wStreak, "Longest L Streak", each List.Max(Table.SelectRows([Count], each [Value]="L")[Custom])),
LastStreak = Table.CombineColumns(Table.TransformColumnTypes(Table.ExpandRecordColumn(Table.AddColumn(lStreak, "Custom.1", each Table.Last([Count])), "Custom.1", {"Value", "Custom"}, {"Value", "Custom"}), {{"Custom", type text}}, "en-US"),{"Value", "Custom"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Last Streak"),
Remove1 = Table.RemoveColumns(LastStreak,{"Index", "Count"})
in
Remove1``````
Book1
ABCDEFGHIJKLMNOPQRS
1NameWEEK 1WEEK 2WEEK 3WEEK 4WEEK 5WEEK 6WEEK 7WEEK 8WEEK 9WEEK 10WEEK 11WEEK 12WEEK 13WEEK 14WEEK 15Longest W StreakLongest L StreakLast Streak
2AlanWWWWXWLLLLWW54W2
3BarbLXXLWLWXLLLWL13L1
4CainXLLWXWWLXLLWL33L1
5DanaWLLLLWLXLWWW34W3
6EricWLWLXWXLWLLWWW32W3
7FranXLLWLWXLWLW12W1
8GaryLLLLLXXXWWXLLLL25L4
9HanaLLXXWXWXXWXWWXW52W5
10IvanWWLWLLWWLXWL22L1
11JaneLWLWXWWXLLXWWX32W2
12KentWLLLWLWWLXXLX23L2
13LeahXWWWXXXLWWXW31W3
14MarkWWWWWLLWWXLLXW52W1
15NinaLLLLXLLWXLWLLXL16L3
16RobLWLLWWWWXWWXW72W7
Sheet1