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!
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.
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!