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

[srehman]

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
	C	D	E	F	G	H	I	J
2	Index	SKU	A	B	C	D	DEAD	SUM
3	1	15163160	6	0	0	0	0	6
4	2	62958900	6	0	0	0	0	6
5	3	69591300	6	0	0	0	0	6
6	4	15131420	6	0	0	0	0	6
7	5	26431470	6	0	0	0	0	6
8	6	16021240	6	0	0	0	0	6
9	7	26439080	6	0	0	0	0	6
10	8	16298490	6	0	0	0	0	6
11	9	13241130	6	0	0	0	0	6
12	10	15120560	6	0	0	0	0	6
13	11	64145520	6	0	0	0	0	6
14	12	62957530	6	0	0	0	0	6
15	13	62953570	5	1	0	0	0	6
16	14	13482750	5	1	0	0	0	6
17	15	28630060	5	1	0	0	0	6
18	16	69595350	4	2	0	0	0	6
Sheet1

 

[alansidman]

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.

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]

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

Thanks for your time. You always helpful.

 

[alansidman]

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]

try

// 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]

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

 

[srehman]

Hi Sandy,

I have implemented your approached unbelievable ideas you have.

Thanks a lot.

 

[sandy666]

You are welcome
Thanks for the feedback