Inserting previous rows basing upon a condition

[bobhy]

How to insert the same number of rows for Mode=AER in comparision to Mode=BER for that respective "ID" (here in this case "200" & "300") and also to copy the previous column data for ID, Assignment, AssignmentDesc? 
(Expecting output similar to the one outlined in the chart below under the heading "Expected output" ). Please see the chart below for more info.

Current Data
ID	Code	Assignment	AssignmentDesc	Cost	Mode
200	10	34	JDC	120.19	BER
200	10	18	OB	58.88	BER
200	10	51	FFY	82.42	BER
200	30			1084.51	AER
300	10	34	JDC	235.67	BER
300	10	23	LPT	456.34	BER
300	10	45	APT	700	BER
300	10	18	OB	2345.78	BER
300	90			1000	AER
Expected Output
ID	Code	Assignment	AssignmentDesc	Cost	Mode
200	10	34	JDC	120.19	BER
200	10	18	OB	58.88	BER
200	10	51	FFY	82.42	BER
200	30			1084.51	AER
200	30	34	JDC		AER
200	30	18	OB		AER
200	30	51	FFY		AER
300	10	34	JDC	235.67	BER
300	10	23	LPT	456.34	BER
300	10	45	APT	700	BER
300	10	18	OB	2345.78	BER
300	90			1000	AER
300	90	34	JDC		AER
300	90	23	LPT		AER
300	90	45	APT		AER
300	90	18	OB		AER

Thank you,

Joyhy

[bobhy]

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

[ziying35]

try this:

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("pdK9CsIwEADgd7m5hPy2abb+WFAURVxEHUSLCLUOsZP47ja1lKBBrW5JyH133N3qCsMUFMXYg+S8z0GR+hRpfTyUp7y8gGLcvqe53oGCUZqACdD1B0IxIqEHkyYc4sEcbt5HlkgHO43XFcZb3NlCIin70oI46CxbdqqkiNNPKntSy6ooXt3Ha9sILDkyyVs4smD2R4MpE8gPnPW+YSlzsOPZomO58JHJ3Y/lwsFGFhuY2H7md7tAGRcocG+DhYc/zM0quRna5g4=", BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source),
    acc = List.Accumulate(rows,
              {{}, {}}, 
              (s,c)=>if c{5}<>"AER" then 
                          {s{0}&{List.ReplaceRange(c, 4, 2, {null, "AER"})}, s{1}&{c}} 
                     else 
                          {{}, s{1}&{c}&s{0}}),
    result = #table(Table.ColumnNames(Source), acc{1})
in
    result

[bobhy]

Hi,

Rows are inserting accordingly,But value for the column "MODE" is replaced/copied by its previous row value.In our case it should not.
Please see the chart below for more info.

ID	Code	Assignment	AssignmentDesc	Cost	Mode
200	10	18	OB	58.88	BER
200	10	34	JDC	120.19	BER
200	10	51	FFY	82.42	BER
200	30			1084.51	AER
200	30	18	OB		BER—AER Instead of “BER”
200	30	34	JDC		BER—AER Instead of “BER”
200	30	51	FFY		BER—AER Instead of “BER”
300	10	18	OB	2345.78	BER
300	10	23	LPT	456.34	BER
300	10	34	JDC	235.67	BER
300	10	45	APT	700	BER
300	90			1000	AER
300	90	18	OB		BER—AER Instead of “BER”
300	90	23	LPT		BER—AER Instead of “BER”
300	90	34	JDC		BER—AER Instead of “BER”
300	90	45	APT		BER—AER Instead of “BER”

Thanks,

Joyhy

[Vijay_A_Verma · Accepted solution]

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"

[bobhy]

Thank you for your help!

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

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"}),
            #"Appended Query" = Table.Combine({Tbl, Custom3}),
            #"Filled Down" = Table.FillDown(#"Appended Query",{"Code"})
        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"