Need M-code help, trying to get data from a table online that changes

AustSportingPix

New Member
Joined
Oct 5, 2020
Messages
42
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. 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

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:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Not sure what value is in B1 , does this work for testing purposes: Benalla Free Form Guide | Full Fields | Race 1 | Tuesday, 30th January 2024

Looks like: "benalla"

the table that I'm after is "Data13 = Source{12}[Data],"I have to in most cases go in and change the "{12}" to match the table that is the barriers, it can change from meeting to meeting or even by the hour.

I already have a table ready that has "12" in it, I just want to get that "Data13 = Source{12}[Data]" to be changed to the way its done with the track.so getting the source from 2 tables., one with the track & date, the other with the table number.

I'm sure that can be done?
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top