Dealing with duplicated values

[JaoGabriel]

I think I have a easy one for you guys.
We have a table with all suppliers, and in the past was not centralized who would setup them.

With that being said we have the following scenario. (it is way more complext than that, but solving this would help)

I want to show only the highlighted ones.....We may have suppliers duplicated with and without Country.
For those duplicated ones, I want to show only the row with country, if there is no duplicates we show what we have (with or without Country)
In this scenario, Pen Supplier must be shown because there is no row with country for it.

How do I do that?

Thank you!

[Anonymous · Accepted solution]

Hi jgeddes ,thanks for the quick reply, I'll add more.

Hi @JaoGabriel ,

The Table data is shown below:

The solution using dax is as follows:

Use the following DAX expression to create a table

Table 2 = 
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Supplier],
        "CountOfSupplier", COUNTROWS ( 'Table' )
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            _table,
            "Country",
                IF (
                    [CountOfSupplier] = 1,
                    VAR _supplier = [Supplier]
                    RETURN
                        MAXX ( FILTER ( 'Table', 'Table'[Supplier] = _supplier ), [Country] ),
                    CONCATENATEX (
                        FILTER ( 'Table', 'Table'[Country] <> "-" ),
                        [Country],
                        UNICHAR ( 10 )
                    )
                )
        ),
        [Supplier],
        [Country]
    )

Final output

Best Regards,
Wenbin Zhou

[jgeddes]

Here is an example in M that will do what you have asked.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsSC1SCC4tKMjJTC1S0lHSVYrVwSLsVJRYlZkDkUvNw6IhNS85MwdZPDTYUSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Supplier = _t, Country = _t]),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"Supplier", type text}, 
            {"Country", type text}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"Supplier"}, 
        {
            {"_innerTable", each Table.SelectColumns(_, "Country"), type table [Country=nullable text]}
        }
    ),
    Custom1 = 
    Table.TransformColumns(
        #"Grouped Rows", 
        {
            {"_innerTable", each if Table.RowCount(_) > 1 then Table.SelectRows(_, each [Country] <> "-") else _}
        }
    ),
    #"Expanded _innerTable" = 
    Table.ExpandTableColumn(
        Custom1, 
        "_innerTable", 
        {"Country"}, 
        {"Country"}
    )
in
    #"Expanded _innerTable"