Forum Discussion
Data Cleaning
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 |
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.
2 Replies
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.
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?
