Forum Discussion
Merging cells and removing duplicate text
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!
Could you provide expected output of this source?
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.
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