Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 30 columns and want to count the occurances of 5 different strings across the entire table, and then to weight those occurences from 1-5.
I can really only think of creating 30 measures to count the occurences in each column individually (and weight them accordingly), and then to sum up the measures. Or one gigantic nested measure. Both are too laborious to even tackle (and will probably take a long time to resolve once it's done...)
Is there a way to create one simple measure for this? Or even a new calculated column that counts the occurences across each row?
Think of a table where every column is a question from a survey, and every row makes up a respondant's selections. I want a total "score". To accomplish this in PBI in the past, I manipulated the excel file so that there were three columns. Respondant, Question and Response. One column with all the responses makes for a long column, but only requires one simple measure. But this excel manipulation (transposing and using index() function) is just as laborious as creating 30 measures (and I have to redo it every time a new survey comes about!).
As a novice, I really struggle coming from Excel where I could perform calulcations on rows too, not just columns.
Solved! Go to Solution.
This looks a lot like one of your previous questions. Would you mind reacting on that one too?
For this new question I created the following Power Query code, based on survey results from an Excel file.
The code can be used in Power BI as well.
The first 3 steps are the result of getting data from table "SurveyResults" in the specified Excel Workbook.
#"Unpivted Columns" the result from selecting all columns and chosing Unpivot Columns.
#"Grouped Rows" is the result from chosing Group By and accepting the defaults.
#"Added custom" column adds the score (part of the code copied from the answer to your previous question...).
#"Sorted Rows" is the result from sorting rows.
let Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Counting occurrence of string over many columns.xlsx"), null, true), SurveyResults_Table = Source{[Item="SurveyResults",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(SurveyResults_Table,{{"Question 1", type text}, {"Question 2", type text}, {"Question 3", type text}, {"Question 4", type text}, {"Question 5", type text}, {"Question 6", type text}, {"Question 7", type text}, {"Question 8", type text}, {"Question 9", type text}, {"Question 10", type text}, {"Question 11", type text}, {"Question 12", type text}, {"Question 13", type text}, {"Question 14", type text}, {"Question 15", type text}, {"Question 16", type text}, {"Question 17", type text}, {"Question 18", type text}, {"Question 19", type text}, {"Question 20", type text}, {"Question 21", type text}, {"Question 22", type text}, {"Question 23", type text}, {"Question 24", type text}, {"Question 25", type text}, {"Question 26", type text}, {"Question 27", type text}, {"Question 28", type text}, {"Question 29", type text}, {"Question 30", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Score", each 1 + List.PositionOf({"Strongly disagree","Disagree","Neutral", "Agree","Strongly agree"},[Value]), Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Score", Order.Ascending}}) in #"Sorted Rows"
This looks a lot like one of your previous questions. Would you mind reacting on that one too?
For this new question I created the following Power Query code, based on survey results from an Excel file.
The code can be used in Power BI as well.
The first 3 steps are the result of getting data from table "SurveyResults" in the specified Excel Workbook.
#"Unpivted Columns" the result from selecting all columns and chosing Unpivot Columns.
#"Grouped Rows" is the result from chosing Group By and accepting the defaults.
#"Added custom" column adds the score (part of the code copied from the answer to your previous question...).
#"Sorted Rows" is the result from sorting rows.
let Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Counting occurrence of string over many columns.xlsx"), null, true), SurveyResults_Table = Source{[Item="SurveyResults",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(SurveyResults_Table,{{"Question 1", type text}, {"Question 2", type text}, {"Question 3", type text}, {"Question 4", type text}, {"Question 5", type text}, {"Question 6", type text}, {"Question 7", type text}, {"Question 8", type text}, {"Question 9", type text}, {"Question 10", type text}, {"Question 11", type text}, {"Question 12", type text}, {"Question 13", type text}, {"Question 14", type text}, {"Question 15", type text}, {"Question 16", type text}, {"Question 17", type text}, {"Question 18", type text}, {"Question 19", type text}, {"Question 20", type text}, {"Question 21", type text}, {"Question 22", type text}, {"Question 23", type text}, {"Question 24", type text}, {"Question 25", type text}, {"Question 26", type text}, {"Question 27", type text}, {"Question 28", type text}, {"Question 29", type text}, {"Question 30", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Score", each 1 + List.PositionOf({"Strongly disagree","Disagree","Neutral", "Agree","Strongly agree"},[Value]), Int64.Type), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Score", Order.Ascending}}) in #"Sorted Rows"
Thanks, I appreciate your response.
I know nothing of Power Query code, so I will have to look into this. Does this go into the Advanced editor in PowerBi when editing queries?
Thanks.
Yes, the code goes into the Advanced Editor.
You can read in my original post how it was created.
Hello,
I'm using PowrBi to analyze a large set of survey results. Essentially the answers are all strongly agree, agree, neutral, disagree, strongly disagree.
Can anyone think of a way to get an average of all the values? I don't really know how to use PowerBi to assign a value to each string. Strongly agree = 5, strongly disagree = 1.
Hi @Anonymous,
I note that you post another thread in this link and the issue has been resolved, do you have same issue in the two thread? If so, I will merge them. If not, please help to post sample data and expected result for us to analyze.
Thanks,
Lydia Zhang
They are the same dataset. Different, but closely related issues. You can merge. Apologies.
Example created with Power Query in Excel. Scores get values using List.PositionOf.
let Source = Excel.CurrentWorkbook(){[Name="Answers"]}[Content], Typed = Table.TransformColumnTypes(Source,{{"Answers", type text}}), Scored = Table.AddColumn(Typed, "Score", each 1+List.PositionOf({"Strongly disagree","Disagree","Neutral", "Agree","Strongly agree"},[Answers]),Int64.Type), Average = List.Average(Scored[Score]) in Average
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |