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

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"