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

 

[muhammad susanto]

hi alan,
here new link (you can edit).

sample capkin 2022 by sst

docs.google.com

 

[alansidman]

If you are referring to the first table then of course there is no field named "Target", you need to change the field name in Power Query to the name of your field in your sheet. I believe that in this case it is "Target Th 2022". In my example, I used the same field name that you provided in your post. If you expectation is in a different table, then you need to change that field name to match your table field name in the PQ Mcode.

 

[muhammad susanto]

hi alan, i have do change "Target Th 2022" to "Target" but still not work

 

[alansidman]

No you have to change the query to Target Th 2022 and keep the name in your google/excel file.

 

[alansidman]

I have had no success with working with your Google Sheets. I don't have any experience in this arena as I only work in Microsoft products. Good Luck as you will have to wait for someone with both Google Sheets and PQ experience. I cannot get your sheet into PQ nor can I save it as an Excel file.

 

[muhammad susanto]

hi alan...
i have create new simple data source in google sheet
your code not fully work, i think the problem in this formula.
here new code

let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1SPg4Ga6V61_wMw1AmHst0ByzHSI_0tngrcPQj4FmzUw"),
    Sheet1_Table = Source{[name="Sheet1",ItemKind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(Sheet1_Table, "Custom", each if [Column1]<1 then Number.ToText([Column1],"B2") else [Column1])
in
    #"Added Custom"

i upload result after running the code for decimal number is error. i don't know why maybe formula convert to decimal still in problem.
for whole number is correct but for decimal number is not correct
please, see the image

 

[alansidman]

You have B2 in your expression. It should be P2.

 

[muhammad susanto]

Hi Akan, why P2,? What, s the meaning P2, ? It, s cell location or code?

 

[alansidman]

Read this Number.ToText - PowerQuery M

 

[Automatrix]

How about this:

Book2
	A	B	C	D	E	F
1	Target		Target	type data	Target x100	Output
2	0,1		0,1	percentage1	10	10%
3	0,19		0,19	percentage1	19	19%
4	0,65		0,65	percentage1	65	65%
5	6,75		6,75	percentage2	675	6,75%
6	123,5		123,5	percentage2	12350	123,5%
7	1000		1000	whole number	100000	1000
8	50		50	whole number	5000	50
9	15		15	whole number	1500	15
10	67626		67626	whole number	6762600	67626
11	12345		12345	whole number	1234500	12345
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Target", type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Target", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "type data", each if not Text.Contains([Target], ",") then "whole number" else if Text.StartsWith([Target], "0,") then "percentage1" else "percentage2"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Target", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Target x100", each [Target] * 100),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Target x100", type text}, {"Target", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type3", "Output", each if [type data] = "whole number" then [Target] 
else if [type data] = "percentage1" then [Target x100] & "%"
else [Target] & "%"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"Output", type text}})
in
    #"Changed Type4"

Change Target to text type, do a conditional column based on the presence of "," and "0,", change Target back to numbers, do a x100 column to use for one of the data types, finish with a custom conditional column.