cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Concat Member
Member

Counting occurence of string over many columns

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Counting occurence of string over many columns

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"

 

Specializing in Power Query Formula Language (M)
7 REPLIES 7
Concat Member
Member

Taking the average of string values

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.

Super User
Super User

Re: Taking the average of string values

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

 

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: Counting occurence of string over many columns

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"

 

Specializing in Power Query Formula Language (M)
Concat Member
Member

Re: Counting occurence of string over many columns

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?

Super User
Super User

Re: Counting occurence of string over many columns

Thanks.

 

Yes, the code goes into the Advanced Editor.

You can read in my original post how it was created.

 

Specializing in Power Query Formula Language (M)
Moderator v-yuezhe-msft
Moderator

Re: Taking the average of string values

Hi @Concat,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Concat Member
Member

Re: Taking the average of string values

They are the same dataset.  Different, but closely related issues.  You can merge.  Apologies.