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"
ziying35's avatar
ziying35
Impactful Individual
4 years ago

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