Forum Discussion
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 and registrations can be repeated or only one exit. In "Amount A" there are always records but in Amount B, there are not always but when there are and the order is repeated (for example 4 times) said "amount B" is repeated with the same value those four times.
What I need is to add a fourth column called "Single Amount" in which I put the same value of amount B but only in the first row of each order, that is, if there is only one order because it puts the same amount as Amount B but if there are 2 or more repeated orders, I put the value of amount B only in the first row and in other rows with the same Ask to put 0 Is this possible to do? I attach an example with the result I am looking for.
| RESULT TABLE WITH THE COLUMN "SINGLE AMOUNT" I WANT | |||
| Order | Imports A | Import B | Single Amount |
| 1111 | 100 | 0 | 0 |
| 2222 | 200 | 1.500 | 1.500 |
| 2222 | 300 | 1.500 | 0 |
| 2222 | 1.000 | 1.500 | 0 |
| 3333 | 3.000 | 0 | 0 |
| 3333 | 1.000 | 0 | 0 |
| 4444 | 700 | 2.500 | 2.500 |
| 5555 | 1.000 | 300 | 300 |
| 5555 | 3.000 | 300 | 0 |
| 5555 | 4.000 | 300 | 0 |
Thanks in advance,
Best regards
I've finally solved it with Visual Basic from excel.
5 Replies
You can 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", "Import B"}, {{"Data", each _, type table [Order=nullable number, Imports A=nullable number, Import B=nullable number]}, {"Min_B", each List.Min([Import B]), type nullable number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Imports A", "Index"}, {"Imports A", "Index"}), #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Single Amount", each if [Import B]=[Min_B] and [Index]=1 then [Import B] else 0), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Min_B", "Index"}) in #"Removed Columns1"Output
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.
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.
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).
I've finally solved it with Visual Basic from excel.
