# How to divide or direct percentage from sum column to each column value

#### srehman

##### Board Regular
Hi Team,

I want to get divide or direct percentage of column A,B,C,D , Dead column by sum. no decimal required with percenatge. only in PQ.

I am doing one by one each column which long process.

Book1
CDEFGHIJ
3115163160600006
4262958900600006
5369591300600006
6415131420600006
7526431470600006
8616021240600006
9726439080600006
10816298490600006
11913241130600006
121015120560600006
131164145520600006
141262957530600006
151362953570510006
161413482750510006
171528630060510006
181669595350420006
Sheet1

#### Attachments

• 32.9 KB Views: 8
• 27.9 KB Views: 8

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### alansidman

##### Well-known Member
Here is the Mcode for only adjusting Column A. You will need to do the same for each of the other columns. I saw no point in repeating this exercise since it was for a learning experience exercise and not the actual data.

Rich (BB code):
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"SKU", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"DEAD", Int64.Type}, {"SUM", Int64.Type}}),
#"Inserted Percent Of" = Table.AddColumn(#"Changed Type", "Percent Of", each [A] / [SUM] * 100, type number),
#"Inserted Round Up" = Table.AddColumn(#"Inserted Percent Of", "Round Up", each Number.RoundUp([Percent Of]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Round Up",{"Percent Of"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "SKU", "Round Up", "A", "B", "C", "D", "DEAD", "SUM"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"A"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Round Up", "A"}})
in
#"Renamed Columns"``````

#### srehman

##### Board Regular
Thanks Yes i have done same way one by one.
I was looking for short method. I understand sometime no choice.

#### alansidman

##### Well-known Member
Don't know if there is a way to do multiple columns at once. But once the code is done, it can be used over and over again.

#### sandy666

##### Well-known Member
try
Rich (BB code):
``````// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Index", "SKU", "SUM"}, "Attribute", "Value"),
Prefix = Table.TransformColumns(Unpivot, {{"Attribute", each "% " & _, type text}}),
PercentOf = Table.AddColumn(Prefix, "Percent Of", each [Value] / [SUM] * 100, type number),
Round = Table.TransformColumns(PercentOf,{{"Percent Of", each Number.Round(_, 0), type number}}),
RC = Table.RemoveColumns(Round,{"Value"}),
Pivot = Table.Pivot(RC, List.Distinct(RC[Attribute]), "Attribute", "Percent Of", List.Sum),
TSC = Table.SelectColumns(Pivot,{"Index", "SKU", "% A", "% B", "% C", "% D", "% DEAD", "SUM"})
in
TSC``````

#### srehman

##### Board Regular
Oh i Just checked i will give you response time difference it was night here in Melbourne.
Thank again Sandy

#### srehman

##### Board Regular
Hi Sandy,

I have implemented your approached unbelievable ideas you have.

Thanks a lot.

#### Attachments

• 33.6 KB Views: 4

#### sandy666

##### Well-known Member
You are welcome
Thanks for the feedback

1,102,140
Messages
5,484,976
Members
407,478
Latest member
wsupaul

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...