Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 21

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

  1. #11
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #12
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    68
    Articles
    0
    Excel Version
    2010, 2013, 2016
    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)

  3. #13
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    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.

  4. #14
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    68
    Articles
    0
    Excel Version
    2010, 2013, 2016
    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)

  5. #15
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    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.

  6. #16
    Acolyte Bill Szysz's Avatar
    Join Date
    Apr 2016
    Location
    Poznań, Poland Dublin, Ireland
    Posts
    73
    Articles
    0
    Excel Version
    Excel 365
    Three different result tables (only one of them is loaded to the sheet - you can load the remaining ones if you like)

    Regards
    Attached Files Attached Files

  7. #17
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    68
    Articles
    0
    Excel Version
    2010, 2013, 2016
    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
    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
    This second query references the first and returns one row per player:
    Query: Results
    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
    Is that something you can work with?
    Ron Coderre
    Microsoft MVP (2006-2015)

  8. #18
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    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!

  9. #19
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    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?

  10. #20
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    68
    Articles
    0
    Excel Version
    2010, 2013, 2016
    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)

Page 2 of 3 FirstFirst 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •