Is there a quick way to group together a new column

[EaglesTony]

I have the following Data:

Key    Status       Count

123    Backlog    5

123    Done        4

456    Backlog    8

456    Started     4

456    Done        1

What I need is 1) a total count by Key and then the percentage for each row, so I would have the following:

Key    Status       Count     TotalRecordsForKey   PercentageOfTotal

123    Backlog    5             9                                 56%

123    Done        4             9                                 44%

456    Backlog    8            13                                62%

456    Started     4            13                                31%   

456    Done        1            13                                 8%

Thanks

[EaglesTony · Accepted solution]

I was able to use the existing data with a 100% stacked bar chart and my results are correct using this visual.

[v-sgandrathi]

Hi @EaglesTony, Thankyou for your reply.

If your query has resloved, please mark the helpful reply as the accepted solution and give Kudos, as this will help other members with similar queries.

Thankyou for Reaching out to community Forum.

[v-sgandrathi]

Hi @EaglesTony,

Thankyou for using Microsoft Community Forum.

If your issue is resolved, kindly accept the helpful reply as the solution and give Kudos to assist other members with similar queries.

Sahasra.

[EaglesTony]

So, I am getting closer and have the following:

Key      Status                TotCountByStatus

123      Not Started       28

123      Done                 14

123      In Progress          5

I need to get one row someway as

Key      Status                TotCountByStatus     Status  TotCountByStatus    Status            TotCountByStaus

123      Not Started       28                             Done     14                           In Progress    5 

[v-sgandrathi]

Hi @EaglesTony,

Thank you community members for taking the time to provide suggestions for the query.

I wanted to check if you had the opportunity to review the information provided by @dufoq3. Please feel free to contact us if you have any further questions.

If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you for your understanding.

[dufoq3]

Hi @EaglesTony, 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8ssvUQguSSwqSU0B8owslGJ1YFIu+XmpQMrQBEnMM08hoCg/vSi1uBjIM1WKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Status = _t, TotCountByStatus = _t]),
    Transformed = [ transformed = List.Combine(List.Transform(Table.Split(Source, 1), Table.ToColumns)),
    colNames = [ a = Table.ColumnNames(Source),
                 b = List.Transform({"2"..Number.ToText(List.Count(transformed) / List.Count(a))}, (x)=> List.Transform(a, (y)=> y & x)),
                 c = a & List.Combine(b)
               ][c],
    toTable = Table.FromColumns(transformed, colNames)
  ][toTable]
in
    Transformed

[EaglesTony]

I'm not sure where are teh column names ?

[v-sgandrathi]

Hi @EaglesTony,

The column names in the query are generated dynamically, but they might not align correctly. To fix this, make sure the naming logic matches the transformed data.

You can manually define column names in Table.FromColumns or check colNames to verify their creation.

Hope this might clarifies your query. If this post was helpful, please consider marking Accepting it as a solution to guide other members in finding it more easily.

Regards,
Sahasra.

[Omid_Motamedise]

Use this formula

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVgACJR0lp8Tk7Jz8dCjPVClWB1nWJT8vVQECgDwTsKyJqRlWvRZossEliUUlqSk49KKabKgUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Status = _t, Count = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(Table.SelectRows(#"Changed Type", (x)=>x[Key]=[Key])[Count])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Percentage", each [Count]/[Total])
in
#"Added Custom1"

[Chewdata]

Hey!

You can achieve this without using groupby function, but instead make use of lists.

Table.SelectRows(Source, (Row) => Row[Key] = _[Key])[Count]) gives you a list with all the counts of of the same key in the previous(in this case Source) step. List.Sum() then sums this list, giving you the total.

let
    Source = YOURDATA,
    add_TotalRecordsForKey = Table.AddColumn(Source, "TotalRecordsForKey", each List.Sum(Table.SelectRows(Source, (Row) => Row[Key] = _[Key])[Count])),
    add_PercentageOfTotal = Table.AddColumn(add_TotalRecordsForKey, "PercentageOfTotal", each Value.Divide([Count],[TotalRecordsForKey]) ?? 0, Percentage.Type)
in
    add_PercentageOfTotal

Hopefully this helps, if so please consider giving kudos and accepting this response as a solution, so other users can find it quickly!

[EaglesTony]

Seems like this takes a little while to refresh to get the results:

 add_TotalRecordsForKey = Table.AddColumn(Source, "TotalRecordsForKey", each List.Sum(Table.SelectRows(Source, (Row) => Row[Key] = _[Key])[Count])),

[Gabry]

Hey buddy,

do you want to do it in power query?

 GroupedRows = Table.Group(
        Source, 
        {"Key"}, 
        {{"TotalRecordsForKey", each List.Sum([Count]), type number}}
    ),

    Merged = Table.NestedJoin(
        Source, 
        {"Key"}, 
        GroupedRows, 
        {"Key"}, 
        "GroupedData", 
        JoinKind.LeftOuter
    ),

    /
    Expanded = Table.ExpandTableColumn(
        Merged, 
        "GroupedData", 
        {"TotalRecordsForKey"}, 
        {"TotalRecordsForKey"}
    ),

    
    WithPercentage = Table.AddColumn(
        Expanded, 
        "PercentageOfTotal", 
        each Number.Round(([Count] / [TotalRecordsForKey]) * 100, 2),
        type number
    )

[EaglesTony]

Yes

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVgACJR0lp8Tk7Jz8dCjPVClWB1nWJT8vVQECgDwTsKyJqRlWvRZossEliUUlqSk49KKabKgUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key    " = _t, #"Status     " = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Key    "}, {{"Rows", each _, type table [#"Key    "=nullable text, #"Status     "=nullable text, Count=nullable number]}, {"TotalRecordsForKey", each List.Sum([Count]), type nullable number}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Status     ", "Count"}, {"Status     ", "Count"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Rows", "PercentageOfTotal", each [Count]/[TotalRecordsForKey], Percentage.Type)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.