Forum Discussion

bamba98's avatar
bamba98
Icon for Helper I rankHelper I
6 years ago
Solved

Remove duplicates without changing values in calculated column

I have a bit of a tricky situation and I am not able to figure out how to solve it. So I have a huge data set (million rows) and many duplicate IDs. The duplicate IDs are within the table because it ...
Need Help
  • bamba98's avatar
    bamba98
    6 years ago

    I managed to solve this by uploading again the same data set under a different name. I've removed the duplicates in the second data set and used FIRSTNONBLANK function to add the calculated Tot column of the first data set to the second data set. A bit of an unconventional approach I would say, but it does the trick!

     

    This idea was inspired by az38. Thank you!

bamba98's avatar
bamba98
Icon for Helper I rankHelper I
6 years ago

az38 Because I want to keep the latest period an ID was active and after removing the duplicates I don't care much about the column containing Val.

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

bamba98 

try to create new calculated table like

Table 2 = 
ADDCOLUMNS(
SUMMARIZE('Table', 'Table'[ID], "Period", MAX('Table'[Period])),
"Val", CALCULATE(SUM('Table'[Val]), FILTER('Table', 'Table'[ID] = EARLIER([ID]) && 'Table'[Period] = EARLIER([Period]))),
"Tot", CALCULATE(SUM('Table'[Tot]), FILTER('Table', 'Table'[ID] = EARLIER([ID]) && 'Table'[Period] = EARLIER([Period])))

)