Forum Discussion
Power BI Matrix Sort Rows By Value
Hi All,
I have a set of data I am using from surveys that is like so: Every question answered is a row in the table with the followiung attributes, a responseid attribute that is unique for each person who has taken a survey, a questionid and questiontext attribute for each question, and order attribute denoting the order each question appears in, and an answertext attribute denoting the text a user entered for a question. Every particular question in a survey is going to have the same questionid, questiontext, and order attributes.
I am trying to organize this data into a matrix, with responseid defining the columns, questionid defining the rows, and the answertext defining the values where a row and column meet.
However, I would also like the question text to be what is display for each row label along the left, and I would like the rows to appear in order according to their order attribute. How can I implement this?
Thanks
Jared Leonard
Glad to hear that you making progress. I have enclosed a picture of a test data and how to add an index column column in the query editor, this can then be sorted in Desktop.
You can add the same to your dimesions Questions and then when you are in the desk top view sort the column by that row.
The code you need is= Table.AddIndexColumn(#"YOUR PREVIOUS STEP", "COLUMN NAME", 1, 1, Int64.Type)The column in the UI will add your index
Then when in desk top choose the table column and then the sort by option and choose the index, this will then sort your matrix in the right ordser for you.
Chose the column you want to sort and then sort by
If you need to provide dummy data, then this can be pasted in here or you can email it across if that is easier for you.
Hope this helps.
kind regards
11 Replies
- DemoFour
Continued Contributor
Hi Jared_Leonard
Have you got a dimension table of your questions with a sort order column, as this would help to sort the matrix in to question order.In my picture the Question ID is also numerical so I can sort any visual by this patten.
Hope this helps.
Sorry, I'm still new to Power BI, is that under Model View? And what options did you choose to create those tables? Are they pulled directly from a datasource or create using an existing datasource?
- DemoFour
Hi Jared_Leonard ,
Not a probem, that is a picture of my model I built for a survey on theModel tab, I split the flat file in the query editor to create a star schema - or data model.
Check out here Microsoft guidance - star-schema for an overview.
