Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dealing with duplicated values

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's avatar
    Anonymous
    2 years ago

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

    Hi Anonymous ,

    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

     

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    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"

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

    Hi Anonymous ,

    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