Forum Discussion
Inserting previous rows basing upon a condition
- 4 years ago
Use this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/dCsIwDEbfpdcjNH9rdunUXYigiDcy9v6vYRoFKyuUQxNO2i/rmijnNCSsYHFcTsdaUwac/DKfH2kb/jQ0x212qIFZX1J0LMvLaQRCO4srPgezCcTA4atwNxSxwlialxqN2HG9P52iI8RURxOtv4RWottxfvsRi0KxnTa12aMTwbc3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Code = _t, Assignment = _t, AssignmentDesc = _t, Cost = _t, Mode = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Temp", each _, type table [ID=nullable number, Code=nullable number, Assignment=nullable number, AssignmentDesc=nullable text, Cost=nullable number, Mode=nullable text]}}), //Function Start fxProcess=(Tbl)=> let #"Filtered Rows" = Table.SelectRows(Tbl, each ([Mode] = "BER")), Custom2 = Table.ReplaceValue(#"Filtered Rows",each [Code],each null,Replacer.ReplaceValue,{"Code"}), Custom3 = Table.ReplaceValue(Custom2,each [Cost],each null,Replacer.ReplaceValue,{"Cost"}), Custom4 = Table.ReplaceValue(Custom3,each [Mode],each null,Replacer.ReplaceValue,{"Mode"}), #"Appended Query" = Table.Combine({Tbl, Custom4}), #"Filled Down" = Table.FillDown(#"Appended Query",{"Code", "Mode"}) in #"Filled Down", //Function End #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "Temp"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"}, {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"}) in #"Expanded Custom"
Hi,
I am trying to use your given logic on a table which has nearly 50K rows but ran into issues on some of the scenarios which I have not outlined in my earlier post. Now I have updated those scenarios and can you please help me on this. (Expecting output similar to the one outlined in the chart below under the heading "Expected output" ). Please see the chart below for more info.
My requirements are:
1) Take number of rows from where combination of the respective ID & Code is maximum for Assignment & AssignmentDesc is not null for column Mode='BER' and insert those rows for column Mode='AER'.
2) Newly inserted rows for column Mode="AER" should carry Assignment,AssignmentDesc, AllCost column values from their copied/previous rows. Here ALLCost is a "calculated column".
3) Newly inserted rows for column Mode="AER" should have the existing values as it is for ID, Code, Mode columns and cost value as null/blank.
| Current Data | ||||||
| ID | Code | Assignment | AssignmentDesc | Cost | Mode | AllCost |
| 200 | 10 | 250 | BER | 285 | ||
| 200 | 10 | 34 | JDC | 120.19 | BER | 145.19 |
| 200 | 10 | 18 | OB | 58.88 | BER | 65 |
| 200 | 10 | 51 | FFY | 82.42 | BER | 89 |
| 200 | 20 | 320 | BER | 340 | ||
| 200 | 20 | 32 | AOL | 45.34 | BER | 78 |
| 200 | 30 | 1084.51 | AER | 2000 | ||
| 300 | 10 | 500 | BER | 560 | ||
| 300 | 10 | 34 | JDC | 235.67 | BER | 300 |
| 300 | 10 | 23 | LPT | 456.34 | BER | 500 |
| 300 | 40 | 700 | BER | 756 | ||
| 300 | 40 | 51 | FFY | 340 | BER | 370 |
| 300 | 40 | 45 | KT | 290 | BER | 300 |
| 300 | 40 | 18 | OB | 2345.78 | BER | 2587 |
| 300 | 90 | 1000 | AER | 1200 | ||
| Expected Output | ||||||
| ID | Code | Assignment | AssignmentDesc | Cost | Mode | AllCost |
| 200 | 10 | 250 | BER | 285 | ||
| 200 | 10 | 34 | JDC | 120.19 | BER | 145.19 |
| 200 | 10 | 18 | OB | 58.88 | BER | 65 |
| 200 | 10 | 51 | FFY | 82.42 | BER | 89 |
| 200 | 20 | 320 | BER | 340 | ||
| 200 | 20 | 32 | AOL | 45.34 | BER | 78 |
| 200 | 30 | 1084.51 | AER | 2000 | ||
| 200 | 30 | 34 | JDC | AER | 145.19 | |
| 200 | 30 | 18 | OB | AER | 65 | |
| 200 | 30 | 51 | FFY | AER | 89 | |
| 300 | 10 | 500 | BER | 560 | ||
| 300 | 10 | 34 | JDC | 235.67 | BER | 300 |
| 300 | 10 | 23 | LPT | 456.34 | BER | 500 |
| 300 | 40 | 700 | BER | 756 | ||
| 300 | 40 | 51 | FFY | 340 | BER | 370 |
| 300 | 40 | 45 | KT | 290 | BER | 300 |
| 300 | 40 | 18 | OB | 2345.78 | BER | 2587 |
| 300 | 90 | 1000 | AER | 1200 | ||
| 300 | 90 | 51 | FFY | AER | 370 | |
| 300 | 90 | 45 | KT | AER | 300 | |
| 300 | 90 | 18 | OB | AER | 2587 |