- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

grouped rows in power query to show the amount in one column once, not a sumation
I have a huge table with multiple PO's, in my case the PO amount is a gross sum, and then there are different lines that are there to show how much of that gross amonut was spent. I need to see all the lines that show the amount spent, but not have the gross amount be multiolied by how many rows there are. I cant find a way to only show the un multiplied gross amount when I am grouping the rows.
Below is an example of what I am trying to see as a result of this. The PO Amount is the total gross amount allocated to the PO, and the amount spent shows the individual purchases that have used some of the gross amount and is shown in the total line.
PO # Gross amount spend
123456 $4500 $1300
123456 $4500 $1200
totals 123456 $4500 $2500
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Anonymous
Just group on the first two columns.
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRUjExNTAA0YbGQDpWB6uUEUgqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t, #"Gross amount" = _t, Spend = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}, {"Gross amount", Currency.Type}, {"Spend", Currency.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PO #", "Gross amount"}, {{"Total spend", each List.Sum([Spend]), type nullable number}})
in
#"Grouped Rows"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Anonymous
Just group on the first two columns.
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JRUjExNTAA0YbGQDpWB6uUEUgqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t, #"Gross amount" = _t, Spend = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}, {"Gross amount", Currency.Type}, {"Spend", Currency.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"PO #", "Gross amount"}, {{"Total spend", each List.Sum([Spend]), type nullable number}})
in
#"Grouped Rows"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.
Join our Community Sticker Challenge 2025
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Subject | Author | Posted | |
---|---|---|---|
09-18-2023 01:27 AM | |||
10-09-2023 06:43 AM | |||
03-25-2024 02:30 PM | |||
04-25-2024 07:02 AM | |||
07-15-2024 07:05 AM |