undefined

Anonymous ,

In power query, create a custom function to select the three columns and append them using code like pattern below:

let
Source = Excel.Workbook(File.Contents("C:\Users\JimmyTao\Desktop\Client satisfaction survey.xlsx"), null, true),
#"Sheet 1_Sheet" = Source{[Item="Sheet 1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet 1_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"respondent_id", Int64.Type}, {"collector_id", Int64.Type}, {"date_created", type datetime}, {"date_modified", type datetime}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Project", type text}, {"Projektnummer", Int64.Type}, {"Deliverymanager", type text}, {"Date", type text}, {"Client", type text}, {"Column15", type any}, {"Client Organisation", type text}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Category 1", Int64.Type}, {"Rating 1", Int64.Type}, {"Category 2", Int64.Type}, {"Rating 2", Int64.Type}, {"Category 3", Int64.Type}, {"Rating 3", Int64.Type}, {"Category 4", Int64.Type}, {"Rating 4", Int64.Type}, {"Category 5", Int64.Type}, {"Rating 5", Int64.Type}, {"Column30", type any}, {"Weighting 1", Int64.Type}, {"Weighting 2", Int64.Type}, {"Weighting 3", Int64.Type}, {"Weighting 4", Int64.Type}, {"Weighting 5", Int64.Type}}),
fn = (Table as table, Column1 as text, Column2 as text, Column3 as text) => 
Table.Combine({Table, Table.RenameColumns(Table.SelectColumns(#"Changed Type", {Column1, Column2, Column3}), {{Column1, Text.Start(Column1, 8)}, {Column2, Text.Start(Column2, 6)}, {Column3, Text.Start(Column3, 9)}})}),
Table = Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Category 1", "Rating 1", "Weighting 1"}), {{"Category 1", "Category"}, {"Rating 1", "Rating"}, {"Weighting 1", "Weighting"}}),
Result = fn(fn(fn(fn(Table, "Category 2", "Rating 2", "Weighting 2"), "Category 3", "Rating 3", "Weighting 3"), "Category 4", "Rating 4", "Weighting 4"), "Category 5", "Rating 5", "Weighting 5")
in
Result

The result is as below:

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.