Forum Discussion

bigk's avatar
bigk
Helper III
1 year ago
Solved

Merging cells and removing duplicate text

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 c...
Need Help
  • dk_dk's avatar
    dk_dk
    1 year ago

    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's avatar
dk_dk
Super User
1 year ago

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's avatar
    dk_dk
    Super User
    1 year ago

    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.