# Power Query - Split an amount into multiple rows

#### jon999

##### New Member
Hi

I have a scenario where I need to be able to split the amount over multiple rows. Example of current data

 Invoice number Location Quantity Amount 100 A 2 10 101 A 4 30 101 B 1 0

<tbody>
</tbody>

What I need to do is against invoice 101 be able to split the total invoice amount being 30 over the rows for invoice 101 based on quantity. The calculation needs to be:

Location A 4/5 * 30 = 24
Location B 1/5 * 30 =6

So it needs to look like this

 Invoice number Location Quantity Amount 100 A 2 10 101 A 4 24 101 B 1 6

<tbody>
</tbody>

Thanks

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice number", Int64.Type}, {"Location", type text}, {"Quantity", Int64.Type}, {"Amount", Int64.Type}}),
Table1 = Table.Group(#"Changed Type", {"Invoice number"}, {{"TotQty", each List.Sum([Quantity]), type number}, {"TotAmt", each List.Sum([Amount]), type number}}),

#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Invoice number", Int64.Type}, {"Location", type text}, {"Quantity", Int64.Type}, {"Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Invoice number"}, {{"Count", each _, type table}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows",{"Invoice number"},Table1,{"Invoice number"},"Table1",JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"TotQty", "TotAmt"}, {"TotQty", "TotAmt"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Expanded Table1", "Count", {"Location", "Quantity", "Amount"}, {"Location", "Quantity", "Amount"}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Amount", "TotQty", "TotAmt"})
in
#"Removed Columns"

Thanks

Replies
8
Views
232
Replies
2
Views
141
Replies
2
Views
42
Replies
0
Views
346
Replies
7
Views
799

1,203,081
Messages
6,053,416
Members
444,662
Latest member
AaronPMH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back