cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Remove Duplicates

Hi,

 

I want to remove duplicates in Power BI Data tab. I have created a new column and I want to remove duplicates. How I can do that.

 

We can do it using tranform table but I created a new column in data tab so that column is not appearing in transform tab.

 

Also, Can i create concatenate in transform table? I want to add text and num value. Its giving an error we cannot combine text and value in tarnsform concatenate.

 

Thnaks

C

17 REPLIES 17
Highlighted

Hi,

It is best to write a measure (DAX formula) to solve this question rather than write a M language code.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Yea that;'s the issue. I have get the average but I cannot remove duplicates in data tab.

 

I can remove duplicates in tranform tabl but I cannot get the average it before removing duplicates.

 

I sthere any way that I can do this?

Highlighted

Hi,

I cnanot understand your question.  Share your data, explain the business context and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

This is the table,

I want to get the average of lat for 7000 TAS. There are 6 lat records for 7000 TAS. So I want to get the average for that. Likewise I want to get the averageif for all the data.

suburbpostcodestateSuburb and PostcodelatlonPostCode AND State
GLEBE7000TASGLEBE7000-42.874031147.3263547000 TAS
HOBART7000TASHOBART7000-42.882743147.3302347000 TAS
MOUNT STUART7000TASMOUNT STUART7000-42.873119147.3022977000 TAS
NORTH HOBART7000TASNORTH HOBART7000-42.872319147.3145797000 TAS
QUEENS DOMAIN7000TASQUEENS DOMAIN7000-42.865684147.328727000 TAS
WEST HOBART7000TASWEST HOBART7000-42.883713147.3149077000 TAS
HOBART7001TASHOBART7001-42.837499147.5061627001 TAS
TASMAN ISLAND7001TASTASMAN ISLAND7001-43.238773148.0029587001 TAS
NORTH HOBART7002TASNORTH HOBART7002-42.899691147.4463497002 TAS
BATTERY POINT7004TASBATTERY POINT7004-42.892767147.3332427004 TAS
SOUTH HOBART7004TASSOUTH HOBART7004-42.892586147.3161097004 TAS
DYNNYRNE7005TASDYNNYRNE7005-42.901018147.3141317005 TAS
LOWER SANDY BAY7005TASLOWER SANDY BAY7005-42.919553147.3544397005 TAS
Highlighted

Hi,

Click on the Table visual and drag the 2 fields Suburb and Post code to the visual.  Write this measure

=average(Data[Lat])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

I want to get the avarage of lat and vlookup it to ther other sheet. Its not about showing the avaerage in a table.

 

So without doing thses in Power BI its better to do ot in Excel right? But if there is anyway to remove duplicates in Data tab I can get the correct results.

Highlighted

Hi,

You will need to share that other table as well.  Show the exact result that you are expecting to see in the other table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Thanks. I found a way.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors