Merging Data Rows in PowerBI

[kevin_tran22]

I have a table that looks like this: 

This data comes from a CSV report that I've pulled from online and connected it to my online data source that is already in PowerBI. This data source is connected by the ID column. I want to merge these two rows to reduce the # of rows we have in my data table report. Highlighted in yellow are the data points that I want to keep. What is the best way to merge both rows and have the PowerBI report look cleaner? 

[vanessafvg]

can you provide the data in text format?  

also what type of scenarios  are you expecting?  is there a rule of what to select over the other?

[kevin_tran22]

@vanessafvg I want first line of data to be deleted only if there is a time series correction (there are some lines that are not time series and less commonly multiple time series). Essentially I want my PBI data table to be less clustered. I'm hoping that I can eliminate one of these lines of data somehow. Yes the rule should be to take data from the bottom row excepted for the effective start date, we want the min value of that. 

ID  Type    Name          Attribute Name   Action                  Effective Start Date   Effective End Date   New Value       Old Value  Knowledge Date   Modified By
1   Equity  Common Units  Payment Rank     Time Series Correction  1/1/2000               12/22/2020           Not Applicable             12/23/2020       TS:mgeick
1   Equity  Common Units  Payment Rank                             12/23/2020             12/23/2020           First lien                 12/23/2020       mgeick

[vanessafvg]

This is an example of one way of doing it. 

I created 2 additional tables in power query that grouped the by the id and min date, and then the id and max date, and i used these two tables by merging with them to bring back the rows i was interested in, assuming this scenario always works.  Obviously if you can push this logic back if your data is really big to a system that has good compute, but if you working with small amounts of data and you dont have access to a database, this should work ok.

see attached