Forum Discussion

cham's avatar
cham
Post Patron
6 years ago
Solved

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 col...
Data Protection
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    This M code should let you comobine text with number

    =Text_column&Number.ToText(Numeric_column)

    Replace Text_column and Numeric_column with your actua table and column names.

cham's avatar
cham
Post Patron
6 years ago

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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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.

  • cham's avatar
    cham
    Post Patron
    6 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    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.

  • cham's avatar
    cham
    Post Patron
    6 years ago

    Thanks. I found a way.