How to change the data type from decimal number to % for 1 row and decimal number to Number in 2 row

[RuN]

Hi Everyone,

I have a dataset which has has 1st row values as % and the second type of row as numbers.

The data type detected is decimal number for all.

How do I change the data type to % from KPI column to q3y2 for row 1?

Also, how do I change the data type to Number for the same columns for row 2? 

I am new to PowerBI. First month. My source data is in excel and have tried changing the type but power Bi refuses to recognise it.

I have tried transposing it, pivot and unpivot, nothing is working. This is all in data transform in PowerBi.

I need to add some calculations column after changing the data type. Also the visualization is a matrix and hence unable to change the data type there as well.

I have recreated the dataset for privacy. The first 2 columns are text fields. The KPI column is the benchmark and I need it. The first row are different percentages in each cell of row 1 showing as a decimal number of different lengths from KPI column onwards. The second row are different integers in each cell showing as a decimal number.

I want the row 1 decimal numbers to show as % without affecting the integer values in row 2 and the text in the first 2 columns. How do I do this? Please help! I am stumped.

Indicator	Indicator Type	KPI	q1 yr1	q2 yr1	q3 y1	q4 y1	q1 y2	q2 y2	q3 y2
text 1	text 3	0.997967867	0.9979678	0.99796	0.9979612	0.997934	0.001	0.83767263	0.77823723
text 2	text 3	1234	1234	123	0	12345	234	567	789

[HotChilli]

Powerbi works with columns of the same type so it looks like you will want to unpivot everything apart from the first 2 columns.

Is the KPI data required, it looks redundant. If so, you can remove it.

Can we rely on Indicator to tell us which values are percentages and which are whole numbers? If so, you can then Pivot this column using the Value column in Values.
This will give you 2 columns (the other columns will be in there as well) which you can assign % or whole number data types to.

--

This is all in Power Query using the interface.

[RuN]

I have recreated the dataset for privacy. 

The first columns are text fields.

The KPI column is the benchmark and I need it.

The first row are different percentages in each cell of row 1 showing as a decimal number of different lengths from KPI column onwards.

The second row are different integers in each cell showing as a decimal number.

[asadmd93]

 Do you have different data types at different rows in all the columns?

[RuN]

The data types are the same for each row - first row are pecentages showing as decimal number hence the 0.897986987397, second row are numbers which are integer values.