Bulk Renaming Columns after Splitting by Delimiter

[HelenBrunyee]

Hello Everyone,

I've had a good search through this forumn but cannot find exactly what I'm after, so I was hpoing someone could help.

I have had to split a huge column by delimiter, which has, in turn, split into 64 columns, so I am trying to find a way of avoiding having to go through every single one of these 64 columns and renaming them all manually. They currently look like:

data.1|data.2|data.3|data.4|data.5|data.6|data.7 etc.

Is there any quick way of renaming so Data.1 becomes ID, Data.2 = Permit Ref, Data.3 = EndDate then repeat it so Data4 is ID(1), Data5 is Permit Ref(1), Data6 is EndDate(1) and so on?? (I understand that you can't have the same column name twice, so happy for the brackets to be in there to illiminate this issue)

In Excel, I would type the first 3 column names, highlight and drag so that the sequence is copied, if that helps explain what I want better 🙂

Any help is much apprecited & will save me so much manual work - thank you! 🙂

[jennratten]

Hello - in this solution you input the number of columns that should be in each set (3), the script calculates the number of sets that exist in the table, renames those columns and does not rename any extra columns.  A sample file is attached.

SCRIPT

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DY4JEcQwDAO5HILIlpwES6dMFvwVwD7P8xNFY8KwOVy0kFChRkZBgzY66FKL+piimjIVaqhNHerSixb9KZs2HXroTR/64oWFC39F4+DBGx98ySIiRZp8QyFDNjnkMosRU0wz/r3vHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"data.1|data.2|data.3|data.4|data.5|data.6|data.7|data.8|data.9|data.10|data.11|data.12|data.13|data.14|data.15|data.16|data.17|data.18|data.19|data.20|data.21|data.22|data.23|data.24|data.25|data.26|data.27|data.28|data.29|data.30|data.31|data.32|data.33|data.34|data.35|data.36|data.37|data.38|data.39|data.40|data.41|data.42|data.43|data.44|data.45|data.46|data.47|data.48|data.49|data.50|data.51|data.52|data.53|data.54|data.55|data.56|data.57|data.58|data.59|data.60|data.61|data.62|data.63|data.64" = _t]),
    SplitCol = Table.SplitColumn(Source, Table.ColumnNames(Source){0}, Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), Text.Split(Table.ColumnNames(Source){0},"|")),
    OldNames =Table.ColumnNames( SplitCol ),
    NumberInEachSet = 3,
    NumberOfSets = Number.IntegerDivide(List.Count(Table.ColumnNames( SplitCol )),NumberInEachSet),
    NewNames = List.Transform(List.Combine(List.Transform({0..NumberOfSets}, each {"ID("&Text.From(_)&")", "Permit Ref("&Text.From(_)&")", "EndDate("&Text.From(_)&")"})), each Text.Replace(_, "(0)", "") ),
    RenameColumns = Table.RenameColumns( SplitCol, List.Range(List.Zip({OldNames,NewNames}),0,NumberInEachSet*NumberOfSets))
in
 RenameColumns

BEFORE

RESULT

[Vijay_A_Verma · Accepted solution]

You can use this where #"Split Column by Delimiter" is last step and it will generate names like "ID","Permit Ref","EndDate", "ID1","Permit Ref1","EndDate1", "ID2","Permit Ref2","EndDate2" and so on. It is not dependent upon how many columns do you have

= Table.TransformColumnNames(#"Split Column by Delimiter",(x)=> if Text.Start(x,5)="data." then {"ID","Permit Ref","EndDate"}{Number.Mod(Number.From(Text.AfterDelimiter(x,"."))-1,3)} else x)

 See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DY6xEcMwDMR2cY1CzycpaRafi+TiBbx/ER8KlMB5Hr/7OTju5+X1hy8mKZrJYqOBhAIZJSrUaKKFNjEIEUGYSKKIJiaxiI0HFg5snLhw44kX3uQgRQb5FpMssslJLnJTgxIVlKl3qKimJrWoTQ9adNCm87iuPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColA = _t, ColB = _t, data = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"data.1", "data.2", "data.3", "data.4", "data.5", "data.6", "data.7", "data.8", "data.9", "data.10", "data.11", "data.12", "data.13", "data.14", "data.15", "data.16", "data.17", "data.18", "data.19", "data.20", "data.21", "data.22", "data.23", "data.24", "data.25", "data.26", "data.27", "data.28", "data.29", "data.30", "data.31", "data.32", "data.33", "data.34", "data.35", "data.36", "data.37", "data.38", "data.39", "data.40", "data.41", "data.42", "data.43", "data.44", "data.45", "data.46", "data.47", "data.48", "data.49", "data.50", "data.51", "data.52", "data.53", "data.54", "data.55", "data.56", "data.57", "data.58", "data.59", "data.60", "data.61", "data.62", "data.63", "data.64"}),
    Custom1 = Table.TransformColumnNames(#"Split Column by Delimiter",(x)=> if Text.Start(x,5)="data." then {"ID","Permit Ref","EndDate"}{Number.Mod(Number.From(Text.AfterDelimiter(x,"."))-1,3)} else x)
in
    Custom1

[HelenBrunyee]

Oh my gosh! I cannot tell you how helpful this is! Worked a treat - thank you so so much! You have saved me countless hours of manual work (especially as more and more columns get added!) - genius! 😄 thanks again, relaly appreciate it!

[Anonymous]

I don't think it is possible to make the transformations you ask for (at least as you asked for them), since 64 is not a multiple of 3.

In any case, see if this is for you ...

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hc+7EcMgFEXBXhQ78PthuxaN+m/DLMEhusvAfV9xva7c1a53s1u7z+67++3i7bAM07AN47AO87APIIgk8txNJJFEEkkkkUQSRRRR5zlEEUUUUUQRRTTRRBN9fkA00UQTTTQxxBBDDDHn08QQQwwxxCIWsYhFrL6e5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [data.1 = _t, data.2 = _t, data.3 = _t, data.4 = _t, data.5 = _t, data.6 = _t, data.7 = _t, data.8 = _t, data.9 = _t, data.10 = _t, data.11 = _t, data.12 = _t, data.13 = _t, data.14 = _t, data.15 = _t, data.16 = _t, data.17 = _t, data.18 = _t, data.19 = _t, data.20 = _t, data.21 = _t, data.22 = _t, data.23 = _t, data.24 = _t, data.25 = _t, data.26 = _t, data.27 = _t, data.28 = _t, data.29 = _t, data.30 = _t, data.31 = _t, data.32 = _t, data.33 = _t, data.34 = _t, data.35 = _t, data.36 = _t, data.37 = _t, data.38 = _t, data.39 = _t, data.40 = _t, data.41 = _t, data.42 = _t, data.43 = _t, data.44 = _t, data.45 = _t, data.46 = _t, data.47 = _t, data.48 = _t, data.49 = _t, data.50 = _t, data.51 = _t, data.52 = _t, data.53 = _t, data.54 = _t, data.55 = _t, data.56 = _t, data.57 = _t, data.58 = _t, data.59 = _t, data.60 = _t, data.61 = _t, data.62 = _t, data.63 = _t, data.64 = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"data.1", Int64.Type}, {"data.2", Int64.Type}, {"data.3", Int64.Type}, {"data.4", Int64.Type}, {"data.5", Int64.Type}, {"data.6", Int64.Type}, {"data.7", Int64.Type}, {"data.8", Int64.Type}, {"data.9", Int64.Type}, {"data.10", Int64.Type}, {"data.11", Int64.Type}, {"data.12", Int64.Type}, {"data.13", Int64.Type}, {"data.14", Int64.Type}, {"data.15", Int64.Type}, {"data.16", Int64.Type}, {"data.17", Int64.Type}, {"data.18", Int64.Type}, {"data.19", Int64.Type}, {"data.20", Int64.Type}, {"data.21", Int64.Type}, {"data.22", Int64.Type}, {"data.23", Int64.Type}, {"data.24", Int64.Type}, {"data.25", Int64.Type}, {"data.26", Int64.Type}, {"data.27", Int64.Type}, {"data.28", Int64.Type}, {"data.29", Int64.Type}, {"data.30", Int64.Type}, {"data.31", Int64.Type}, {"data.32", Int64.Type}, {"data.33", Int64.Type}, {"data.34", Int64.Type}, {"data.35", Int64.Type}, {"data.36", Int64.Type}, {"data.37", Int64.Type}, {"data.38", Int64.Type}, {"data.39", Int64.Type}, {"data.40", Int64.Type}, {"data.41", Int64.Type}, {"data.42", Int64.Type}, {"data.43", Int64.Type}, {"data.44", Int64.Type}, {"data.45", Int64.Type}, {"data.46", Int64.Type}, {"data.47", Int64.Type}, {"data.48", Int64.Type}, {"data.49", Int64.Type}, {"data.50", Int64.Type}, {"data.51", Int64.Type}, {"data.52", Int64.Type}, {"data.53", Int64.Type}, {"data.54", Int64.Type}, {"data.55", Int64.Type}, {"data.56", Int64.Type}, {"data.57", Int64.Type}, {"data.58", Int64.Type}, {"data.59", Int64.Type}, {"data.60", Int64.Type}, {"data.61", Int64.Type}, {"data.62", Int64.Type}, {"data.63", Int64.Type}, {"data.64", Int64.Type}}),
    oldnames=Table.ColumnNames( #"Modificato tipo"),
    newnames=List.Combine(List.Transform({0..21}, each {"ID_"&Text.From(_), "Permit Ref_"&Text.From(_), "EndDate_"&Text.From(_)})),
    trc=Table.RenameColumns( #"Modificato tipo", List.Range(List.Zip({oldnames,newnames}),0,64))
in
 trc

[HelenBrunyee]

Thanks for giving this a go for me! Apologies, I should have been more specific in sayign there are 64 for now, but will no doubt get added too. I think you've seen @Vijay_A_Verma 's solution, which worked really well. Thanks for your time anyway! 🙂