Forum Discussion
Helper IGive a score base on text
I have a survey with the following results
| Response | Q1 | Q2 | Q3 |
| 1 | A | A;D | E;F |
| 2 | C | B;C | G |
| 3 | D | C | H |
Suppose I wanted to give them a score base on the text
like to A & G = +1, B,C,D,E,F & H=-1
| Response | Q1 | Q2 | Q3 | Total |
| 1 | 1 | 1-1 | -1-1 | -1 |
| 2 | -1 | -1-1 | 1 | -2 |
| 3 | -1 | -1 | -1 | -3 |
What would be the best way of doing this, I have 20 odd questions
Hi RichardTam
Use Power Query to transform your table may be easier.
Different form lkalawski , I use merge excpet replacing each value in the table, and split column may choose advance setting to split into row.
I build two tables to achieve your goal.
Table1:
Score Table:
Duplicate Table1 ,rename as Merge Table and do the transform.
Split into row can reduce one step.
Then Merge "Merge"Table and Score Table by Category Column and Value column. Finally Use group by to get the result.
You can copy this code into Advance Editor and get the result you want.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYWsXIOlq7aYUqxOtZARkOwOxkzWIdAeLGQNZLlBxD6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Response = _t, Q1 = _t, Q2 = _t, Q3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Response", Int64.Type}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Q1", "Q2", "Q3"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Value"}, Score, {"Category"}, "Score", JoinKind.LeftOuter), #"Expanded Score" = Table.ExpandTableColumn(#"Merged Queries", "Score", {"Score"}, {"Score.Score"}), #"Grouped Rows" = Table.Group(#"Expanded Score", {"Response", "Attribute"}, {{"Sum", each List.Sum([Score.Score]), type nullable number}}) in #"Grouped Rows"Result:
Then use this table to build a Matrix visual.
You can download the pbix file from this link: Give a score base on text
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- lkalawski
Resident Rockstar
Hi RichardTam
In my opinion, you should follow these steps.
1. Separate responses where there is a separator. You will have the answers in a separate row.
2. Convert letters into numbers.
3. Make the calculation.I prepared a file with this solution for you.
_______________
If I helped, please accept the solution and give kudos! 😀 - FrankAT
Community Champion
Hi RichardTam
you can do it with Powert Query like this:
// Table // Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYWsXIOlq7aYUqxOtZARkOwOxkzWIdAeLGQNZLlBxD6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Response = _t, Q1 = _t, Q2 = _t, Q3 = _t]), #"Inserted Merged Column" = Table.AddColumn(Source, "Merged", each Text.Combine({[Q1], [Q2], [Q3]}, ";"), type text), #"Added Custom1" = Table.AddColumn(#"Inserted Merged Column", "Total", each List.Sum(List.Transform(Text.Split([Merged],";"), each if _ = "A" or _ = "G" then 1 else -1))), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Merged"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Response", "Total"}, "Attribute", "Value"), #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "NewList", (OT) => Text.Combine(List.Transform(Text.Split(OT[Value],";"), each if _ = "A" or _ = "G" then Text.From(1) else Text.From(-1) ),";"),type text), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "NewList"), #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Response", "Q1", "Q2", "Q3", "Total"}) in #"Reordered Columns"With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut) Hi RichardTam
Use Power Query to transform your table may be easier.
Different form lkalawski , I use merge excpet replacing each value in the table, and split column may choose advance setting to split into row.
I build two tables to achieve your goal.
Table1:
Score Table:
Duplicate Table1 ,rename as Merge Table and do the transform.
Split into row can reduce one step.
Then Merge "Merge"Table and Score Table by Category Column and Value column. Finally Use group by to get the result.
You can copy this code into Advance Editor and get the result you want.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYWsXIOlq7aYUqxOtZARkOwOxkzWIdAeLGQNZLlBxD6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Response = _t, Q1 = _t, Q2 = _t, Q3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Response", Int64.Type}, {"Q1", type text}, {"Q2", type text}, {"Q3", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Q1", "Q2", "Q3"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Value"}, Score, {"Category"}, "Score", JoinKind.LeftOuter), #"Expanded Score" = Table.ExpandTableColumn(#"Merged Queries", "Score", {"Score"}, {"Score.Score"}), #"Grouped Rows" = Table.Group(#"Expanded Score", {"Response", "Attribute"}, {{"Sum", each List.Sum([Score.Score]), type nullable number}}) in #"Grouped Rows"Result:
Then use this table to build a Matrix visual.
You can download the pbix file from this link: Give a score base on text
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
- Greg_Deckler
RichardTam - Which of these solutions did you end up going with?
