cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SelflearningBi
Helper II
Helper II

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 NameSupplier CodeFactory NameFactory CountryStatusValueQtyGrading ResultBSCI ExpiredHigg FEM CertificateHigg IntegrationDo you use renewable energy
Apple111CiciBDActive1000250B6/4/2023YesFEMYes, Solar
Apple111CiciBDActive1000250B6/4/2023YesFSLMYes, Wind
Apple111CiciBDActive1000250B6/4/2023YesMSIYes,hyrdo
Banana222Define IndustyBDok500010000A8/17/2024YesFEMYes, Solar
Banana222Define IndustyBDok500010000A8/17/2024YesFSLMYes, Wind

1 ACCEPTED SOLUTION
Tony_Kuiper
Helper I
Helper I

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.

View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@SelflearningBi 

right click the table in PQ and select reference.

1.PNG

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

or remove other column to only keep these two columns

2.png

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Tony_Kuiper
Helper I
Helper I

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.