Power Query - Split an amount into multiple rows

[jon999]

Hi

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice number[/TD]
[TD]Location[/TD]
[TD]Quantity[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice number[/TD]
[TD]Location[/TD]
[TD]Quantity[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]4[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[sadath]

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"

 

[jon999]

Thanks