Getting distinct values for multiple columns

[Abhaykumar]

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. 

[ankitpatira · Accepted solution]

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

[ToFrai]

Hi Imke,

thanks for the hint - where do I enter the code in the querie editor? Do I have to create a new table first, or add a new step in an existing query?

Problem is that the querie editor shows source tables only, not the ones I caclulated.

Isn't there a Dax formula that I can use in a new table that says something like "copy all (distinct) values from Table A Column 1, and then below all (distinct) valies from Table 2 Column 1, ...."?

BR | Tobias

[ImkeF]

You CAN do it in your current query:

Add step manually by clicking the fx-sign:

 

But of course, this only works for tables that exist in the query editor.

If you've created them in the data view with DAX, then you won't have access to them there.

You might consider opening a new thread and specifically request a DAX-solution to enhance visibility of your request.

[ankitpatira · Accepted solution]

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

[Anonymous]

@ankitpatira if he reomves the duplicates will it not remove the entire row ? which will ultimately result in wrong data ?

i am new to powerBI

[ImkeF]

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 🙂

[Greg_Deckler]

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.

[ImkeF]

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 

[ilamp]

Thank you. Exactly what I needed. 

[Abhaykumar]

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

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.

[ImkeF]

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

[Abhaykumar]

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

Let me know if i am doing something wrong.