AustSportingPix
New Member
- Joined
- Oct 5, 2020
- Messages
- 42
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
Below is my code for getting data from a table.
It goes online and gets the barrier (starting gates) stats for the past 3 years from the track they're racing at.
In my template I put the race track & date and hit refreash, it then goes out and rips down data from about 30 tables online or on my computer.
The only one I have to change in advanced editor is the barrier one because the "Data13 = Source{13}[Data]," highlighted in bold changes. sometimes in 12, 13, other times its 9 or 10. I'll like a quick macro on my streamdeck where I can hit 8-9-10-11-12-13 or 14 and it changes the table which changes the source like the "FilePath" one does.
Anyone able to help? if it helps, the table I have ready with the number 13 in it is called "barrier_num"
Thanks
It goes online and gets the barrier (starting gates) stats for the past 3 years from the track they're racing at.
In my template I put the race track & date and hit refreash, it then goes out and rips down data from about 30 tables online or on my computer.
The only one I have to change in advanced editor is the barrier one because the "Data13 = Source{13}[Data]," highlighted in bold changes. sometimes in 12, 13, other times its 9 or 10. I'll like a quick macro on my streamdeck where I can hit 8-9-10-11-12-13 or 14 and it changes the table which changes the source like the "FilePath" one does.
Anyone able to help? if it helps, the table I have ready with the number 13 in it is called "barrier_num"
Thanks
Power Query:
let
FilePath = Excel.CurrentWorkbook(){[Name="RS"]}[Content]{0}[Column1],
Source = Web.Page(Web.Contents(FilePath)),
[B]Data13 = Source{13}[Data],[/B]
#"Changed Type" = Table.TransformColumnTypes(Data13,{{"Race", type text}, {"Dist", type text}, {"BP", Int64.Type}, {"Wins", Int64.Type}, {"Win%", type number}, {"Places", Int64.Type}, {"Place%", type number}, {"Starts", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Race"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Columns", {{"Dist", each Text.BeforeDelimiter(_, "n"), type text}}),
#"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Dist", each Text.BeforeDelimiter(_, "m"), type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter1",{{"Dist", Int64.Type}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Win%", "Place%"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each [Wins]/[Starts]*100),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Custom", "Win Rate"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Plaace Rate", each [Places]/[Starts]*25),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Rating", each [Win Rate]+[Plaace Rate]),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Rating", type number}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type3", {{"Rating", 0}}),
#"Removed Columns2" = Table.RemoveColumns(#"Replaced Errors",{"Plaace Rate", "Rating", "Win Rate"})
in
#"Removed Columns2"
Last edited by a moderator:
