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

C

CONFUSED1

New member
Joined
Dec 5, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
2013, Office 365
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!
 
.
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
 

Attachments

  • Find n Replace.xlsm
    16.6 KB · Views: 10
How and where do I put this for Power Query Table?

THanks for the help and sorry for my lack of experience.
 
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?
 
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"
 
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!
 
....
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"
 
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)
 
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.

RkFirst NameInitialLast NameInitLnameAgeTmLgGPAABRH2B3BHRRBISBCSBBSOBAOBPSLGOPSOPS+TBGDPHBPSHSFIBBPos Summary
5Austin L.A.AdamsA.Adams27WSNNL2000000000000000000/1
6ChanceC.AdamsC.Adams23NYYAL1000000000000000000/1
7LaneL.AdamsL.Adams28ATLNL262925106102610480.240.3450.520.8651301310000/987
8MattM.AdamsM.Adams29WSNNL121337306427310021570027730.2390.3090.4770.7861051466400337/D9
9MattM.AdamsM.Adams29NL9427724937649018480024550.2570.3320.510.8421181276400237/D9
10MattM.AdamsM.Adams29STLNL276057591039003180.1580.20.3330.5334319000013
11JimJ.AdduciJ.Adduci33DETAL59185176194782321106450.2670.290.3860.6768368202103/D
 
Ron Coderre said:
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)
Click to expand...

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.
 

Attachments

  • bbrefbatterdata.xlsm
    485.8 KB · Views: 12
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?
 
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?
 
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
 

Attachments

  • bbrefbatterdata_BS_PQ.xlsm
    458.9 KB · Views: 9
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("[URL]https://www.baseball-reference.com/leagues/MLB/2018-standard-batting.shtml#players_standard_batting::none[/URL]")),
    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?
 
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.
 
You must log in or register to reply here.
Back
Top