Show Most recent comments

[jcastr02]

I have a list of comments that I would like to display from most recent to oldest.  In my query I have a completion time.  However, some of the comment field at times will be blank and may have repeating comments/data.  I get below error message, any help is appreciated.     

[smpa01]

@jcastr02 

I assumed a dataset

Column1	Column2
same value	1/1/2020 10:20:00 AM
same value	1/1/2020 10:45:00 AM
same value	1/1/2020 11:00:00 AM
different value	1/1/2020 10:00:00 AM

you can do this and display only the 1s from Index column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk7MTVUoS8wpTVXSUTLUN9Q3MjAyUDA0sDIyUIrVwSNvYopP3tDKAKIfKAqmUzLT0lKLUvNKsBoGUhwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"ad", each _, type table [Column1=text, Column2=datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
X = [ad],
Y = Table.Sort(X,{{"Column2", Order.Descending}}),
Z = Table.AddIndexColumn(Y, "Index", 1, 1)
in Z),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"})
in
    #"Expanded Custom"

if you prefer DAX, a rank column might work

Column = RANKX(FILTER('Table (3)',EARLIER('Table (3)'[Column1])='Table (3)'[Column1]),'Table (3)'[Column2],,ASC)

[MFelix]

Hi @jcastr02 ,

On the query editor sort the table by Completion time then add an index column, then sort the comments by the index should work as intended.

[jcastr02]

@MFelix  I tried that earlier too, but I still get same error 😞

[v-gizhi-msft]

Hi,

According to your description, you can not sort the [Value] by [Completion Time] due to existing duplicate values.

You can add an index column, and sort [Index] by [Completion Time], when you choose [Value] in visual, try to add this [index] to the visual as well and [Value] will sort by [Completion Time] because [Index] uniquely corresponds to [Value] in table.

Best Regards,

Giotto Zhi

[jcastr02]

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

I added it to my visual, but still keeps it in alphabetical order.  I've attached my BI file as well.  Please see 'duplicate of 60 day tab'

trying to work on visual with green background.  

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

@v-gizhi-msft @smpa01   Tried both of your solutions but can't seem to have it sort by completion time.  

[MFelix]

Hi @jcastr02 , 

Best option is what you have done in your visualization so placing the comment and index on your table then turn off wrap text on title and sort the table by index then just hide the index column by reducing it's size.

A workaround but it works.

[jcastr02]

@MFelix Yes ! this seems to work!   I appreciate the help.  Only thing I noticed that repeating items such as N/A, pay, the hours, etc. are pushed to the top so everything else sorts except the repeating items, any work around you are aware of?

[MFelix]

Hi @jcastr02 ,

Make the summarization of the index column the miminum value and then sort it out. what is happening is that the default behavior of ID is to sum values since you have more than one values for certain comments they get to different positions based on its sum.

If you select minimum you will get the first value of the index.

for example NA in your table is giving the 299 (sum of several index) making it to minimum it gives 17 if you go to your table the first record with NA is line 18 at 17/01/2020 so the first one as you want.

[jcastr02]

@MFelix You are awesome that worked! One last ?   How can I hide the sorting arrow.  From end user perspective if they click on the visual, it will sort by Value again

[MFelix · Accepted solution]

Hi @jcastr02 ,

That is not possible on the table visual as a workaround you can:

• Create a button with no backgroud and no line
• Make the button the size of the column header and place it on top of the header
• Group the table with the header

That way you will still see the header of the table but if you click on it won't be reorder.