Average from a Filtered Column with Mixed Text and Numerical Data

If it helps anyone, I was able to solve the problem. The mix of text and numerical data is an issue. To get what I needed, I selected "Transform Data" from the top ribbon, which gives you the option of duplicating the table if you right click the one to be duplicated, from the list of tables on the left of the screen. I think that's important because the duplicate option does not seem to be a part of the "Edit Query" section.

I renamed the table and was able to work with the data. To get my averages, I limited their information by selecting the down arrow in the column header, which lets you specify what goes into that column. For me, I limited the column to the "Question_ID"s that had numerical data and that I needed to average. I also removed a column that had text in it and I did not need. I don't know if that helped or not but since I didn't need that specific data in the duplicated table, it surely didn't hurt. I did keep another column with text that helped me keep track of the thing I was working on.

Then I returned to the report page with my visualization. In the list of tables on the right, my duplicated table contained the "Question_ID" and "Question_User_Response" columns. What was really important here was selecting the "Question_User_Response" column, which contained numbers that were formatted as text. and then, under "Column Tools" in the top ribbon I changed the "Data Type" to "Whole number." The text numbers became numerically formatted and, for other newbies, you can tell because a Sigma symbol appears before the column name under the duplicated table. 

Then I was able to use the regular filtering fields and average function in Power BI to get what I needed. To be specific, I created a table from the Visualizations section and selected my now numerical "Question_User_Reponse" column. Then I dragged "Question_ID" into the filters section, where I could select the exact "Question_ID" I needed. Then, since it was numerical data, I could now select "Average" from the drop menu under "Fields" in the Visualizations section. 

Boom. I got around the mixed data, as well as my totally ignorant incompetence to get the averages I needed. I cross-referenced these results with what I got from the original reporting system that was feeding the data via SQL and it's accurate. 

A couple takeaways for other newbies. Power BI does not seem to like mixed numerical and text data. Numerical data can be worked with using the stock drag and drop or selection tools, which are basic but effective. And numerical data that you can work with like that has a sigma symbol in front of the column name. Whew, even though this is very far from my actual job responsibilities, I'm not getting canned today! 😀