Change type of multiple formats in same column in one step

[Anonymous]

Spoiler

Hi,

I have a table consiting of numbers in text format, percentage, decimals and whole numbers. Is there a simple way I can change the format of all numbers so for example 70% comes as 70 and not 0.7, the text comes as numbers in one without having to extract the numbers and do it in multiple steps using M in power query ?

Kind regards,
Nina

[bfernandez]

If I am understanding your question correctly, it seems that these fields come from the source in a certain format. 

Is this an Excel Data Source?

If this is the case, the underlying data coming from Excel treats the value as a general or numeric data type.

I know you want to use M as minimally as possible, but by using a custom column I beleive you can achieve what you are looking for:

Let me know if this solves your issue or if you need further assitance! 

[Anonymous]

@bfernandez Is it possible to also set the column type as whole number (integer) in the same step?

[bfernandez]

I have tried befor using Number.abs([column]) to some effect but this will not change the data type for the column automatically. 

You need to change the data type afterwards.

[Anonymous]

@bfernandez  do you know if there is a way to transform the percentage to whole numbers when loading the data from excel to power bi? So you don't need to change the type in power query?

[bfernandez]

Only by manipulating the source data, which you never want to do. Make all the changes within Power BI for what you;re trying to accomplish.

@Anonymous is there any particular reason you do not want to manipulate the data in Power Query? 

These transformations and small changes you are making will not make a big difference in the performance of the report.

You can find more information regarding Power BI performance best practices here.

[Anonymous]

@bfernandez  if the percentage is over 100% than the formula of  "if [column name] > 1 then 100* [column name]" will not be valid.. And with a large amount of rows i'm worried that the performance will be reduced when needing to change the data type in many steps. 
The data is presented in a Pivot table in Excel that I need to load into Power BI, so the column with values will contain numbers from percentage, decimals, whole numbers and sometimes trom text. If I then need to extract the percentage-value and transform these and merge the rows I'm worried of the performance when the amount of rows will increase.

[bfernandez]

I would need to see a data you are working with. Is there any way you can provide the file with the data cleaned of secure data?

It does not make much sense to me why there would be percentage values as well as whole number values.

The Pivot Table in Excel needs to be reading from source data so can you provide that?