Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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
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
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.
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
@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
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 🙂
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
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.
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
Thank you. Exactly what I needed.
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.
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.
Let me know if i am doing something wrong.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
84 | |
73 | |
49 |
User | Count |
---|---|
142 | |
133 | |
110 | |
68 | |
55 |