CODEWITHSUNDEEP
powerquerypowerbi-desktop
David Lopez
David Lopez

Reputation: 39

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

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.

Sample spreadsheet and desired result

Upvotes: 0

Views: 749

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

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

enter image description here

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.

enter image description here

Upvotes: 1

Related Questions