cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft Abhaykumar
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

Accepted Solutions
ankitpatira Super Contributor
Super Contributor

Re: Getting distinct values for multiple columns

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

 

Capture.PNG

View solution in original post

achinm45 Member
Member

Re: Getting distinct values for multiple columns

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

20 REPLIES 20
Super User III
Super User III

Re: Getting distinct values for multiple columns

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 

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

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Microsoft Abhaykumar
Microsoft

Re: Getting distinct values for multiple columns

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.

Super User III
Super User III

Re: Getting distinct values for multiple columns

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

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

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Microsoft Abhaykumar
Microsoft

Re: Getting distinct values for multiple columns

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.

Super User IV
Super User IV

Re: Getting distinct values for multiple columns

In DAX, you could use:

 

Column = CONCATENATE(CONCATENATE([Col1],[Col2]),[Col3])

Then you could use DISTINCTCOUNT and such on that Column. Or, just put [Column] in a table/matrix and it will only show the distinct values.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ankitpatira Super Contributor
Super Contributor

Re: Getting distinct values for multiple columns

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

 

Capture.PNG

View solution in original post

Super User III
Super User III

Re: Getting distinct values for multiple columns

My anser was rubbish (it would create a list of distinct values of the selected columns and returns it as a list if applied as a standalone-command (and not within a Table.AddColumn-command where will be performed as a function acting on record-level)).

 

Do as @ankitpatira said in the query-editor: First select the columns with your mouse and then click as shown. No need to edit a formula then 🙂

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

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




achinm45 Member
Member

Re: Getting distinct values for multiple columns

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

Super User III
Super User III

Re: Getting distinct values for multiple columns

If you do it in DAX, just be aware that the full table must be loaded into the data model. Doing it in the query-editor using M would (mostly) fold back to the server, meaning that the SQL-server does the extraction and returns only the results to PBI to be loaded.

 

So if you need to load the full table anyway, DAX would probably be faster.

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

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors