Find Text in column, cut and paste value of row below? See example

[CONFUSED1]

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.

[Ron Coderre]

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?

[CONFUSED1]

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.

[Ron Coderre]

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?

[CONFUSED1]

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.

[Bill Szysz]

Three different result tables (only one of them is loaded to the sheet - you can load the remaining ones if you like)

Regards

[Ron Coderre]

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

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

This second query references the first and returns one row per player:
Query: Results

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

Is that something you can work with?

[CONFUSED1]

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!

[CONFUSED1]

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?

[Ron Coderre]

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.