Remove brackets and contents from select columns

C

CONFUSED1

New member
Joined
Dec 5, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
2013, Office 365
I have a table and I'd like to remove all the brackets and contents for a select range in my power query table. I could split each column and then delete the columns with the bracketed data. I'm looking for a quicker cleaner way if one exits.
 
I think you need to post some sample data or workbook along with the results you'd like to see.
 
the workbook and an example result
 

Attachments

  • removebrackets.xlsm
    61.8 KB · Views: 8
Here you go....Try this M-Code (Posting files sends red flags to my I.T. department)
Code: 
let
    Source = Web.Page(Web.Contents("https://www.baseball-reference.com/leagues/MLB/2018-team-starting-lineups.shtml")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Rk", Int64.Type}, {"Tm", type text}, {"C", type text}, {"1B", type text}, {"2B", type text}, {"3B", type text}, {"SS", type text}, {"LF", type text}, {"CF", type text}, {"RF", type text}, {"DH", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Tm", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Tm.1", "Tm.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Tm.1", type text}, {"Tm.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Tm.1", "Team"}, {"Tm.2", "Record"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Rk", "Team", "Record"}, "Attribute", "Value"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(" (", QuoteStyle.Csv), {"Value.1"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Attribute]), "Attribute", "Value.1")
in
    #"Pivoted Column"

Is that something you can work with?
 
Thanks Ron,

I like what you did there using Pivot. Took me a bit to figure out how it worked but I was able to replicate the results with another similar table! I've never used Pivot before, a new tool to keep in mind.
 
You must log in or register to reply here.
Back
Top