cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bobhy
Helper I
Helper I

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, 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     
IDCodeAssignmentAssignmentDescCostMode
2001034JDC120.19BER
2001018OB 58.88BER
2001051FFY82.42BER
20030  1084.51AER
3001034JDC235.67BER
3001023LPT456.34BER
3001045APT700BER
3001018OB 2345.78BER
30090  1000AER
      
Expected Output    
IDCodeAssignmentAssignmentDescCostMode
2001034JDC120.19BER
2001018OB 58.88BER
2001051FFY82.42BER
20030  1084.51AER
2003034JDC AER
2003018OB  AER
2003051FFY AER
3001034JDC235.67BER
3001023LPT456.34BER
3001045APT700BER
3001018OB 2345.78BER
30090  1000AER
3009034JDC AER
3009023LPT AER
3009045APT AER
3009018OB  AER

 

Thank you,

Joyhy

1 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"

View solution in original post

6 REPLIES 6
bobhy
Helper I
Helper I

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      
IDCodeAssignmentAssignmentDescCostModeAllCost
20010  250BER285
2001034JDC120.19BER145.19
2001018OB 58.88BER65
2001051FFY82.42BER89
20020  320BER340
2002032AOL45.34BER78
20030  1084.51AER2000
30010  500BER560
3001034JDC235.67BER300
3001023LPT456.34BER500
30040  700BER756
3004051FFY340BER370
3004045KT290BER300
3004018OB 2345.78BER2587
30090  1000AER1200
       
       
Expected Output      
IDCodeAssignmentAssignmentDescCostModeAllCost
20010  250BER285
2001034JDC120.19BER145.19
2001018OB 58.88BER65
2001051FFY82.42BER89
20020  320BER340
2002032AOL45.34BER78
20030  1084.51AER2000
2003034JDC AER145.19
2003018OB  AER65
2003051FFY AER89
30010  500BER560
3001034JDC235.67BER300
3001023LPT456.34BER500
30040  700BER756
3004051FFY340BER370
3004045KT290BER300
3004018OB 2345.78BER2587
30090  1000AER1200
3009051FFY AER370
3009045KT AER300
3009018OB  AER2587

 

 

ziying35
Impactful Individual
Impactful Individual

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
Helper I
Helper I

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

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"

Thank you for your help!

Vijay_A_Verma
Super User
Super User

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"

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors