Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

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

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

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



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

    Example from power query:

    DET
    COL
    TOR
    NYY
    TOT
    BAL
    BOS
    SEA
    PIT
    DET
    SFG
    TOT
    KCR
    WSN
    COL

    A column in my power query looks like the above.

    I want a formula to search down the column and when it finds 'TOT'
    it will replace that value with the value of the row below using cut and paste

    So we achieve this result:

    DET
    COL
    TOR
    NYY
    BAL
    'now empty
    BOS
    SEA
    PIT
    DET
    SFG
    KCR
    'now empty
    WSN
    COL

    Is this possible to do within Power Query? I'm not quite sure how to achieve the results. Please help and thanks in advance!

  2. #2
    Conjurer Logit's Avatar
    Join Date
    Nov 2016
    Posts
    208
    Articles
    0
    Excel Version
    2007
    .
    Paste in regular module :

    Code:
    Option Explicit
    
    
    Sub FindnReplace()
        Dim c As Range
        Dim j As Integer
        Dim Source As Worksheet
        Dim Target As Worksheet
        
        Dim myString As String
    
    
        ' Change worksheet designations as needed
        Set Source = ActiveWorkbook.Worksheets("Sheet1")
        'Set Target = ActiveWorkbook.Worksheets("Search Results")
        
        myString = "TOT"
        
        'j = 2     ' Start copying to row 1 in target sheet
        For Each c In Source.Range("A2:A1000")   ' Do 1000 rows
            If c = myString Then
               c.Offset(1, 0).Cut Source.Rows(c.Row)
               j = j + 1
            End If
        Next c
    End Sub
    Attached Files Attached Files

  3. #3
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    How and where do I put this for Power Query Table?

    THanks for the help and sorry for my lack of experience.

  4. #4
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    68
    Articles
    0
    Excel Version
    2010, 2013, 2016
    With this table named Table1 in A1:A16
    Code:
    OrigData
    DET
    COL
    TOR
    NYY
    TOT
    BAL
    BOS
    SEA
    PIT
    DET
    SFG
    TOT
    KCR
    WSN
    COL
    This M-Code transforms the data the way your asking:
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        SetDataTypes = Table.TransformColumnTypes(Source,{{"OrigData", type text}}),
        ReplaceTOTwithNull = Table.ReplaceValue(SetDataTypes,"TOT",null,Replacer.ReplaceValue,{"OrigData"}),
        FillUp = Table.FillUp(ReplaceTOTwithNull,{"OrigData"}),
        Add_0Index_Col = Table.AddIndexColumn(FillUp, "Index", 0, 1),
        Add_1Index_Col = Table.AddIndexColumn(Add_0Index_Col, "Index.1", 1, 1),
        SelfMergeData = Table.NestedJoin(Add_1Index_Col,{"Index"},Add_1Index_Col,{"Index.1"},"NewColumn",JoinKind.LeftOuter),
        KeepUsedCol = Table.ExpandTableColumn(SelfMergeData, "NewColumn", {"OrigData"}, {"NewData"}),
        RemoveNullRows = Table.SelectRows(KeepUsedCol, each [NewData] <> null),
        SetNullField = Table.ReplaceValue(
            RemoveNullRows,
            each [OrigData],
            each if [NewData] = [OrigData]
            then null 
            else [OrigData],
            Replacer.ReplaceValue,
            {"OrigData"}),
        RemoveStagingCols = Table.RemoveColumns(SetNullField,{"Index", "Index.1", "NewData"})
    in
        RemoveStagingCols
    There may be more elegant approaches...but...
    Is that something you can work with?
    Ron Coderre
    Microsoft MVP (2006-2015)

  5. #5
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    Thanks, let me see if I can get it to work. I'm very new to Power Query and M-Code. Where would I insert this code within my current Query?

    Code:
    let
        Source = Web.Page(Web.Contents("https://www.baseball-reference.com/leagues/MLB/2018-standard-batting.shtml#players_standard_batting::none")),
        Data3 = Source{3}[Data],
        #"Changed Type" = 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}}),
        #"Replaced Value" = Table.ReplaceValue(#"Changed Type","*","",Replacer.ReplaceText,{"Name"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#","",Replacer.ReplaceText,{"Name"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Name", Splitter.SplitTextByEachDelimiter({"#(00A0)"}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "Name.1", "Name.1 - Copy"),
        #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Rk", "Name.1", "Name.1 - Copy", "Name.2", "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"}),
        #"Extracted First Characters" = Table.TransformColumns(#"Reordered Columns", {{"Name.1 - Copy", each Text.Start(_, 1), type text}}),
        #"Added Suffix" = Table.TransformColumns(#"Extracted First Characters", {{"Name.1 - Copy", each _ & ".", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Added Suffix",{{"Name.1", "First Name"}, {"Name.1 - Copy", "Initial"}, {"Name.2", "Last Name"}}),
        #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Custom", each [Initial] & [Last Name]),
        #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "InitLname"}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"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
        #"Reordered Columns1"

  6. #6
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    Ron
    I'm not having much success. I realize I have to tweak some of the variables in your M-code, which I attempted. If you could show me how your code inputs together with mine it would be super helpful. Hope to hear back from you!

  7. #7
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    37
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    ....
    Code:
     #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "InitLname"}}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"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"}),
        #"Added Index" = Table.AddIndexColumn(#"Reordered Columns1" , "Index", 0, 1),
        T1 = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(T1,{"Index.1"},T1,{"Index"},"T1",JoinKind.LeftOuter),
        T2 = Table.ExpandTableColumn(#"Merged Queries", "T1", {"Tm"}, {"Tm.1"}),
        #"Merged Queries1" = Table.NestedJoin(T2,{"Index"},T1,{"Index.1"},"T1",JoinKind.LeftOuter),
        #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "T1", {"Tm"}, {"Tm.2"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Table2",{{"Index", Order.Ascending}}),
        #"Added Tm" = Table.AddColumn(#"Sorted Rows", "Tm2", each if [Tm] = "TOT" then [Tm.1] else if [Tm.2]="TOT" then null else [Tm]),
        #"Removed Columns" = Table.RemoveColumns(#"Added Tm",{"Tm", "Index", "Index.1", "Tm.1", "Tm.2"}),
        #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns",{{"Tm2", "Tm"}})
    in #"Renamed Columns3"

  8. #8
    Acolyte Ron Coderre's Avatar
    Join Date
    Aug 2015
    Location
    Boston, MA
    Posts
    68
    Articles
    0
    Excel Version
    2010, 2013, 2016
    More complicated data....I'm assuming new rules apply.
    Can you select a person and describe what data should be returned? (Maybe Matt Adams...has 3 records)
    Ron Coderre
    Microsoft MVP (2006-2015)

  9. #9
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    what I did was take the original query and I had to do some formatting to the players names for a future Index/Match. The problem I have now is that when players are traded it lists there stats for each team they played for and then a total identified by TOT. So in order to do an Index/Match I reference the Player Name and Team Name. However instead of matching to TOT I need the players current team that why I thought of replacing TOT with the value in the row below which is the current team. I can't match just to a name as there maybe players with the same name.

    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
    5 Austin L. A. Adams A.Adams 27 WSN NL 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 /1
    6 Chance C. Adams C.Adams 23 NYY AL 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 /1
    7 Lane L. Adams L.Adams 28 ATL NL 26 29 25 10 6 1 0 2 6 1 0 4 8 0.24 0.345 0.52 0.865 130 13 1 0 0 0 0 /987
    8 Matt M. Adams M.Adams 29 WSN NL 121 337 306 42 73 10 0 21 57 0 0 27 73 0.239 0.309 0.477 0.786 105 146 6 4 0 0 3 37/D9
    9 Matt M. Adams M.Adams 29 NL 94 277 249 37 64 9 0 18 48 0 0 24 55 0.257 0.332 0.51 0.842 118 127 6 4 0 0 2 37/D9
    10 Matt M. Adams M.Adams 29 STL NL 27 60 57 5 9 1 0 3 9 0 0 3 18 0.158 0.2 0.333 0.533 43 19 0 0 0 0 1 3
    11 Jim J. Adduci J.Adduci 33 DET AL 59 185 176 19 47 8 2 3 21 1 0 6 45 0.267 0.29 0.386 0.676 83 68 2 0 2 1 0 3/D

  10. #10
    Seeker CONFUSED1's Avatar
    Join Date
    Dec 2018
    Posts
    15
    Articles
    0
    Excel Version
    2010, 2013
    Quote Originally Posted by Ron Coderre View Post
    More complicated data....I'm assuming new rules apply.
    Can you select a person and describe what data should be returned? (Maybe Matt Adams...has 3 records)
    I would like to see Matt Adams with his team as WSN and not TOT. I need his first occurance which is the row with TOT in the 'Tm' column as this row shows his complete stats for the season. But I need TOT to be replaced with WSN (the team name) in the 'Tm' column from the row below as this is his current team.

    The purpose of this exercise is so I can do an index match to pull data from this table. I require the Player Name and Team Name for the index match to be sure I am getting the correct player and not someone with the same name. If all names were unique I could just index match the player name, unfortunately that's not the case.

    I hope that makes sense...I've attached my excel file.
    Attached Files Attached Files

Page 1 of 3 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
  •