Column type switched unexpectedly in powerquery

[Anonymous]

I stumbled onto a very strange behavior of PowerQuery when importing data from an excelfile.

I read a file, clean it up a a little, add 2 columns (copy1 and copy2).

If I keep the original column, all is well and the columns behave as expected.

If I remove the original column, "copy2" switches it's datatype to "text". I'm completely dumbfounded as to why this occurs.

excel file with data 
pbix file with tables 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

As checked your sample pbix file, the original numeric columns were not recognized as a numeric type, and their data type is Any. Therefore, please add one step "Changed Type" to change the data type of these columns as numeric type before the step of adding the copied columns. Then the copied columns will also show the correct data type.

Best Regards