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 w...
  • 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

     

jgeddes's avatar
jgeddes
Icon for Super User rankSuper 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"