group and analyse data

event_id	menu_hint	EVENT_NAME	EVENT_DT	SELECTION_NAME	WIN_LOSE	BSP
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	5. Convoluted	0	9.953745
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	3. Loui Wonton	0	38.853743
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	1. Cherokee Outlaw	0	1.848149
191856472	AUS / Warragul (AUS) 4th Dec	R1 520m Gr5	04-12-2021 08:08	8. Smooth Jack	1	19.109564

Hello . I have a CSV file with lots of entries like in the table above.
this is just one of them and its a greyhound race with 4 dogs in it. the winner is the one with a 1 in the WIN_LOSE column .
and there are about 80-90 of them each with 4 5 6 7 or eight dogs .
I would like to analyse those races. but I have no Idea how to do that in excel.


First I would like to know how can I see the relations between dogs , like:

what is the BSP of the favorite in every race where it didnt win . in my example the favorite would be the one with 1.84 as BSP . and the winner with 19.1

I have no idea how to .. kinda group those entries by the ID and analyse each one of them .
I hope this is not too much to post or ask ..
thanks !

this might be what I am looking for . I will learn a little bit about this pivot table and hopefully do my work . thanks you all excel wizards !

Originally Posted by Richard Buttrey

Hi,

I'd be inclined to use a Pivot Table. The attached might give you a starter.. You can of course add all the fields for a full analysis. Add slicers as appropriate to more easily filter the data.

umm. another question to you .you created another column with "Lowest BSP" . and it works . but only for a race . in my example with one race and 4 runners . in the real dataset with lots of races if I try to drag the formula down to be applied to all rows this part of your code ($H$2:$H$5,$G$2:$G$5,0) doesn't move down . and I cant figure it out how to make it work !

I did now attach a real example file of my dataset !

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{",EVENT_ID,MENU_HINT,EVENT_NAME,EVENT_DT,SELECTION_NAME,WIN_LOSE,BSP", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Demoted Headers","Column1",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",".",",",Replacer.ReplaceText,{"BSP"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"BSP", type number}, {"EVENT_DT", type datetime}, {"EVENT_ID", Int64.Type}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type2", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Blank Rows",{{"Column1", "Num"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([EVENT_ID] = 191856472)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Num"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([WIN_LOSE] = "0"))
in
    #"Filtered Rows1"

with your help and a little bit of python I managed to do what I wanted . thanks you very much !