Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would like to find a formula in Power BI that have as argument a table and that returns another table with unique values for a particular column.
Could you please help me?
Thanks in advance
Solved! Go to Solution.
Clement,
Let me know if this is the result you need:
'Table' is initial data. 'Results' is new table.
New Table:
Results = DISTINCT(Table[id.])
Add columns twice:
City = CALCULATE(
FIRSTNONBLANK('Table'[City], 1),
FILTER('Table', 'Table'[id.] = 'Results'[id.]))
Date = CALCULATE(
FIRSTNONBLANK('Table'[Date], 1),
FILTER('Table', 'Table'[id.] = 'Results'[id.]))
Fred
id. | City | Date |
1 | Marseille | 3/6/2014 0:00 |
2 | Nice | 1/31/2015 0:00 |
3 | Paris | 12/23/2016 0:00 |
=DISTINCT(Table[Column])
Hello Frederick,
This formula returns a one-column table and I would like to return all the columns of the first table without any duplicated values for a special column.
Can you help please?
Clément
Sure. Can you show an example of the data you would start with, and what the result would be?
Thanks,
Fred
If you want to keep all columns but remove duplicates for one column, you could remove duplicate rows in the query editor.
Can you see another solution ?
Clement,
Let me know if this is the result you need:
'Table' is initial data. 'Results' is new table.
New Table:
Results = DISTINCT(Table[id.])
Add columns twice:
City = CALCULATE(
FIRSTNONBLANK('Table'[City], 1),
FILTER('Table', 'Table'[id.] = 'Results'[id.]))
Date = CALCULATE(
FIRSTNONBLANK('Table'[Date], 1),
FILTER('Table', 'Table'[id.] = 'Results'[id.]))
Fred
id. | City | Date |
1 | Marseille | 3/6/2014 0:00 |
2 | Nice | 1/31/2015 0:00 |
3 | Paris | 12/23/2016 0:00 |
Thank you very much for this answer, it was very useful to me! I really appreciated your help! Thanks again!
I can't directly remove duplicates from Edit Queries because I can't find my table which has been calculated from a imported table.
Exemple :
My initial table :
id. City Date
1 Paris 01/01/2017
3 Paris 23/12/2016
1 Nantes 06/03/2014
1 Marseille 10/10/2017
2 Nice 26/10/2017
2 Paris 31/01/2015
And I would like to return this table (no duplicates in the column id.) :
id. City Date
1 Paris 01/01/2017
3 Paris 23/12/2016
2 Nice 26/10/2017
I don't care about which city is choosen for each id (ex : for id=1, I could have Paris or Nantes or Marseille as city).
Thanks in advance !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |