Power Query - Split an amount into multiple rows

jon999

New Member
Joined
Aug 24, 2015
Messages
34
Hi

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

Invoice numberLocationQuantityAmount
100A210
101A430
101B10

<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 numberLocationQuantityAmount
100A210
101A424
101B16

<tbody>
</tbody>


Thanks
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

sadath

Board Regular
Joined
Oct 10, 2004
Messages
245
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"}),
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Amount1", each [Quantity]/[TotQty]*[TotAmt]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Amount", "TotQty", "TotAmt"})
in
#"Removed Columns"
 

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,332
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top