Insert previous rows with calculated columns

[bobhy]

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

[bobhy]

I got the error with the same data that I posted.  The only difference is that my source is SQL server table with the same data that I have provided you. Yes, the error returned at the #"Group Rows" step. Please see the screenshot below for more info. Thanks.

[ronrsnfld]

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.

[ronrsnfld]

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

[bobhy]

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

[ronrsnfld]

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.

[bobhy]

I followed your steps but while executing the "Main Function", I am getting the following error message. Can you please look into this error message and help me on this.

Expression.Error: We cannot convert a value of type Table to type Function.
Details:
Value=[Table]
Type=[Type]  

[ronrsnfld]

I copy/pasted your latest data, and my latest code, and did not obtain any error message.

That type of message is usually due to some error in the code, but I cannot reproduce it here. Nor can I see what you have done in adapting it to your environment.  Do you obtain the error with the data you posted?  On what step does the error message first appear?  Is there an error returned at the #"Group Rows" step?