Data Cleaning

[SelflearningBi]

I am having touble to handle cleaning process.. still learning how to do power query ..

I have a simlar table as below.  
Because the last two columns contain different information/cell that makes previous 10 column infomration duplicated. In this case, I believe when i do any analysis, the value/ quantity will be also duplicated.

I was wondering how to handle this siutation?
1) Make 2 seperate tables? one table for last two columns and another table for previous column? how to do it?
2) transfer last 2 columns into different columns? how to do ?
please help, thanks!

Supplier Name	Supplier Code	Factory Name	Factory Country	Status	Value	Qty	Grading Result	BSCI Expired	Higg FEM Certificate	Higg Integration	Do you use renewable energy
Apple	111	Cici	BD	Active	1000	250	B	6/4/2023	Yes	FEM	Yes, Solar
Apple	111	Cici	BD	Active	1000	250	B	6/4/2023	Yes	FSLM	Yes, Wind
Apple	111	Cici	BD	Active	1000	250	B	6/4/2023	Yes	MSI	Yes,hyrdo
Banana	222	Define Industy	BD	ok	5000	10000	A	8/17/2024	Yes	FEM	Yes, Solar
Banana	222	Define Industy	BD	ok	5000	10000	A	8/17/2024	Yes	FSLM	Yes, Wind

[ryan_mayu]

@SelflearningBi 

right click the table in PQ and select reference.

select last two column , then remove columns to keep previous columns 

or remove other column to only keep these two columns

for your second request, i dont quite understand. could you pls elaborate more?

[Tony_Kuiper · Accepted solution]

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.