Find percentage of text value in another column

[shparvez]

Hello good people,

I am trying to find percentage of certain text value in another column. Can someone help please?

Continent	Status
Asia	In
Asia	In
Asia	In
Asia	In
Asia	Out
Asia	Out
Africa	Out
Africa	Out
Africa	In
Africa	In

Result I am looking for percentage of Continent+Status in a new column.

Continent	Status	Percentage
Asia	In	40%
Asia	In	40%
Asia	In	40%
Asia	In	40%
Asia	Out	20%
Asia	Out	20%
Africa	Out	20%
Africa	Out	20%
Africa	In	20%
Africa	In	20%

Here, Asia In is 40%, where Asia Out is 20% , followed by Africa Out 20% so as Africa In 20%. 

Thank anyone for help in advance.

[shparvez]

Hi AlienSx,

Many thanks, yes it works, however, in my real table, there are many columns, not possible to do with 'group'.  Do you have any solution without 'group' ?  thanks

[AlienSx]

@shparvez expand as many columns as you want. Expand all of them!

expand = Table.ExpandTableColumn(pct, "rows", Table.ColumnNames(source_table))

[shparvez]

While 'grouping' , I have to mentions all other columns names, not?  Thats very difficult, as I have too many columns. 

 g = Table.Group(
        source_table, 
        {"Continent", "Status"}, 
        {{"rows", each _}, {"Percentage", each Table.RowCount(_), Int64.Type}}

[AlienSx]

@shparvez no, don't do that. Leave them alone. That's the idea behind Table.Group. Just change expand step. 

[shparvez]

Hi,

Suppose the table is as below

Column1, Column2, Column3, Column4, Column5, Column6..... ColumnN

I want to find percetage for values of Column 5 only.  Can you rewrite the code please without mentioning any other columns  ?   Thank you in advance.

[AlienSx]

     g = Table.Group(
        source_table, 
        "Column5", 
        {{"rows", each _}, {"Percentage", each Table.RowCount(_), Int64.Type}}
    )[[rows], [Percentage]],
    ttl = List.Sum(g[Percentage]),
    pct = Table.TransformColumns(g, {"Percentage", each _ / ttl}),
    expand = Table.ExpandTableColumn(pct, "rows", Table.ColumnNames(source_table))

[shparvez]

Hi ,

An error message appears - "Expression.Error: The “Percentage” field already exists in the record."

[AlienSx]

hi

[AlienSx]

hello, @shparvez 

    g = Table.Group(
        source_table, 
        {"Continent", "Status"}, 
        {{"rows", each _}, {"Percentage", each Table.RowCount(_), Int64.Type}}
    )[[rows], [Percentage]],
    ttl = List.Sum(g[Percentage]),
    pct = Table.TransformColumns(g, {"Percentage", each _/ttl}),
    expand = Table.ExpandTableColumn(pct, "rows", {"Continent", "Status"}, {"Continent", "Status"})