Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Inserting previous rows basing upon a condition

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, ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    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"
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Vijay_A_Verma
Most Valuable Professional
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"
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you for your help!