Forum Discussion
undefined
Hello everyone,
I'll try to explain all quick and short and add example files.
I have data from a survey which shows category 1-5, rating 1-5, and weighting 1-5 in a wide list.
Since category 1-5 all contain the same kind of category, it makes sense to show them in long format.
If I unpivot, categories come in rows, which is good. But I want to multiply rating x weighting, which should only be in 2 seperate columns, for category 1, only rating 1 and weighting and weighting 1 should be in the columns behind category. How can I do that?
Example file shows data in sheet 1, and how it is supposed to look like in sheet 2: Example File
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
ResultThe 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.
2 Replies
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
ResultThe 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.