How to rename a column header with a specific string from that header

Question

My company uses a spreadsheet to record manufacturing data, but they also use the spreadsheet to give the user instructions. Those instructions are in the column header for the data they need to record. I am trying to create a report from this spreadsheet for the Quality Engineer and I need to be able to pull a specific string from the column to use as a column name. Luckily, the string that I need is within parentheses and by demoting the headers and transposing, I was able to add a new column and extract text between delimiters. But, at this point, I do not know how to replace the original column names with the extracted one.

Ron Rosenfeld · Accepted Answer · 2021-12-11 02:09:19Z

1

No need to transpose etc.

You can just make a list of the new column names and do a rename.

The below code assumes

The columns that need to be renamed will be renamed with the portion of the header in parentheses
The parenthesized string will never be at the beginning of the existing column name
There will only be a single parenthesized string

let
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"Shop Order", Int64.Type}, {"Month", type text}, 
        {"Day", Int64.Type}, {"Year", Int64.Type}, {"Shift", Int64.Type}, 
        {"SCAN: New Antioxidant:#(lf)(P/N A14656-1)#(lf) Lot Number", type text}, 
        {"Scan: #(lf)Resin (PN A14658-1#(lf))Lot Number", type text}}),

//rename columns
    origColNames=Table.ColumnNames(#"Changed Type"),

//List.Transform modifies only those column names that have the parenthesized substring
    newColNames = List.Transform(origColNames, each let x=Text.SplitAny(_,"()") in if List.Count(x)=1 then x{0} else x{1}),

    renamedHdrs = Table.RenameColumns(#"Changed Type", List.Zip({origColNames,newColNames}))

in
    renamedHdrs

Edit with new column header data

You have added some problematic columns to your example.
It seems the column headers that you do not want modified have parenthesized data at the end of the string.
If that is always the case, you can handle that in code by changing the newColNames line:

    newColNames = List.Transform(origColNames, each 
        let x=Text.SplitAny(_,"()") 
        in 
            if List.Last(x) = "" or List.Count(x) = 1 
            then _ 
            else x{1}),

Algorithm

Split the header on the parentheses
Check the last element of the split
- If the last element is a null string ("") or if there is only one element, then return the original header; otherwise return the 2nd element.

edited Dec 11, 2021 at 2:09

answered Dec 7, 2021 at 20:55

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

David Lopez Over a year ago

Ron, the above code looks like it worked, except opposite of what was expected. It removed what was in parenthesis and left everything else. I wanted to keep what was in parenthesis and discard the rest. Thanks.

David Lopez Over a year ago

I apologize. I was looking at the wrong worksheet. On the worksheet where I applied the code above, I got an error message. Apparently, they are using parenthesis to describe the measurement type, "(lbs)", in multiple columns. This is creating duplicate column headers labeled "lbs". At least, that is what I am assuming the error message is stating:

David Lopez Over a year ago

Error Message: Expression.Error: The field 'lbs' already exists in the record. Details: Name=lbs Value=

Ron Rosenfeld Over a year ago

@DavidLopez Or you could post the problematic entries, and some method to differentiate those that are in parentheses that you want to keep, from those that you do not. If you can do that, it shouldn't be too difficult to code.

Ron Rosenfeld Over a year ago

@DavidLopez Should be possible. Why don't you give it a try and post a new question if you run into trouble. Include the problematic column headers and a list of words not needed. I'm thinking the issue could be handled by modifying the newColumnNames line to just ignore those items.

|

Collectives™ on Stack Overflow

How to rename a column header with a specific string from that header

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related