Removing duplicates

[tiredofit]

I am trying to remove duplicates using Power Query and have encountered a step which I don't know what it means.

I am using Excel 2019.

Step 1. Go to Data -> Get Data -> From File -> From Workbook

Step 2. Select workbook, click OK

Step 3. Select worksheet within the workbook, then select Transform

My data contains a mixture of numbers and text. At present the data type 123, (ie Whole number) so I click on the 123 and choose text.

I am presented with a message:

Change Column Type

The selected column has an existing type conversion. Would you like to replace the existing conversion, or preserve the existing conversion and add the new conversion as a separate step?

There are three buttons: Replace current, Add new step and cancel.

What does the message mean?

When I chose Add new step then remove dupicates and close and load, I got an error message:

[DataFormat.Error] We couldn't  convert to Number

What is wrong?

Thanks

 

[sandy666]

set Replace Current

 

[tiredofit]

set Replace Current

Thanks but what happens if you choose the other option?

 

[sandy666]

you just tested it = error

Power Query rule: column should have the same type of data not mixed

 

[tiredofit]

you just tested it = error

I know but what does the other option mean and do you know what it shows an error?

 

[sandy666]

Power Query rule: column should have the same type of data not mixed
if you add new step Power Query could be because first change data type is from automatic action based on a few data from the top - in your case you have there a few numbers probably so data type is 123
[DataFormat.Error] We couldn't convert to Number = PQ cannot convert text to number

 

[tiredofit]

Power Query rule: column should have the same type of data not mixed
if you add new step Power Query could be because first change data type is from automatic action based on a few data from the top - in your case you have there a few numbers probably so data type is 123
[DataFormat.Error] We couldn't convert to Number = PQ cannot convert text to number

Thanks for the explanation.

My data has a few hundred thousand rows and the first hundred thousand are whole numbers but then the rest is a mixture of numbers and text.

So if I understand you correctly, the moment the data gets imported into Power Query because it looks at the first so many entries, it automatically (in my case) assumed they were all whole numbers?

 

[sandy666]

Correct
the best way is remove first Change data type added automatically then set each column type manualy (from practice )

but as I said: one column = one data type not mixed

 

[tiredofit]

Correct
the best way is remove first Change data type added automatically then set each column type manualy (from practice )

but as I said: one column = one data type not mixed

Got it, thanks for your help.

BTW, I still prefer VBA to do this! It's 100% correct where I find PQ is sort of "try this, then this, then this" to educate PQ to spot patterns.

 

[sandy666]

so we are different, I am against vba in any form
for PQ you need IDEA first