Removing last characters of multiple columns headers

[newbie74]

Dear community,

i am trying to remove the last 3 characters of multiple columns' headers.

see my table below - i have 3 characters following the month and year.

As i will need to redo the same with different characters at every update, i need to find a way to automatize this in M.

Many thanks in advance for your help.

[newbie74]

Never got the chance to thank you guys for the help!

THANKS A LOT!

[FrankAT · Accepted solution]

Hi @newbie74 

you can do it like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0lXSwYJjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Apr-21_27" = _t, #"May-21_28" = _t, #"Jun-21_29" = _t, #"Jul-21_30" = _t, #"Aug-21_31" = _t, #"Sep-21_32" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Demoted Headers", {{"Column1", each Text.BeforeDelimiter(_, "_"), type text}, {"Column2", each Text.BeforeDelimiter(_, "_"), type text}, {"Column3", each Text.BeforeDelimiter(_, "_"), type text}, {"Column4", each Text.BeforeDelimiter(_, "_"), type text}, {"Column5", each Text.BeforeDelimiter(_, "_"), type text}, {"Column6", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Text Before Delimiter", [PromoteAllScalars=true])
in
    #"Promoted Headers"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[amitchandak]

@newbie74 , Unpivot the table.

Then use Text. Start, Text.mid and Text.End to split the date and month

if need you can pivot

https://radacad.com/pivot-and-unpivot-with-power-bi

Text.Start, Mid -https://www.youtube.com/watch?v=vky4wPqm0O0