Forum Discussion
Solved
Grouping by Column Value Question
I have the following data in a table: Project Contract Total Fee Tier Project Group 5166.135 107,850.00 >= 3M to < 5M 5166 5166.136 76,000.00 >= 3M to < 5M 5166 ...
In Power Query you could add the following two lines of code...
Custom1 = Table.AddColumn(#"Changed Type", "Project Group Sum", each List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[Project Group]=[Project Group])[Contract Total]), type number),
Custom2 = Table.AddColumn(Custom1, "Project Group Fee Tier", each if [Project Group Sum] >= 5000000 then ">=5M" else if [Project Group Sum] >= 3000000 then ">=3M to 5M" else if [Project Group Sum] >= 1000000 then ">=1M to 3M" else "<1M", type text)where #"Changed Type" is the previous step in your query.
In Power Query you could add the following two lines of code...
Custom1 = Table.AddColumn(#"Changed Type", "Project Group Sum", each List.Sum(Table.SelectRows(#"Changed Type", (x)=> x[Project Group]=[Project Group])[Contract Total]), type number),
Custom2 = Table.AddColumn(Custom1, "Project Group Fee Tier", each if [Project Group Sum] >= 5000000 then ">=5M" else if [Project Group Sum] >= 3000000 then ">=3M to 5M" else if [Project Group Sum] >= 1000000 then ">=1M to 3M" else "<1M", type text)
where #"Changed Type" is the previous step in your query.
