Forum Discussion
Insert previous rows with calculated columns
Hi,
Can you please help me on this. I am 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 |
Thanks,
Bobhy
7 Replies
If I understand your logic, the following might work
- Group by ID
- Then use a custom function to
- Group by Code
- Determine which Code has the maximum number of rows
- Use the rows from that code to create the rows to be inserted
Custom Function
//rename fnInsertAERrows (tbl as table)=> let //assuming only a single AER code #"AER Code" = Table.SelectRows(tbl, each [Mode]="AER")[Code]{0}, #"BER Rows" = Table.SelectRows(tbl, each [Mode] = "BER" and [Assignment] <> null), #"Code Count" = Table.Group(#"BER Rows",{"Code"},{ {"Count", each Table.RowCount(_), Int64.Type} }), #"Code with Max Rows" = Table.SelectRows(#"Code Count", each [Count] = List.Max(#"Code Count"[Count]))[Code], #"Full Rows to Insert" = Table.SelectRows(#"BER Rows", each List.Contains(#"Code with Max Rows", [Code])), #"Modified Rows to Insert" = Table.FromRecords(Table.TransformRows(#"Full Rows to Insert", (r) => Record.TransformFields(r, {{"Mode", each "AER"}, {"Cost", each null}, {"Code", each #"AER Code"} }))), #"Insert the rows" = Table.Combine({tbl,#"Modified Rows to Insert"}) in #"Insert the rows"Main Code
let Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", Currency.Type}, {"Mode", type text}, {"AllCost", Currency.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, { {"added Rows", each fnInsertAERrows(_), type table [ID=Int64.Type, Code=Int64.Type, Assignment=Int64.Type, AssignmentDesc=nullable text, Cost=Currency.Type, Mode=nullable text, AllCost=Currency.Type]}}), #"Expanded added Rows" = Table.ExpandTableColumn(#"Grouped Rows", "added Rows", {"Code", "Assignment", "AssignmentDesc", "Cost", "Mode", "AllCost"}) in #"Expanded added Rows"Original Data
Results
It's not working as I have multiple "AER" codes. Please see the chart below for more info. As I am new to M code, how to incorporate the Main Code and the Custom Function?
Thanks
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 60 500 AER 600 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 60 500 AER 600 200 60 34 JDC AER 145.19 200 60 18 OB AER 65 200 60 51 FFY AER 89 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 When your example does not reflect the variability in your actual data, and your description is unclear and incomplete, I am not surprised.
Given your new data, it is merely a matter of changing the custom function to account for more than one "AER" row.
The main function remains the same:
Custom Function
//rename fnInsertAERrows (tbl as table)=> let #"AER Rows" = Table.SelectRows(tbl, each [Mode]="AER"), #"All BER Rows" = Table.SelectRows(tbl, each [Mode]="BER"), #"BER Rows" = Table.SelectRows(tbl, each [Mode] = "BER" and [Assignment] <> null), #"Code Count" = Table.Group(#"BER Rows",{"Code"},{ {"Count", each Table.RowCount(_), Int64.Type} }), #"Code with Max Rows" = Table.SelectRows(#"Code Count", each [Count] = List.Max(#"Code Count"[Count]))[Code], #"Full Rows to Insert" = Table.SelectRows(#"BER Rows", each List.Contains(#"Code with Max Rows", [Code])), #"Modified Rows to Insert" = List.Accumulate(List.Numbers(0,Table.RowCount(#"AER Rows")),{#"All BER Rows"},(state, current)=> state & {Table.FromRecords({#"AER Rows"{current}}) & Table.FromRecords(Table.TransformRows(#"Full Rows to Insert", (r) => Record.TransformFields(r, {{"Mode", each "AER"}, {"Cost", each null}, {"Code", each #"AER Rows"[Code]{current}}})))} ), #"Insert the rows" = Table.Combine(#"Modified Rows to Insert") in #"Insert the rows"Main Function
let //Change next line to reflect your actual data source Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content], //set the data types #"Changed Type" = Table.TransformColumnTypes(Source,{ {"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", Currency.Type}, {"Mode", type text}, {"AllCost", Currency.Type}}), //group rows by ID and aggregate using the Custom function #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, { {"added Rows", each fnInsertAERrows(_), type table [ID=Int64.Type, Code=Int64.Type, Assignment=Int64.Type, AssignmentDesc=nullable text, Cost=Currency.Type, Mode=nullable text, AllCost=Currency.Type]}}), //Expand the resultant tables #"Expanded added Rows" = Table.ExpandTableColumn(#"Grouped Rows", "added Rows", {"Code", "Assignment", "AssignmentDesc", "Cost", "Mode", "AllCost"}) in #"Expanded added Rows"Source Data (your modified data in last post)
Results
The code would be pasted as separate queries into the Query (Power Query) Advanced Editor.
Please read the comments in the code for important changes you will need to make in the pasted code and query name.
As I wrote, I cannot see what you have done to adapt the code to your environment. Without that and without being able to reproduce the error, all I can do is suggest you recheck the code you have copied. My guess is that the error is coming from the custom function, and is due to a typo or mis-copy. Even that's hard to say since you did not show the status at the #"Grouped Rows" step in your screenshot.
PS I just tried again, this time using Power BI Desktop instead of Excel, and also obtained the results without errors.
