DISTINCT: A multi-value table was provided where a single value was expected.

[Syndicate_Admin]

I have a problem when trying to delete duplicate customers, the topic is the following, I have users but I need to have only one row per user and next to it the date of their LAST login, I did this but it gives me the following error:

" The expression refers to several columns. You can't convert multiple columns to a scalar value. "

Column = SUMMARIZE( Clientes_compradores_AWS, "date", Clientes_compradores_AWS[start date],"Customers", LASTNONBLANK(Clientes_compradores_AWS[cliente_id], 1))

I replaced the LASTNONBLANK function with DISTINCT but the error I get is the same, I can't understand the cause.

[vicky_ · Accepted solution]

Hey

This type of transformation is best done using PowerQuery - you can check this guide for more details: https://exceleratorbi.com.au/remove-duplicates-keep-last-record-power-query/

The reason that you get the error above is that SUMMARIZE will return a TABLE (which can contain more than one value per cell in the column). To fix this, you can either create a new TABLE instead of a new column, or you will need to aggregate the table to only return a value if the client is the latest (e.g. MAX([date], or something like that)