Sorting by Completion Time

[jcastr02]

See 30 day tab - boxes with background color in red are sorted by completion time , but ones in  purple background will not allow me to sort by completion time.  i've filtered out blanks, but it still gives error message, see pbix file.  

https://1drv.ms/u/s!Ava_12t7CNJtkEcQHD-Ralk-Qgjf 

[itsme]

This is happening because you have multiple values in one column that are identical and trying to sort them by different completion times. For example, take a look at the '30 Day - Additional Support Needed' table. In the 'What can we do to provide additional support to improve you experience?' column, there are 2 identical values, "change my schedule". Both of those values have different corresponding Completion Times.

To solve this, I would go to PowerQuery (PQ) and create a couple of new column in those tables that you can sort by. Start by clicking on the Transform Data button to get into PQ and go to the tables you need to edit. Using the same table as an example, duplicate the 'Completion Time' column and change it to decimal type. You will delete this column after we create the next column. For the next column, from the Add Column tab click on the Custom Column button. Give it whatever name you want, maybe something like "ColumnSortID". Type in this formula:

Number.ToText([#"Completion Time - Copy"])&[#"What can we do to provide additional support to improve your experience?"]

Change it to text type, delete the previous column created (Completion Time - Copy), hit the Close & Apply button in the Home tab and wait for the changes to load.

Now, you can click on the 'What can we do to provide additional support to improve your experience?' column, under the Column Tools tab click the Sort by Column button and choose the new column we created.

I hope this helps!

[jcastr02]

@itsme  Thank you for your help.  I am getting below error message, just note that It's a different table than the one in your example, but i'm writing the formula the same way...

[itsme]

@jcastr02 I see you have the function spelled "number.to.text" - this is incorrect. It needs to be "Number.ToText". There is only 1 period and capitalize the first letter of each word. See if that works.

[jcastr02]

Thank you, that fixed error message but when I go to sort, I'm still getting same error message. see file link

@itsme 

https://1drv.ms/u/s!Ava_12t7CNJtkEcQHD-Ralk-Qgjf?e=47Zsvb 

[itsme]

I'm sorry, I'm not sure what I was thinking here...the dataset I was using is a bit different (I can't edit your data model in PQ because of access to the data sources).

Honestly, since there are multiple values in the response fields that are exactly the same and they have different completion times, I don't find a way this will be possible. For example, the 30 Day New Hire Survey table, even if you got rid of the blanks in the response column you still have multiple values that say "None" and each one of those have different completion times.

In order to sort, you would need to either change those completion times to be the same or make those responses uniquely different, e.g. concatenate a unique id at the end of the responses. Then you would be able to sort the respones by completion time. The only downside is that the end user of this report would see that id at the end of the response.

[jcastr02]

@itsme no worries, I appreciate your help