Filtering out data based on column entry based on TEXT

Question

Hello,&nbsp;I am looking for the best way to filter out data based on a text entry.&nbsp; I will provide an example that should help explain what I'm trying to get at.&nbsp;Account&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Effective_Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Transaction_Date1234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataA&nbsp; &nbsp; &nbsp; 56&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/5/20181234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataB&nbsp; &nbsp; &nbsp; 47&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/18/20184567&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataB&nbsp; &nbsp; &nbsp; 48&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/5/20188910&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataA&nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12/25/20178910&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataB&nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/5/2018&nbsp;I am looking for this output for my dataset&nbsp;Account&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Effective_Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Transaction_Date1234&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataA&nbsp; &nbsp; &nbsp; 56&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5/5/20184567&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataB&nbsp; &nbsp; &nbsp; 48&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/5/20188910&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DataA&nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/1/2018&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12/25/2017&nbsp;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 Answer

Ok, this is super quick and dirty.&nbsp;See this file.&nbsp;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.&nbsp;See it in the Power Query window.&nbsp;

Forum Discussion

Filtering out data based on column entry based on TEXT

1 Reply

Related content

Filter CSV dataset based on a column

Slicer for filtering several columns

Filter a column based on another column

find max value based on another filtered column

dynamic x-axis based on filter