Forum Discussion
Solved
Get Survey Summary By Org
I need to get a summary of Survey Response by ORG. For example, I need to get % summary of Survey Responses for ORG 1 --> Grouped By Category (Cat1, Cat 2, Cat3) -->drill down to individual Q's 1 ...
Hi PowerBiNJ ,
Please update the applied codes in your Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdK7DoMwDAXQX6kyM0AePPbufADK0AExgRDi/4VAqsDmXpqhg4co8okdu+tMuwyvwmTm3c+fZR37aT2Oe+QPIe9jBiFLEopLSJRADiRqUD7yhayEPEE4SKBA2kGwhJyESpLAg0BVQjX4sxVUw8nc2/1ZUUPaYh9/VuTVQuKtuyunxqSn4aMl4DVZOOWU5Q5KckBJWwEt+b9JgXbBOoxxAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org Name" = _t, #"Department Name" = _t, #"Cat 1 Level 1 Q1" = _t, #"Cat 1 Level 1 Q2" = _t, #"Cat 1 Level 2 Q1" = _t, #"Cat 1 Level 2 Q2" = _t, #"Cat 1 Level 2 Q3" = _t, #"Cat 2 Level 1 Q1" = _t, #"Cat 2 Level 2 Q1" = _t, #"Cat 2 Level 2 Q2" = _t, #"Cat 2 Level 2 Q3" = _t, #"Cat 2 Level 3 Q1" = _t, #"Cat 2 Level 3 Q2" = _t, #"Cat 3 Level 3 Q3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org Name", type text}, {"Department Name", type text}, {"Cat 1 Level 1 Q1", Int64.Type}, {"Cat 1 Level 1 Q2", Int64.Type}, {"Cat 1 Level 2 Q1", Int64.Type}, {"Cat 1 Level 2 Q2", Int64.Type}, {"Cat 1 Level 2 Q3", Int64.Type}, {"Cat 2 Level 1 Q1", Int64.Type}, {"Cat 2 Level 2 Q1", Int64.Type}, {"Cat 2 Level 2 Q2", Int64.Type}, {"Cat 2 Level 2 Q3", Int64.Type}, {"Cat 2 Level 3 Q1", Int64.Type}, {"Cat 2 Level 3 Q2", Int64.Type}, {"Cat 3 Level 3 Q3", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Cat 1 Level 1 Q1", "Cat 1 Level 1 Q2", "Cat 1 Level 2 Q1", "Cat 1 Level 2 Q2", "Cat 1 Level 2 Q3", "Cat 2 Level 1 Q1", "Cat 2 Level 2 Q1", "Cat 2 Level 2 Q2", "Cat 2 Level 2 Q3", "Cat 2 Level 3 Q1", "Cat 2 Level 3 Q2", "Cat 3 Level 3 Q3"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Cat ","Cat", Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Level ","Level",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Category", "Level", "Question"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"Level", type text}, {"Question", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Question]), "Question", "Value", List.Sum) in #"Pivoted Column"Best Regards
Hi PowerBiNJ ,
I created a sample pbix file(see the attachment), please check if that is what you want.
Best Regards
Hi, thank you for working on this...Is it possible to not merge all Q1,Q2,Q3 together? Every Question is different in in Category and Level.
Can i just remove the last part "Question" of the Transform Colummn Type-->Split By Column?
Hi PowerBiNJ ,
Please update the applied codes in your Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdK7DoMwDAXQX6kyM0AePPbufADK0AExgRDi/4VAqsDmXpqhg4co8okdu+tMuwyvwmTm3c+fZR37aT2Oe+QPIe9jBiFLEopLSJRADiRqUD7yhayEPEE4SKBA2kGwhJyESpLAg0BVQjX4sxVUw8nc2/1ZUUPaYh9/VuTVQuKtuyunxqSn4aMl4DVZOOWU5Q5KckBJWwEt+b9JgXbBOoxxAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Org Name" = _t, #"Department Name" = _t, #"Cat 1 Level 1 Q1" = _t, #"Cat 1 Level 1 Q2" = _t, #"Cat 1 Level 2 Q1" = _t, #"Cat 1 Level 2 Q2" = _t, #"Cat 1 Level 2 Q3" = _t, #"Cat 2 Level 1 Q1" = _t, #"Cat 2 Level 2 Q1" = _t, #"Cat 2 Level 2 Q2" = _t, #"Cat 2 Level 2 Q3" = _t, #"Cat 2 Level 3 Q1" = _t, #"Cat 2 Level 3 Q2" = _t, #"Cat 3 Level 3 Q3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org Name", type text}, {"Department Name", type text}, {"Cat 1 Level 1 Q1", Int64.Type}, {"Cat 1 Level 1 Q2", Int64.Type}, {"Cat 1 Level 2 Q1", Int64.Type}, {"Cat 1 Level 2 Q2", Int64.Type}, {"Cat 1 Level 2 Q3", Int64.Type}, {"Cat 2 Level 1 Q1", Int64.Type}, {"Cat 2 Level 2 Q1", Int64.Type}, {"Cat 2 Level 2 Q2", Int64.Type}, {"Cat 2 Level 2 Q3", Int64.Type}, {"Cat 2 Level 3 Q1", Int64.Type}, {"Cat 2 Level 3 Q2", Int64.Type}, {"Cat 3 Level 3 Q3", Int64.Type}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Cat 1 Level 1 Q1", "Cat 1 Level 1 Q2", "Cat 1 Level 2 Q1", "Cat 1 Level 2 Q2", "Cat 1 Level 2 Q3", "Cat 2 Level 1 Q1", "Cat 2 Level 2 Q1", "Cat 2 Level 2 Q2", "Cat 2 Level 2 Q3", "Cat 2 Level 3 Q1", "Cat 2 Level 3 Q2", "Cat 3 Level 3 Q3"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Cat ","Cat", Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Level ","Level",Replacer.ReplaceText,{"Attribute"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Category", "Level", "Question"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"Level", type text}, {"Question", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Question]), "Question", "Value", List.Sum) in #"Pivoted Column"Best Regards