Complex duplication removal process

[kzielinska]

Hi,

I want to remove the row if name is duplicated and the surname is blank only if for this name there also is some data in surname column.

input:

desired output:

How to do that?

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMlSK1YGxoUxTrEyQAiMo2wTCjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, surname = _t]),
    #"Grouped Rows" = Table.Group(Source, {"name"}, {{"surname", each let l=List.Distinct(_[surname]) in if List.Count(l)=1 then l else List.RemoveItems(l,{""}), type list}}),
    #"Expanded Rows" = Table.ExpandListColumn(#"Grouped Rows", "surname")
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", Int64.Type}, {"surname", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each _, type table [name=nullable number, surname=nullable number]}, {"Count1", each List.NonNullCount(_[surname]), Int64.Type}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"surname"}, {"surname"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Count1]<>0 and [surname]=null then "Remove" else "Keep"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count1", "Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.

[Anonymous · Accepted solution]

Hi @kzielinska 

Thanks for the reply from lbendlin and Ashish_Mathur , please allow me to provide another insight:

Method 1:

1. Create a measure as follows:

KeepRow = 
VAR _NonBlankSurnamesExist = 
CALCULATE(
    COUNTROWS(
        FILTER(
            'Table',
            NOT ISBLANK('Table'[surname])
        )
    ) + 0,
    ALLEXCEPT('Table', 'Table'[name])
)
RETURN
IF(
    _NonBlankSurnamesExist > 0 && MAX([surname]) <> BLANK() || _NonBlankSurnamesExist = 0,
    1,
    0
)

2. Put the measure into the visual-level filters, set up show items when the value is 1.

Method 2:

Create a calculated table as follows:

Table 2 = 
VAR _NonBlankSurnamesExist = 
CALCULATE(
    COUNTROWS(
        FILTER(
            'Table',
            NOT ISBLANK('Table'[surname])
        )
    ) + 0,
    ALLEXCEPT('Table', 'Table'[name])
)
VAR _KeepRow =
IF(
    _NonBlankSurnamesExist > 0 &&  MAX('Table'[surname]) <> BLANK() || _NonBlankSurnamesExist = 0,
    1,
    0
)
RETURN
SUMMARIZE(FILTER('Table', [KeepRow] = 1), 'Table'[name], 'Table'[surname])

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", Int64.Type}, {"surname", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"name"}, {{"Count", each _, type table [name=nullable number, surname=nullable number]}, {"Count1", each List.NonNullCount(_[surname]), Int64.Type}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"surname"}, {"surname"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Count", "Custom", each if [Count1]<>0 and [surname]=null then "Remove" else "Keep"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "Keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count1", "Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMlSK1YGxoUxTrEyQAiMo2wTCjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [name = _t, surname = _t]),
    #"Grouped Rows" = Table.Group(Source, {"name"}, {{"surname", each let l=List.Distinct(_[surname]) in if List.Count(l)=1 then l else List.RemoveItems(l,{""}), type list}}),
    #"Expanded Rows" = Table.ExpandListColumn(#"Grouped Rows", "surname")
in
    #"Expanded Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.