Filtering out data based on column entry based on TEXT

[Anonymous]

Hello,

I am looking for the best way to filter out data based on a text entry.  I will provide an example that should help explain what I'm trying to get at.

Account          ID          Value          Effective_Date         Transaction_Date

1234               DataA      56              1/1/2018                 5/5/2018

1234               DataB      47              1/1/2018                 6/18/2018

4567               DataB      48              1/1/2018                 7/5/2018

8910               DataA      5                1/1/2018                 12/25/2017

8910               DataB       10              1/1/2018                 6/5/2018

I am looking for this output for my dataset

Account          ID          Value          Effective_Date         Transaction_Date

1234               DataA      56              1/1/2018                 5/5/2018

4567               DataB      48              1/1/2018                 7/5/2018

8910               DataA      5                1/1/2018                 12/25/2017

Basically if there is a DataA value, use that over DataB's value, but if not, then use DataB's for the given account.

Thanks for any potential insight.

[edhans · Accepted solution]

Ok, this is super quick and dirty.

See this file.

It seems that the Account # and ID are always the same when there is duplication with DataA and DataB, so I grouped on Account#, then took the FIRST value in ID., then merged it with itself.

See it in the Power Query window.