i want to see the latest comments in powerbi table

[Yeswanth]

I want to showcase the recent comments in table. initially in every ticket we have multiple updates when customer posted their updates in each row, we have the multiple comments and multiple timestamp's specific to the ID .

I want to showcase the recent comments in table.

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

[Yeswanth]

Hi @Ashish_Mathur please find the below table wihich shows actual vs expected result. please provide the desired dax ? or any suggestions thanks
Actual Data

ID	Comments
2287	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend 2024-01-09 13:51:35 - Rakesh (Additional comments) Many sources of pollution were unregulated parts of industrialization during the 19th and 20th centuries until the emergence of environmental regulation and pollution policy in the later half of the 20th century. 2024-01-03 10:20:26 - Rakesh (Additional comments) Pollution has widespread consequences on human and environmental health, having systematic impact on social and economic systems. 2023-12-19 14:11:44 - Rakesh (Additional comments) Although environmental pollution can be caused by natural events, the word pollution generally implies that the contamin 2023-12-12 12:35:37 - kumar (Additional comments) Pollution is the introduction of contaminants into the natural environment that cause adverse change.[1]
2288	2024-01-13 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend 2024-01-09 13:51:35 - Rakesh (Additional comments) Many sources of pollution were unregulated parts of industrialization during the 19th and 20th centuries until the emergence of environmental regulation and pollution policy in the later half of the 20th century. 2024-01-03 10:20:26 - Rakesh (Additional comments) Pollution has widespread consequences on human and environmental health, having systematic impact on social and economic systems. 2023-12-19 14:11:44 - Rakesh (Additional comments) Although environmental pollution can be caused by natural events, the word pollution generally implies that the contamin 2023-12-12 12:35:37 - kumar (Additional comments) Pollution is the introduction of contaminants into the natural environment that cause adverse change.[1]
2230	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend 2024-01-09 13:51:35 - Rakesh (Additional comments) Many sources of pollution were unregulated parts of industrialization during the 19th and 20th centuries until the emergence of environmental regulation and pollution policy in the later half of the 20th century. 2024-01-03 10:20:26 - Rakesh (Additional comments) Pollution has widespread consequences on human and environmental health, having systematic impact on social and economic systems. 2023-12-19 14:11:44 - Rakesh (Additional comments) Although environmental pollution can be caused by natural events, the word pollution generally implies that the contamin 2023-12-12 12:35:37 - kumar (Additional comments) Pollution is the introduction of contaminants into the natural environment that cause adverse change.[1]

Expected output:
-------------

ID	Comments
2287	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend
2288	2024-01-13 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend
2230	2024-01-16 13:17:59 - Kumar (Additional comments) such as children and the elderly, and marginalized communities, because polluting industries and toxic waste sites tend

[Ashish_Mathur]

Hi,

I worked with this sample data

and got this result

with this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Comment", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comment"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Comment", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Comment] <> "")),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Filtered Rows", "Datetime", each Text.BeforeDelimiter([Comment], "-", {0, RelativePosition.FromEnd}), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Before Delimiter","",null,Replacer.ReplaceValue,{"Datetime"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Datetime"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Datetime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each Table.Max(_,"Datetime")}}),
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"Comment", "Datetime"}, {"Comment", "Datetime"})
in
    #"Expanded Count"

Hope this helps.

[Ashish_Mathur]

When i paste that data in MS Excel, i get each comment in a seperate cell but i think those comments (for a specific ID) are in the same cell (seperated with an Alt + Enter).  Please share the download link of the Excel file instead.

[Yeswanth]

Hi @Ashish_Mathur i want the only latest comment in comments column specific to the ID instead of past comments

[Yeswanth]

hi @v-xuxinyi-msft please check the below comments

[v-xuxinyi-msft · Accepted solution]

Hi @Yeswanth 

Thanks for the reply from @Ashish_Mathur , please allow me to provide another insight:

I used the latest data you gave, which according to my tests requires you to have a corresponding ID for each comment, otherwise the following will happen.

no corresponding ID for each row:

1. Create a calculated column as follows

Date = LEFT([Comments], 19)

2. Create a measure as follows

Measure = 
VAR _max = CALCULATE(MAX([Date]), ALLEXCEPT('Table', 'Table'[ID]))
RETURN
IF(MAX([Date]) = _max, 1, 0)

3. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yeswanth]

Hi , Thanks for your support, but please dont divide the date and customer name both are in the same field specific to the id , customer name was not constant it will varies as per the daily updates only 2 columns 
ID : 1
comments: 24/09 hi rakesh
                   19/03 welcome rakesh
                    01/01 bye rakesh 

i want to see the lastest commnet as per the date in commnets column

[Yeswanth]

Hi thanks for your time but this is not the solution I am looking for 

1 ID = MULTIPLE COMMENTS in the same field there is no date in the table in the screenshot having date as a text along with the multiple updates daily so I want to see the latest comments 

[v-xuxinyi-msft]

Hi @Yeswanth 

Here is an example of what I did:

I restored some of your data.

1. Create a measure as follows

Measure = 
VAR _max = CALCULATE(MAX([Date]), ALLEXCEPT('Table', 'Table'[Customer]))
RETURN
IF(MAX([Date]) = _max, 1, 0)

2. Put the measure into the filter so that the visual only shows data where the measure is equal to 1.

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.