I have a table that contains customer survey data. I have created categories for strength and weakness of the company based on the responses.
I want to score the strengths accordingly. Strength 1 columns is scored 3, strength 2 as 2 and strength 3 as 1.
I have created the output table that I want in excel.
I need guidance on how to do the same in power bi.
Hi Shruthi_21
Load the survey data from Excel into Power BI and click Transform data to open Power Query Editor. Then transform the data to get your expected output.
You can create a blank query and open Advanced editor, then paste below codes to replace the default one to check the steps. Also attach the pbix for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJBDoIwEPxK03MT3oAkeFMTvREOtCzSCF3TFhN+b6uJkUoRLk0mszO7O9uioBcAYmUPJiE7xJtUV8roSWNC3ENMi3eHUyHAGMllJ+2YkAzVA5QEJYCWLOKxoGH0kMaFuXZVdTeSHnoO2nFZN/A44Y2cH3s/eSV8RwnmRXxBRveItRtk0AYC5Eun7NlWTdP8LTvaFvTybNF9Pi1mYvDC8AohXug5G+yWtDeaBKHHNl7zJ7ae//dCk5uvU5VP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Str1 Category" = _t, #"Str2 Category" = _t, #"Str3 Category" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Str1 Category", type text}, {"Str2 Category", type text}, {"Str3 Category", type text}}),
#"Categories" = Table.FromList(List.Distinct(List.Combine({#"Changed Type"[Str1 Category],#"Changed Type"[Str2 Category],#"Changed Type"[Str3 Category]}))),
#"Grouped Str1" = Table.Group(#"Changed Type", {"Str1 Category"}, {{"Str1 Count", each Table.RowCount(_), Int64.Type}}),
#"Grouped Str2" = Table.Group(#"Changed Type", {"Str2 Category"}, {{"Str2 Count", each Table.RowCount(_), Int64.Type}}),
#"Grouped Str3" = Table.Group(#"Changed Type", {"Str3 Category"}, {{"Str3 Count", each Table.RowCount(_), Int64.Type}}),
#"Merged Table 1" = Table.Join(Categories, {"Column1"}, #"Grouped Str1", {"Str1 Category"}, JoinKind.LeftOuter),
#"Merged Table 2" = Table.Join(#"Merged Table 1", {"Column1"}, #"Grouped Str2", {"Str2 Category"}, JoinKind.LeftOuter),
#"Merged Table 3" = Table.Join(#"Merged Table 2", {"Column1"}, #"Grouped Str3", {"Str3 Category"}, JoinKind.LeftOuter),
#"Removed Columns" = Table.RemoveColumns(#"Merged Table 3",{"Str1 Category", "Str2 Category", "Str3 Category"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Str1 Count", "Str2 Count", "Str3 Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Column1", "Strength Category"}, {"Str1 Count", "Strength 1"}, {"Str2 Count", "Strength 2"}, {"Str3 Count", "Strength 3"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Score", each 3 * [Strength 1] + 2 * [Strength 2] + 1 * [Strength 3])
in
#"Added Custom"
What's more, if you want to show the output in a table visual in the report, we can use DAX method to realize it.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi Shruthi_21 I'd suggest to unpivot the table so you have columns of strength and category. Once you do that it should be fairly easy to aggregate them into a matrix.
I want to create the second table in power bi. currently, its in excel. It's not a pivot table.
Right. So import the Excel file into Power BI. Using Power Query, unpivot the table so you only have columns for strength and category.
Hi Shruthi_21
Load the survey data from Excel into Power BI and click Transform data to open Power Query Editor. Then transform the data to get your expected output.
You can create a blank query and open Advanced editor, then paste below codes to replace the default one to check the steps. Also attach the pbix for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJBDoIwEPxK03MT3oAkeFMTvREOtCzSCF3TFhN+b6uJkUoRLk0mszO7O9uioBcAYmUPJiE7xJtUV8roSWNC3ENMi3eHUyHAGMllJ+2YkAzVA5QEJYCWLOKxoGH0kMaFuXZVdTeSHnoO2nFZN/A44Y2cH3s/eSV8RwnmRXxBRveItRtk0AYC5Eun7NlWTdP8LTvaFvTybNF9Pi1mYvDC8AohXug5G+yWtDeaBKHHNl7zJ7ae//dCk5uvU5VP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Str1 Category" = _t, #"Str2 Category" = _t, #"Str3 Category" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Str1 Category", type text}, {"Str2 Category", type text}, {"Str3 Category", type text}}),
#"Categories" = Table.FromList(List.Distinct(List.Combine({#"Changed Type"[Str1 Category],#"Changed Type"[Str2 Category],#"Changed Type"[Str3 Category]}))),
#"Grouped Str1" = Table.Group(#"Changed Type", {"Str1 Category"}, {{"Str1 Count", each Table.RowCount(_), Int64.Type}}),
#"Grouped Str2" = Table.Group(#"Changed Type", {"Str2 Category"}, {{"Str2 Count", each Table.RowCount(_), Int64.Type}}),
#"Grouped Str3" = Table.Group(#"Changed Type", {"Str3 Category"}, {{"Str3 Count", each Table.RowCount(_), Int64.Type}}),
#"Merged Table 1" = Table.Join(Categories, {"Column1"}, #"Grouped Str1", {"Str1 Category"}, JoinKind.LeftOuter),
#"Merged Table 2" = Table.Join(#"Merged Table 1", {"Column1"}, #"Grouped Str2", {"Str2 Category"}, JoinKind.LeftOuter),
#"Merged Table 3" = Table.Join(#"Merged Table 2", {"Column1"}, #"Grouped Str3", {"Str3 Category"}, JoinKind.LeftOuter),
#"Removed Columns" = Table.RemoveColumns(#"Merged Table 3",{"Str1 Category", "Str2 Category", "Str3 Category"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,0,Replacer.ReplaceValue,{"Str1 Count", "Str2 Count", "Str3 Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Column1", "Strength Category"}, {"Str1 Count", "Strength 1"}, {"Str2 Count", "Strength 2"}, {"Str3 Count", "Strength 3"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Score", each 3 * [Strength 1] + 2 * [Strength 2] + 1 * [Strength 3])
in
#"Added Custom"
What's more, if you want to show the output in a table visual in the report, we can use DAX method to realize it.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.