CODEWITHSUNDEEP
powerquery
Sas
Sas

Reputation: 51

Modifying specific column headers in Excel Power Query

I have an Excel table that, in addition to providing a number of other filters/presentations, contains a handful of variable headers that I want to convert into consistent column names so that the rest of the M code can be straightforward.

One set of headers might look like this

AAA BBB CCC A Tool One - Site1 Another Tool - Site1 Third and Final Tool - Site1 DDD

A second set of headers might look like this

AAA BBB CCC A Tool One - SecondSite Another Tool - SecondSite Third and Final Tool - SecondSite DDD

A third set of headers might look like this

AAA BBB CCC A Tool One - NoThreeLocale Third and Final Tool - NoThreeLocale DDD

All relevant column headers will be in the format of:

I would like to make all of them consistent, junking everything after and including the SpaceHyphenSpace leaving me with the following (all headers trimmed):

AAA BBB CCC A Tool One Another Tool Third and Final Tool DDD

As shown in the third example, it is possible that all sites will not have all tools present, and I'd like to only have headers for columns which will have data.

I've tried several iterations but cannot get past creating the new column names. I think that once I do, I have the correct code that will do the renaming:

let
    Source = Excel.CurrentWorkbook(),
    UserExport = Source{[Name="UserExport"]}[Content],
    #"Removed Blank Rows" = Table.SelectRows(UserExport, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),

    OldNames = List.Select( Table.ColumnNames(#"Promoted Headers"), each (Text.Contains(_, "Tool")) ),
    NewNames = Table.FromList({OldNames},Splitter.SplitTextByDelimiter("-") ),
    Renamed = List.Zip({OldNames,NewNames}),
    Custom = Table.RenameColumns(Navigation,Renamed),
)
in
    #"Added Custom1"

For Site1, OldNames gives me ||List| |---|---| |1|A Tool One - Site1| |2|Another Tool - Site1| |3|Third and Final Tool - Site1|

But NewNames throws the following error

Expression.Error: We cannot convert a value of type List to type Text.
Details:
    Value=[List]
    Type=[Type]

I've tried a dozen other snippets to get NewNames to be in the same format as OldNames, but I get similar errors that complain about format mismatches every time, regardless if I'm converting to values, lists, or tables. The closest I've come (with a wholly different set of M code for NewNames) left me with |OldNames|NewNames| |---|---| |A Tool One - Site1|List| |Another Tool - Site1|List| |Third and Final Tool - Site1|List|

Any help would be greatly appreciated.

Upvotes: 0

Views: 676

Answers (2)

Frédéric LOYER
Frédéric LOYER

Reputation: 1064

I have a little query which seems to do the job:

let
    Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
    Renamed = Table.RenameColumns(Source,
                        List.Transform(Table.ColumnNames(Source),
                                       each {_,let p = Text.PositionOf(_,"-")
                                               in 
                                                  if p=-1 
                                               then _ 
                                                  else Text.Trim(Text.Start(_,p))}))
in
    Renamed

To make the query simpler, I rename all the columns... and sometimes, the new name is equal to the old one.

Note: this version will strip "-" alone and also " -", with extra hyphens.

Upvotes: 1

horseyride
horseyride

Reputation: 21393

You could try

Rename= Table.RenameColumns( #"PriorStepGoesNameHere", List.Zip( { Table.ColumnNames( Source ),List.Transform(Table.ColumnNames(Source), each Text.Split(_," -"){0})}))

replace Table.ColumnNames(Source) with some other list of column names if you plan to apply this to some subset, like perhaps OldNames

Upvotes: 1

Related Questions