Forum Discussion
Microsoft EmployeeGetting 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.
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
31 Replies
achinm45Advocate 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
I am trying to create a table that shows the person's unique code, department and name. I am using the Summarize function and am getting duplicates. How do I exclude the duplicate that does not get associated with a department?
Table = Summarize('MO YTD Jan - June Revenue','MO YTD Jan - June Revenue'[Employee Code],'MO YTD Jan - June Revenue'[Department Name])Thank you!Kim- ImkeF
Community Champion
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.
You can also wrap a DISTINCT(SELECTCOLUMNS(table, "col1name", Col1fromtable, table, "col2name", Col2fromtable)
Despite the existence of many solutions in Power BI (Query Editor, DAX, etc), this seams to be the most elegante solution.
Thants for your hint achinm45
- ankitpatira
- 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 :-)
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
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
AbhaykumarThanks 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.
Thank you. Exactly what I needed.
- 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.
Hi ImkeF
I followed your approach in this thread to create my first manual query in Power BI. I wanted to create this list of distinct values from two different columns from two tables, so that I could use the newly created column as the relational data point between those two tables/columns. However, I'm getting the ambiguity error when trying to relate the second column to it. Is that not the purpose of creating this union list? I also tried creating a table that did the same thing, and the ambiguity error popped up there as well.
Is there a way to create a stand alone column that is the list of unique values from two columns for the purpose of relating those two columns in one-to-many relationships?
- ImkeF
Hi Anonymous
sorry, but I cannot follow.
Could you possibly post some sample data?
= List.Distinct(List.Union({#"Obligations by FY"[Project Number],#"CJI3 Actual Cost Data"[Project Number]}))
So, that's the query I created. In my model, I then try to create a relationship between this new column, and the two different "Project Number" columns, so that the new "Project Number" column can be used for filtering purposes in visuals.
I can create one relationship, but upon creating the second relationship, I get the error about ambiguous paths. So, I can't do what I wanted with the new query/column.
You can use the Table.Group function without any aggregation.
= Table.Group(Source, {"Col1", "Col2", "Col3"}, {})
