Data Cleaning

Do you have to retain the last two columns? ie can you simply use "yes" for renewable question (remove the rest) and do you need to know the Higg Integration code?

You can make another table which uses the first as a source, then remove the bits that are not required for the calculation of the value and qty. If you need to view the other data they can be linked to show this in another table on the report.

ie

First table

let
Source = Excel.Workbook(File.Contents("C:\Users\tccack\Downloads\sample file.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type with Locale" = Table.TransformColumnTypes(Table1_Table, {{"BSCI Expired", type date}}, "en-US")
in
#"Changed Type with Locale"

Second table

let
Source = Table1,
#"Removed Columns" = Table.RemoveColumns(Source,{"Higg Integration", "Do you use renewable energy"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"

You can use one for the maths and one for the details?

You may be able to also get this done using measures within the report but selecting the earliest date or a distinct value from a column, or use the MAX value on the qty and value column.