I selected Matt Adams because he has 3 records (TOT, WSN, and STL). I understand replacing the TOT with his latest team. But, your instructions were to change the next row down, WSN to null. What happens to the STL row?
horseyride, I tried to insert your code and I got a 'token comma' error ? Not sure what this means, did a google search and had no success fixing it. Problem is I don't have a clue how to do anything in power query beyond the basics within the menus.
I appreciate your help, thank you.
I selected Matt Adams because he has 3 records (TOT, WSN, and STL). I understand replacing the TOT with his latest team. But, your instructions were to change the next row down, WSN to null. What happens to the STL row?
Ron Coderre
Microsoft MVP (2006-2015)
The STL row can remain as is, or if we can make it NULL as well thats even better. The reason I wanted to change the row below to NULL is so I wouldn't have duplicate WSN rows for my index match.
Do you need the non-TOT rows at all? Or can we just replace TOT with the latest team, then delete the other rows...Only for the players with a TOT row?
Ron Coderre
Microsoft MVP (2006-2015)
So based on what you said above, we would only have one occurence of each player, correct. If so that would be fine as well. Whichever is easier to do.
Three different result tables (only one of them is loaded to the sheet - you can load the remaining ones if you like)
Regards
I see that Bill responded.
I took a different approach:
First, I reworked your initial query to clean it up a bit:
Query: Player Standard Batting
This second query references the first and returns one row per player:Code:let Source = Web.Page(Web.Contents("https://www.baseball-reference.com/l..._batting::none")), Data3 = Source{3}[Data], SetDataTypes = Table.TransformColumnTypes(Data3,{{"Rk", Int64.Type}, {"Name", type text}, {"Age", Int64.Type}, {"Tm", type text}, {"Lg", type text}, {"G", Int64.Type}, {"PA", Int64.Type}, {"AB", Int64.Type}, {"R", Int64.Type}, {"H", Int64.Type}, {"2B", Int64.Type}, {"3B", Int64.Type}, {"HR", Int64.Type}, {"RBI", Int64.Type}, {"SB", Int64.Type}, {"CS", Int64.Type}, {"BB", Int64.Type}, {"SO", Int64.Type}, {"BA", type number}, {"OBP", type number}, {"SLG", type number}, {"OPS", type number}, {"OPS+", Int64.Type}, {"TB", Int64.Type}, {"GDP", Int64.Type}, {"HBP", Int64.Type}, {"SH", Int64.Type}, {"SF", Int64.Type}, {"IBB", Int64.Type}, {"Pos Summary", type text}}), RemoveNameAsterisk = Table.ReplaceValue(SetDataTypes,"*","",Replacer.ReplaceText,{"Name"}), RemoveNameHash = Table.ReplaceValue(RemoveNameAsterisk,"#","",Replacer.ReplaceText,{"Name"}), DuplicateNameCol = Table.DuplicateColumn(RemoveNameHash, "Name", "Last Name"), SplitName_FirstLast = Table.SplitColumn(DuplicateNameCol, "Last Name", Splitter.SplitTextByEachDelimiter({"#(00A0)"}, QuoteStyle.Csv, true), {"First Name", "Last Name"}), DuplicateFirstNameCol = Table.DuplicateColumn(SplitName_FirstLast, "First Name", "Initial"), GetFirstInit = Table.TransformColumns(DuplicateFirstNameCol, {{"Initial", each Text.Start(_, 1) & ".", type text}}), AddShortNameCol = Table.AddColumn(GetFirstInit, "InitLName", each [Initial] & [Last Name]), ReorderCols = Table.ReorderColumns(AddShortNameCol,{"Rk", "First Name", "Initial", "Last Name", "InitLName", "Age", "Tm", "Lg", "G", "PA", "AB", "R", "H", "2B", "3B", "HR", "RBI", "SB", "CS", "BB", "SO", "BA", "OBP", "SLG", "OPS", "OPS+", "TB", "GDP", "HBP", "SH", "SF", "IBB", "Pos Summary"}) in ReorderCols
Query: Results
Is that something you can work with?Code:let Source = #"Player Standard Batting", ReplaceTOTwithNULL = Table.ReplaceValue(Source,"TOT",null,Replacer.ReplaceValue,{"Tm"}), FillUpNulls = Table.FillUp(ReplaceTOTwithNULL,{"Tm"}), GetMinRank = Table.Group(FillUpNulls, {"Name"}, {{"MinRank", each List.Min([Rk]), type number}}), MergeDataWithMinRank = Table.NestedJoin(FillUpNulls,{"Rk"},GetMinRank,{"MinRank"},"NewColumn",JoinKind.Inner), RemoveUnusedCol = Table.RemoveColumns(MergeDataWithMinRank,{"NewColumn"}) in RemoveUnusedCol
Ron Coderre
Microsoft MVP (2006-2015)
Thanks Bill and Ron for all of your help. I plan to study (reverse engineer so to speak) these codes and try and make heads and tails of what you guys have done so I can do it myself in the future.
One question, and this will show you both that I know nothing about power query. How do I load the other tables (labelled connection only) into the workbook.
My experience lies with VBA, still extremely limited. It seems PQ would be the better choice, I like the ease of pulling in tables from the web and manipulating them if need be. Usually this part is quite easy, this time I ran into a big challenge with the multiple player entries.
Thanks again to both of you for your time and efforts in helping me out!
I'm pasting everything from below SetDataTypes in the first group of code into my code.
I get the following error:
Expression.SyntaxError: Token Comma expected
I'm just playing around trying to recreate what you did, I've already picked up a few things from you guys.
Also why is the URL in your code truncated?
Source = Web.Page(Web.Contents("https://www.baseball-reference.com/l..._batting::none")),
and I'm interested
Can RemoveNameAsterisk & RemoveNameHash be done in one command?
In the M-Language, every command statement ends with a comma...except for the last statement before the "in" statement. If you pasted code after the last line of your code, you'll need to put a comma after that last line.
Regarding the url, I certainly didn't do that purposely. I suspect Microsoft was "helping". Just put the complete url in your code.
You could combine the asterisk and hash replacements but the code might be a bit ugly. I think separate replacements is easier to read and maintain.
Ron Coderre
Microsoft MVP (2006-2015)
Bookmarks