M/Power Query - rename all columns, based on text and delimiters

[vyacheslavg]

Dear community, 

Could you please help with hint on M code to do the following.

I have several columns in table, with headers similar to this:

abc[some good text]xyz.

I need to rename all column headers  from these 

abc[some good text]xyz

to 

some good text 

In other words, leave only the text between [ ] and remove everything else. 

I figured out, that I will most probably  need Table.Renamecolumns and Text.Betweendelimeters functions -  but I can't combine it into the working M code, exceeds my current capabilities... 

[sdjensen]

@vyacheslavg - you could perhaps try with the transpose feature that will switch your columns to rows (and rows to columns) and then make one (or more) search/replace on the first column (that used to be your column headers) and after you make your search/replace you transpose the table again to put columns back as columns.

[MarcelBeug · Accepted solution]

Actually, Table.TransformColumnNames is the better choice.

Example (assuming if "[" is present, the "]" will also be there):

let
    Source = Table1,
    RenamedColumns = Table.TransformColumnNames(Source, each if Text.Contains(_,"[") then Text.BetweenDelimiters(_,"[","]") else _)
in
    RenamedColumns

[vyacheslavg]

Marcel, thanks a lot. 

It looks very close to a solution (I need to test it first).

Edited:

It worked!

Spent some time trying to figure out strange results - I forgot to promote headers first. After that everything worked just great.

Could you please advise some good training or book  for Power Query/M?

I've found the "M for the Data Monkey", but it seems to be so heavy PowerPivot related... Want to find something more PBD and SSDT focused.