Get a table of distinct values of a column of another table

[Anonymous]

Hi,

I have a table like this:

ID	Product	Sub-product
1	A	AAAA1
2	B	BBBB1
3	A	AAAA1
4	D	DDDD1
5	B	BBBB2
6	A	AAAA3
7	A	AAAA2
8	B	BBBB1
9	D	DDDD3
10	A	AAAA1
11	C	CCCC1
12	A	AAAA5

What I want is a table that contains the distinct values in "Sub-product" column and also the corresponding value in the "Product" column.

The result is:

Distinct Product	Distinct Sub-product
A	AAAA1
B	BBBB1
D	DDDD1
B	BBBB2
A	AAAA3
A	AAAA2
D	DDDD3
C	CCCC1
A	AAAA5

So, all distinct values of the "Sub-product" column are only shown. I want to use this table as the main table to build relations and filter several tables.

How can I do this?

Thanks.

[edhans]

I recommend you do this in Power Query. This is what I get with two steps - Remove Other Columns, and Remove Duplicates.

M code is here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYSAwVIrViVYyAvKcQBgIICLGGGpMgDwXEAYCiIgpki4jsIgZki5jsIg5kghEjQWGXZZIJkN0GRpgWG8IcrUzCAMBVMgISZWpUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t, #"Sub-product" = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Product", "Sub-product"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

You could do it in DAX though:

THis is because ALL() returns distinct values unless you are using it on the entire table. Here it is just columns.

New Table = ALL('DataTable'[Product], 'DataTable'[Sub-product])

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[amitchandak · Accepted solution]

@Anonymous , Try like this in DAX

New table =

Summarize(Table,Table[Product], Table[Sub Product])

Also refer the pwoer query way : https://www.youtube.com/watch?v=kU2M1LmNvNo

https://www.youtube.com/watch?v=vHuhbvYCiNc