Merging cells and removing duplicate text

[bigk]

Hello, community!

I have a response log where respondents can update statuses with free text multiple times. For each new response the tool by default gives previous answer. Users can delete it completely and add new response or may select to continue to respond after the previous response. Sometimes for the same task, they can in one case leave old response and in another case delete it. So it is unpredictable. I would like to get the full response log per ID but remove duplicate text and also add the response date before each response.

Thanks for the help.

[bigk]

Thanks @dk_dk , you are genious! Works flawlesly! I've adjusted a bit to fit my needs but generally used your logic. One more thing left that is maybe not related to merging but still related to my case. Users are sometimes adding date into the response field. The format of date may differ. I would need to find a way to remove the dates from the text field. One of my idea to check if first 1 or 2 characters are numbers and if yes, then trim the text to first letter. There can of course be some cases when users start to reply with number but that won't be a date but i can live with that. I've added query to reflect my case.

T1

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DWw1DUyMDIBc/QMjPSAHGMFXYXQgpTEklQFw5g8pVgdqGIjwoohLCOEHmOi9cTkGRrpGZhD5CFixghzTCgyJyYPKGoMFIXwTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type date}, {"status", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, T2, {"Index.1"}, "T2", JoinKind.LeftOuter),
#"Expanded T2" = Table.ExpandTableColumn(#"Merged Queries", "T2", {"status"}, {"T2.status"}),
#"Sorted Rows" = Table.Sort(#"Expanded T2",{{"date", Order.Ascending}}),
#"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"T2.status", "Previous Status"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"id", "date", "status", "Previous Status", "Index", "Index.1"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Custom", each try if Text.Start([status],Text.Length([Previous Status])) = [Previous Status] then Text.End([status],Text.Length([status])-Text.Length([Previous Status])) else [status] otherwise [status]),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom","#(cr)","",Replacer.ReplaceText,{"Custom"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(lf)","",Replacer.ReplaceText,{"Custom"}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Replaced Value1", {{"date", type text}}, "lv-LV"),{"date", "Custom"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Update"),
#"Removed Columns1" = Table.RemoveColumns(#"Merged Columns",{"status", "Previous Status"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"id"}, {{"Count", each Text.Combine([Update],"#(lf)#(lf)"), type text}})
in
#"Grouped Rows"

T2

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DWw1DUyMDIBc/QMjPSAHGMFXYXQgpTEklQFw5g8pVgdqGIjwoohLCOEHmOi9cTkGRrpGZhD5CFixghzTCgyJyYPKGoMFIXwTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type date}, {"status", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type)
in
#"Added Index1"

[dufoq3]

Could you provide expected output of this source?

[bigk]

The output is below. I've also found that in some cases users do not provide response but change the dates only. So the response field is empty. For these cases i would need that a predefined text like "No changes to reposnse. Only deadline was changed." would be shown along with date. 

I've adjusted a bit source data to reflect below expected result:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pYw7DoAgEESvQrbmsyyIchAroDDRllh4/0iEBDpN7HbezrwQQAMH1AK9ICT7BIkkSzBMsPXct+tgOmZIvJXpvVwv6hvzeROzJolz/Vdmusf+8sRcqCm0Ztu90+Dt1A3UK6fINtMCKd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t])

Here is my expected output.

[dufoq3]

Here you go, but keep in mind that in case of status like "some text 13/07/2024" - this won't work. This will work only if the date is before other text.

If you want to have blank row between answers - change in last step GroupedRows "#(lf)" to "#(lf)#(lf)"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pYw7DoAgEESvQrbmsyyIchAroDDRllh4/0iEBDpN7HbezrwQQAMH1AK9ICT7BIkkSzBMsPXct+tgOmZIvJXpvVwv6hvzeROzJolz/Vdmusf+8sRcqCm0Ztu90+Dt1A3UK6fINtMCKd0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t]),
    Ad_CleanedStatus = Table.AddColumn(Source, "Cleaned Status", each 
        [ a = List.Last(List.Select(Text.SplitAny([status], "#(lf)#(cr)"), (x)=> Text.Trim(x) <> "")),
          b = try Text.PositionOfAny(a ?? "", {"a".."Z", "A".."Z"}) otherwise null,
          c = Text.Range(a, b) ?? "No changes to response. Only deadline was changed"
        ][c], type text),
    Ad_Merged = Table.AddColumn(Ad_CleanedStatus, "Merged", each Text.Combine({ if [date] is text then Text.Replace([date], "-", ".") else Date.ToText([date], "dd.MM.yyyy") , [Cleaned Status]}, " - "), type text),
    GroupedRows = Table.Group(Ad_Merged, {"id"}, {{"All", each Text.Combine([Merged], "#(lf)") , type text}})
in 
    GroupedRows

[Omid_Motamedise]

just use group by command

[dufoq3]

Hi @bigk, different approach:

You have to have these column names (case sensitive!):

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DWw1DUyMDIBcvzySxTys5VidaByRhhyMXkxee6pJSWZeekKSUA6tQih2pgI1SARx5zc/OIShYLUorTU5BKEfhMk/QEwyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Status = _t]),
    Ad_Response = Table.AddColumn(Source, "Response", each 
        [ a = List.Last(List.Select(Text.SplitAny([Status], "#(lf)#(cr)"), (x)=> Text.Trim(x) <> "")),
          b = Text.Combine({ if [Date] is text then [Date] else Date.ToText([date], "dd-MM-yyyy"), a}, " - ")
        ][b], type text),
    GroupedRows = Table.Group(Ad_Response, {"ID"}, {{"Response", each Text.Combine([Response], "#(lf)"), type text }})
in
    GroupedRows

[bigk]

Hi, @dk_dk 

Thanks for quick response. My example of the table is very simple. My bad 😞

The real life responses are way bigger and they can have more than one line divided by the line/paragraph in one  response or have several lines between. So for example the "Getting better" response could be like:

Getting better.

Next target is to make it perfect.

So the your solution, although working weel for simple case, not working for cases when the text will be in several rows.

My initial thought was to compare if the next row cell is in the contents of the previous cell and if so, ignore it (or remove it from previous cell contents). Then there can be less problems with the structure of each response and delimiters used.

[dk_dk · Accepted solution]

Ah I see.
You could test if you can get somewhere with text comparison. IT might not be impossible but with free text input it is always difficult to cater for all possible scenarios.

I tried the following:

Add two new columns to your data: 0 index and 1 index

Duplicate your query.

Merge the two queries with the 0-1 index columns, expand the status column from the merge. This should be the previous row's status, and null for the very first row.

After some column renaming, try the following custom column:

try if Text.Start([status],Text.Length([Previous Status])) = [Previous Status] then Text.End([status],Text.Length([status])-Text.Length([Previous Status])) else [status] otherwise [status]

Here are the full queries:

T1:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DWw1DUyMDIBcvzySxTys5VidaByRhhyMXkxee6pJSWZeekKSUA6tQih2pgI1SARx5zc/OIShYLUorTU5BKEfhMk/QFIkkYgSVMszowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type date}, {"status", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, T2, {"Index.1"}, "T2", JoinKind.LeftOuter),
    #"Expanded T2" = Table.ExpandTableColumn(#"Merged Queries", "T2", {"status"}, {"T2.status"}),
    #"Sorted Rows" = Table.Sort(#"Expanded T2",{{"date", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"T2.status", "Previous Status"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"id", "date", "status", "Previous Status", "Index", "Index.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Index", "Index.1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each try if Text.Start([status],Text.Length([Previous Status])) = [Previous Status] then Text.End([status],Text.Length([status])-Text.Length([Previous Status])) else [status] otherwise [status]),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom","#(cr)","",Replacer.ReplaceText,{"Custom"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(lf)","",Replacer.ReplaceText,{"Custom"})
in
    #"Replaced Value1"

T2 (duplicate of the first part of T1) before the merge

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DWw1DUyMDIBcvzySxTys5VidaByRhhyMXkxee6pJSWZeekKSUA6tQih2pgI1SARx5zc/OIShYLUorTU5BKEfhMk/QFIkkYgSVMszowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type date}, {"status", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type)
in
    #"Added Index1"

I added a couple replace values to the end, to clean up the whitespace around the statuses.

Hope this works for your use case!

[dk_dk]

Hi @bigk 

Assuming that when the users add on to the existing response the new response will always be on a new line (as your example), you can do the following:

1. Duplicate your status column
2. split column by delimiter
3. 

   Select Custom, rightmost occurence, and as per the screenshot, select Line Feed (or Carriage Return, or another option depending on your data). This will populate the delimiter field automatically. Click ok.

4. Add a new custom column: if [#"status - Copy.2"] is null then [status] else [#"status - Copy.2"]
5. The final column will have the latest row of data from the original status column, or the only row if there is only one row.

See below:

Here is my entire M query (I entered the data myself, but you should be able to replace the source and adjust the column references:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1DWw1DUyMDIBcvzySxTys5VidaByRhhyMXkxee6pJSWZeekKSUA6tQih2pgI1SARx5zc/OIShYLUorTU5BKEfhMk/QEwyVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, date = _t, status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"date", type date}, {"status", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "status", "status - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "status - Copy", Splitter.SplitTextByEachDelimiter({"#(lf)"}, QuoteStyle.Csv, true), {"status - Copy.1", "status - Copy.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "fixed status", each if [#"status - Copy.2"] is null then [status] else [#"status - Copy.2"])
in
    #"Added Custom"

[dk_dk]

If you want the result to be the date and corresponding status in one single column, you can simply concatenate the date column with the above result using Merge Columns in power query.