Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

Hi,

 

I have a table like this:

 

IDProductSub-product
1AAAAA1
2BBBBB1
3AAAAA1
4DDDDD1
5BBBBB2
6AAAAA3
7AAAAA2
8BBBBB1
9DDDDD3
10AAAAA1
11CCCCC1
12AAAAA5

 

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 ProductDistinct Sub-product
AAAAA1
BBBBB1
DDDDD1
BBBBB2
AAAAA3
AAAAA2
DDDDD3
CCCCC1
AAAAA5

 

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.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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

edhans_0-1617160135338.png

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:

edhans_1-1617160216601.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.