cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Abhaykumar
Microsoft
Microsoft

Getting distinct values for multiple columns

Is there a way to get distinct values for multiple columns? Specifically, I want to replicate the following SQL query into PowerBI to create a new table:

 

SELECT DISTINCT Col1, Col2, Col3 FROM TableA;

I can find the DISTINCT keyword, but it only supports one column. 

 

 

2 ACCEPTED SOLUTIONS
ankitpatira
Community Champion
Community Champion

@Abhaykumar You can also select individual columns in query editor and click Remove Duplicates.

 

Capture.PNG

View solution in original post

achinm45
Advocate IV
Advocate IV

Hi @Abhaykumar,

 

Use DAX and in DAX the function SUMMARIZE().

Summarize will pull out distinct values from columns.

 

So your resultset will be new table from Summarize

Table_Output =  Summarize(Table_IN,Col1,Col2,Col3)

 

I hope it helps !

 

BR,

Achin

View solution in original post

25 REPLIES 25
ImkeF
Super User II
Super User II

So you have to turn your multiple columns into one list first.

List.Distinct(List.Union({Source[Col1], Source[Col2], Source[Col3]}))

Where "Source" is the name of the table/previous step 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ilamp
Regular Visitor

Thank you. Exactly what I needed. 

Thanks Imke for your reply. However I am getting the following error :

2016-04-28 16_20_45-Error.jpg

The expression is for creating a new table in Data view.

The error says :

A single value for column 'BlobName' in table 'ProdBlobInfo-Final' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Sorry, should have mentioned that you need to add this column in the query editor, it's M-code.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I tried to create a custom column using Query Editor. The column gets created as a List but gives error when expanded.

 

2016-04-28 16_51_56-Error1.jpg2016-04-28 16_52_47-Error2.jpg2016-04-28 16_53_07-Error3.jpg

 

Let me know if i am doing something wrong.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.