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

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?

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

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

  • 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.