Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
Ok, this is super quick and dirty.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk, this is super quick and dirty.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
101 | |
91 | |
83 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |