Delete duplicates in directquery

[Anonymous]

Hey Community, 

So i'm using directquery mode And I need to delete rows that are (almost) identical, keeping the last row. Example below:

Source:

ID, Type , Type2,

1, A, a

1, A, b

1, B, a

Result:

1, A, b

1, B, a

So I delete One of the first two rows because ID And Type Are equal.

Any ideas How to get this Done? 

[v-xiaoyan-msft · Accepted solution]

Hi,@Anonymous 

You can use a measure as visual level filter to meet your needs:

Create a measure like this after you got the sample:

Measure = RANKX(ALLEXCEPT('Table','Table'[ID],'Table'[Type]),CALCULATE(MAX('Table'[Type2])),,DESC)

Then you can put the measure in your visual to filter what you want.

Hope it helps.

Best Regards,

Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can delete data in Direct Query. In Visual Take max of Type2 and other two as not summarized

measure = Max(Table[Type2])