Formula To Solve Columns With Multiple Data Types

[muhammad susanto]

hi all..

i have problem currently within my dataset there is a column that contains both percentage & numerical data types.
my sheet name is 'new adku' with name field 'Target'
i want to calculate but keep in different types.
i want to decimal number convert to be percentage number and number to be whole number
here the illustration layout

Book1
	C	D	E
1	Target	Output wanted	type data
2	0,1	10,00%	percentage
3	0,19	19,00%	percentage
4	0,65	65,00%	percentage
5	6,75	6,75%	percentage
6	123,5	123,50%	percentage
7	1000	1000,00	whole number
8	50	50,00	whole number
9	15	15,00	whole number
10	67626	67626,00	whole number
11	12345	12345,00	whole number
new adku

i hope someone would help me getting out this problem..
susant

 

[ExceLoki]

this should be a simple solution. let me know if this works for what you need

=IF(E2="percentage",TEXT(D2,"##.0###"),TEXT(D2,"##"))

 

[muhammad susanto]

Hi..
Thanks buat I think not like this.. Column E as a note that i want data type. Just show that i want not helper column.
How about if there is no col E. What, s formula? Sorry, should be column E not shown

 

[ExceLoki]

sorry, i misread your first post. this will work for your data you posted. but there's a problem getting excel to recognize what should be a % vs # when it's just above 1. ex- 1.235. for now i have it looking for any # under 2 to convert to a %.

Book1
	C	D	E	F
1	Target	Output wanted	type data	formula
2	0.1	10.00%	percentage	10.00%
3	0.19	19.00%	percentage	19.00%
4	0.65	65.00%	percentage	65.00%
5	0.0675	6.75%	percentage	6.75%
6	1.235	123.50%	percentage	123.50%
7	1000.5	100.00	whole number	1000.50
8	50	50.00	whole number	50.00
9	15	15.00	whole number	15.00
10	67626	67626.00	whole number	67626.00
11	12345	12345.00	whole number	12345.00
Sheet1

Cell Formulas
Range	Formula
F2:F11	F2	=IF(C2<2,TEXT(C2,"0.00%"),TEXT(C2,"0.00"))

 

[muhammad susanto]

hi i have to modified your formula into Power Query like this but not work/error, how to fix this problem

if[[Target  Th 2022]<2,Text[[Target  Th 2022]="0.00%",text{[Target  Th 2022],"0.00")

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Target", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Target]<1 then Number.ToText([Target],"P2") else [Target])
in
    #"Added Custom"

 

[muhammad susanto]

hi alan..
why not work for me? show message 'the column ' Target' of the table wasn't found'

 

[alansidman]

I don't understand your comment. Below is what my code results in. In the table to the left is the raw/source data. The table to the right is the result of the Mcode I have previously supplied based upon your data supplied.

Book3
	A	B	C	D
1	Target		Target	Custom
2	0.1		0.1	10.00%
3	0.19		0.19	19.00%
4	0.65		0.65	65.00%
5	6.75		6.75	6.75
6	123.5		123.5	123.5
7	1000		1000	1000
8	50		50	50
9	15		15	15
10	67626		67626	67626
11	12345		12345	12345
Sheet1

 

[muhammad susanto]

hi alan..
your formula is correct if to do in Excel as data source
how about if data source in google sheet
here my complete code with data source in google sheet as link

let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1TO4mggqdC3JzfwD8iKHYEGQUMMkpWhu5O3QyS0vDS-w"),
    #"new adku_Table" = Source{[name="new adku",ItemKind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Target", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Target]<1 then Number.ToText([Target],"P2") else [Target])
in
    #"Added Custom"

after running code show message "Expression Error: The Column 'Target of the table wasn't found"
how to fix this problem?
Note:
I'm using Excel 2021 & Power BI Desktop Free

 

[alansidman]

Can you provide through a third party the file you are working with. Without seeing the file, cannot determine the issue you are facing.