Forum Discussion
Solved
Put Amounts in some rows in Power Query
Hello, I have the following problem: in power Query I have 3 columns, the first called "Orders", the second "Amount A" and the third "Amount B". The "Orders" column is ordered from lowest to highest...
I've finally solved it with Visual Basic from excel.
Seeing the resulting table that you have put I see that this proposal does not solve it since in the Single Amount column, in the first row of the order 2222 you should put the sum of 1.5 + 2.5 + 2.5 = 6.1 and in the rest of the rows of the Single Amount column of this order you would have to put "0". In order 5555 in Single Amount it says 300 when it should be (300+300+300=900).
Is this output you want?
Put the following code to Advanced Editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBCgAhCIXhu7iOeGoxh5Huf415Ey1S5l8EfkoRokyaKMAXslqIMQ62SftEYt9slbWjLJx992eRUBMOxuEpH0x23Xpm/+dx83oB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, #"Imports A" = _t, #"Import B" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Imports A", type number}, {"Import B", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Order"}, {{"Count", each _, type table [Order=nullable number, Imports A=nullable number, Import B=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Imports A", "Import B", "Index"}, {"Imports A", "Import B", "Index"}),
#"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Order", "Import B"}, {{"Min_B", each List.Min([Import B]), type number}, {"Data", each _, type table [Order=nullable number, Imports A=number, Import B=number, Index=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Data],"Index_B",1,1)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Data"}),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Imports A", "Index", "Index_B"}, {"Imports A", "Index", "Index_B"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom1",{"Order", "Imports A", "Import B", "Min_B", "Index", "Index_B"}),
#"Added Custom2" = Table.AddColumn(#"Reordered Columns", "Flag", each if [Import B]<>0 and [Import B]=[Min_B] and [Index_B]<>1 then 1 else 0),
#"Added Custom4" = Table.AddColumn(#"Added Custom2", "Custom", each Table.RowCount(
Table.SelectRows(#"Added Custom2",(x)=> x[Order]=[Order] and x[Flag]=1))),
#"Added Custom3" = Table.AddColumn(#"Added Custom4", "Single Count", each if [Custom]>0 and [Index]=1 then List.Sum(Table.SelectRows(#"Added Custom4",(x)=> x[Order]=[Order])[Import B]) else if [Index_B]=1 and [Custom]=0 then [Import B] else 0),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"Min_B", "Index", "Index_B", "Flag", "Custom"})
in
#"Removed Columns2"Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.