Remove Duplicated strings from a cell in Power Query for Power BI

Question

I'm trying to remove duplicates from two cells, and it seems a nowhere close a solution. So, I would like to ask for your assistance.

I have a table with a few tickets (TKS00XX) and their corresponding causing records (CSR00XX), and additionally to that I also have the investigation records (INV00XX) linked to the tickets. What happens is that in the investigation records we also have the causing records, which sometimes is the causing records linked back to the tickets.

In the example above, it is how is the table at source. In Power BI, I can group the records by the ticket and Investigation Records where I'd get the following

I'd like to merge these two causing records columns and remove the duplicated items (for example, the item highlighted in red (CSR0032). And this is the result I'm trying to get.

Do you know how I can achieve this result in the Power Query Editor for Power BI?

I have tried unpivoting the Causing Record tables and then grouping then by the ticket number ad investigation record numbers, but it removed some entries that shouldn't be removed.

Answer 1

1. Duplicate your table
2. From the 1st table remove the 1st CAUSING RECORD
3. From the 2nd table remove the 2nd CAUSING RECORD
4. Append both tables as new

5. Remove duplicate rows
6. Group by TICKET and INVESTIGATION RECORD and aggregate CAUSING RECORD AS SUM of CAUSING RECORD.

7. Since the last row will lead to an error, edit the formula in the formula bar and change List.Sum([CAUSING RECORD]) to Text.Combine([CAUSING RECORD], ", ")

The M-Code for the 3 involved tables is

Table1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvEONjAwNFLSUXIODjIwMAKxPP3CgCyYkLGRUqwOhkJjLAqNsSk0NMRQaGKCUGhqCtduClMIFzKzxKYQqB1doamFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TICKET = _t, #"CAUSING RECORD" = _t, #"INVESTIGATION RECORD" = _t, #"CAUSING RECORD.1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET", type text}, {"CAUSING RECORD", type text}, {"INVESTIGATION RECORD", type text}, {"CAUSING RECORD.1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"CAUSING RECORD.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"TICKET", "INVESTIGATION RECORD", "CAUSING RECORD"})
in
    #"Reordered Columns"

Table2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvEONjAwNFLSUXIODjIwMAKxPP3CgCyYkLGRUqwOhkJjLAqNMRX6+3t6Yig0MUEoNDWFazeFKYQLmVliUwjUjq7Q1EIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TICKET = _t, #"CAUSING RECORD" = _t, #"INVESTIGATION RECORD" = _t, #"CAUSING RECORD.1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET", type text}, {"CAUSING RECORD", type text}, {"INVESTIGATION RECORD", type text}, {"CAUSING RECORD.1", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"CAUSING RECORD"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"CAUSING RECORD.1", "CAUSING RECORD"}})
in
    #"Renamed Columns"

Table3

let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Sorted Rows" = Table.Sort(
        #"Removed Duplicates",{{"TICKET", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(
        #"Sorted Rows", 
        {"TICKET", "INVESTIGATION RECORD"}, 
        {
            {"CAUSING RECORD", each Text.Combine([CAUSING RECORD], ", "), type nullable text}
        }
    )
in
    #"Grouped Rows"

Answer 2

Once you join your two tables, you can Group by TICKET and INVESTIGATION RECORD.

Within the Table.Group function add a custom aggregation of the CAUSING RECORD columns, remove the duplicates, optionally sort, and combine them as a text string.

In the code below, it is the Table.Group function that is important; the rest is just setup and housekeeping.

Tickets

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvEONjAwNFLSUXIODjIwMDJSitXBEDXGKmpoiBA1NYWrNcUmamKiFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TICKET = _t, #"CAUSING RECORD" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TICKET", type text}, {"CAUSING RECORD", type text}})
in
    #"Changed Type"

Investigation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vQLMzAwUtJRcg4OMjAwNlKK1cEQNMYiaGKCEDSFCZpZYhE0tVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"INVESTIGATION RECORD" = _t, #"CAUSING RECORD" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INVESTIGATION RECORD", type text}, {"CAUSING RECORD", type text}})
in
    #"Changed Type"

Combined

let

//Combine the two tables
    #"Append Columns" = Table.FromColumns(
        Table.ToColumns(Ticket) & Table.ToColumns(Investigation),
        type table[TICKET=text, CAUSING RECORD=text, INVESTIGATION RECORD=text, CAUSING RECORD2=text]),

//Group by TICKET, INVESTIGATION RECORD
    #"Grouped Rows" = Table.Group(#"Append Columns", {"TICKET", "INVESTIGATION RECORD"}, {
        {"CAUSING RECORDS", each 
            Text.Combine(
                List.Sort(
                    List.Distinct(
                        List.Combine({[CAUSING RECORD],[CAUSING RECORD2]}))),
            ", "), type text}})
in
    #"Grouped Rows"