Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Clément
Regular Visitor

Power BI formula

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

1 ACCEPTED 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.CityDate
1Marseille3/6/2014 0:00
2Nice1/31/2015 0:00
3Paris12/23/2016 0:00

View solution in original post

7 REPLIES 7
freder1ck
Kudo Kingpin
Kudo Kingpin

=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.CityDate
1Marseille3/6/2014 0:00
2Nice1/31/2015 0:00
3Paris12/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 !

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.