topic Dealing with duplicated values in DAX Commands and Tips

Dealing with duplicated values

Anonymous — Fri, 16 Aug 2024 15:20:30 GMT

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!

Re: Dealing with duplicated values

jgeddes — Fri, 16 Aug 2024 15:51:35 GMT

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"

Re: Dealing with duplicated values

Anonymous — Mon, 19 Aug 2024 01:46:36 GMT

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