Forum Discussion
Solved
Bulk Renaming Columns after Splitting by Delimiter
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 ha...
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
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
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! 🙂