Weird Table Question

Hello dwedding3. You can achieve this Power Query Editor. Please find the full source code. I added a conditional column, it checks if your "colour column" contains "Other", it outputs other, else the column value that it contains before. Then I added index to calculate the count of colors as I have no Unique ID.

 let
Source = Excel.Workbook(File.Contents("C:\Users\AJoshi\Downloads\Random.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Added Conditional Column" = Table.AddColumn(#"Removed Top Rows", "Color", each if Text.Contains([Column1], "Other") then "Other" else [Column1]),
#"Renamed Columns" = Table.RenameColumns(#"Added Conditional Column",{{"Column1", "Pre_Color"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1, Int64.Type)
in
#"Added Index"

I hope this helps!