# Trying to do a Hockey Standings sheet...

#### hockeyfan1985

##### New Member
Hello, I was seeing if anyone would know any formulas or how to do a last ten games formula. Like a win-loss-ot loss. Also need a formula for a win/loss/ot streaks. Also if you have any other formulas please list for standings sheet. I have a sheet for the schedule and a 2nd for standings. I know my basics but just looking for some of the tougher ones.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### DRSteele

##### Well-known Member
I have a detailed model of the NHL. It works by using PowerQuery to download the scores from a website for all games (1271 for the 2018 season) into an Excel Table. Once it's downloaded, the entire NHL schedule can be used as the source data for every type of automated calculation imaginable, including all those streaks and the standings.

Do you know how to use PQ?

#### DRSteele

##### Well-known Member
OK. Let's begin. We'll have to do this in small steps that work with my layout, otherwise I'll become all confused and my head will ache.

Create a sheet called Q_Data. Create a blank query called Q_Scores_18_HR and paste this MCode into the blank query. 'Close and Load to' and put it in Q_Data in cell A40. Your table should end up in cells A40 to H1311.

Then we will name this as an ExcelTable T_Scr and begin to add many columns to that ExcelTable to create more essential data.

let
Source = Web.Page(Web.Contents("http://www.hockey-reference.com/leagues/NHL_2018_games.html")),
Data1 = Source{1}[Data],
#"Changed Type2" = Table.TransformColumnTypes(Data1,{{"Date", type date}, {"Visitor", type text}, {"G", type text}, {"Home", type text}, {"G2", type text}, {"", type text}, {"Att.", type text}, {"LOG", type text}, {"Notes", type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"Date", type date}, {"Visitor", type text}, {"G", type text}, {"Home", type text}, {"G2", type text}, {"", type text}, {"Att.", type text}, {"LOG", type text}, {"Notes", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Att.", "LOG", "Notes"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Date", "Visitor", "Home", "G", "G2", ""}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"G", "VScr"}, {"G2", "HScr"}, {"", "type"}, {"Home", "Host"}}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [VScr] = null then null else if [type] = "SO" then "S/O" else if [type] <> null then [type] else "R" ),
#"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Result"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"type"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Scoreline", each [Visitor] &" " & [VScr] &" - " & [Host] &" " & [HScr] &" "&[Result]),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Date", "Visitor", "Host", "Scoreline", "VScr", "HScr", "Result"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"VScr", Int64.Type}, {"HScr", Int64.Type}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Visitor", "Host", "Scoreline", "VScr", "HScr", "Result"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns2",{{"Index", "Gm"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Result", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type3", each true)
in
#"Filtered Rows"

Last edited:

#### hockeyfan1985

##### New Member
Thank you, I'm just not understanding that.

#### hockeyfan1985

##### New Member
not understanding PQ.

Replies
6
Views
348
Replies
4
Views
333
Replies
1
Views
176
Replies
9
Views
144
Replies
4
Views
172

1,171,648
Messages
5,876,652
Members
433,205
Latest member
jabin1991

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back