Power Query Inserting rows when condition met

[Bi2thelly]

I need to insert rows (preferably above but not necessary) when a condition is met on another row using PQ. I was lookining into Table.InsertRows but I'm having trouble using it. Condition I was trying to use when looking at the Initial table is if [Status] = "inactive" and [Serial ID] <> [Testing2.Serial ID] then insert a new row. An example below showing how the original data for Serial ID X002 is missing the initial active status row which needs to be added. Any help would be appreciated. 

Initial:

Requested Result:

[Bi2thelly]

Thank you very much for your help. Unfortunately neither one of the options completely worked for what I needed as the first option did not provide an "active" status and the second option used the current date and time when the Active Date was needed. After looking through the code above and doing more research on the internet I found that I could provide a list of "active" and "inactive" to each of the selected rows that met the criteria in order to duplicate the row and then replaced one of the rows with an "active" status. This seems to work so far but not sure how inefficient it may be.

[jbwtp]

Hi @Bi2thelly,

Slightly alternative solution to already suggested. This does not care about inserting a row in a particular place, which is in most cases a mere reading convenience and gives no benefits to data processing in the report for a lot of complexity in the code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY6xDsIwDET/JXMl+0xI0pmZHVRlQKhDFxYQ348DShq3ElIGx767d9PkLsxwgxMCkzBGnW/31/KeddB3YAL0IFE/R1+kp7NakHrhdX52WkSXh2+ybJKXR7OoEnVtIdE3yMbzw6zOjhLIlwD+Wz9xTRbs67cIUz6VHWwPCRTr2hLGYAi77qsz5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial ID" = _t, #"Freeze Date" = _t, Status = _t, #"Inactive Date" = _t, #"Active Date" = _t, Index = _t, #"Testing2.Serial ID" = _t, Testing2.Status = _t, Keep = _t, #"Activity Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial ID", type text}, {"Freeze Date", type date}, {"Status", type text}, {"Inactive Date", type date}, {"Active Date", type date}, {"Index", Int64.Type}, {"Testing2.Serial ID", type text}, {"Testing2.Status", type text}, {"Keep", type text}, {"Activity Date", type date}}),
    
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Status] = "inactive" and [Serial ID]<>[Testing2.Serial ID])),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Serial ID", "Status", "Active Date", "Activity Date"}),
    ResetActiveDate = Table.ReplaceValue(#"Removed Other Columns",null,null,(x, y, z) as date=> Date.From(DateTime.LocalNow()),{"Active Date", "Activity Date"}),
    ResetStatus = Table.ReplaceValue(ResetActiveDate,null,null,(x, y, z) as text => "active",{"Status"}),
    
    Output = Table.Combine({ResetStatus, #"Changed Type"})
in
    Output

It is also unclear how do you define the Active and Activity date for the new rows, so you may need to change how they are set in the code.

Kind regards,

John

[Bi2thelly]

You are right. I should not have included the Activity Date in the screenshot as it is produced afterward.

[KT_Bsmart2gethe]

Hi @Bi2thelly ,

Please see below code and outcome:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY6xDsIwDET/JXMl+0xI0pmZHVRlQKhDFxYQ348DShq3ElIGx767d9PkLsxwgxMCkzBGnW/31/KeddB3YAL0IFE/R1+kp7NakHrhdX52WkSXh2+ybJKXR7OoEnVtIdE3yMbzw6zOjhLIlwD+Wz9xTRbs67cIUz6VHWwPCRTr2hLGYAi77qsz5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial ID" = _t, #"Freeze Date" = _t, Status = _t, #"Inactive Date" = _t, #"Active Date" = _t, Index = _t, #"Testing2.Serial ID" = _t, Testing2.Status = _t, Keep = _t, #"Activity Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial ID", type text}, {"Freeze Date", type date}, {"Status", type text}, {"Inactive Date", type date}, {"Active Date", type date}, {"Index", Int64.Type}, {"Testing2.Serial ID", type text}, {"Testing2.Status", type text}, {"Keep", type text}, {"Activity Date", type date}}),


    #"Added Custom" = Table.AddColumn(
                            #"Changed Type", 
                            "Condition", 
                            each 
                                [Status]="inactive" and [Serial ID]<>[Testing2.Serial ID]
                        ),
    #"Added Index" = Table.AddIndexColumn(
                        #"Added Custom", 
                        "Row No", 
                        0, 
                        1, 
                        Int64.Type
                    ),
    GetRowIndex = List.Sort(
                            Table.SelectRows(
                                #"Added Index", 
                                each 
                                    ([Condition] = true)
                            )[Row No], 
                            Order.Descending
                        ),
    InsertRecords = List.Accumulate(
                        {0..List.Count(GetRowIndex)-1},
                        #"Changed Type",
                        (x,y) => 
                            Table.InsertRows(
                                    x,
                                    GetRowIndex{y},
                                    //Empty Record
                                    {#table(
                                        List.Select(
                                            Table.ColumnNames(#"Changed Type"), 
                                            each 
                                                _<>"Serial ID" and _<>"Status" and _<>"Active Date" and _<>"Activity Date"
                                        ), 
                                        {
                                            {null, null, null, null, null, null}
                                        }
                                    ){0}
                                    &
                                    //Specified Record - Value based on inactive record. (i.e. if condition met then take the record value for below fields and join with empty record above to form a full record)
                                    Record.SelectFields(
                                        Record.FromList(
                                            Record.ToList(#"Changed Type"{GetRowIndex{y}}),
                                            Table.ColumnNames(#"Changed Type")
                                        ), 
                                        {"Serial ID", "Status", "Active Date", "Activity Date"}
                                    )}
                            )
                    )
in
    InsertRecords

Regards

KT

[Bi2thelly]

This seems to work so far but is applying "inactive" for the new record and not "active".

[KT_Bsmart2gethe]

Hi @Bi2thelly ,

That's odd. It should add records based on the inactive row's record.

Amend the step below (Delete the RED and Add Blue):

InsertRecords = List.Accumulate(
{0..List.Count(GetRowIndex)-1},
#"Changed Type",
(x,y) =>
Table.InsertRows(
x,
GetRowIndex{y},
//Empty Record
{#table(
List.Select(
Table.ColumnNames(#"Changed Type"),
each
_<>"Serial ID" and _<>"Status" and _<>"Active Date" and _<>"Activity Date"
),
{
{null, null, "inactive", null, null, null, null}
}
){0}
&
//Specified Record - Value based on inactive record. (i.e. if condition met then take the record value for below fields and join with empty record above to form a full record)
Record.SelectFields(
Record.FromList(
Record.ToList(#"Changed Type"{GetRowIndex{y}}),
Table.ColumnNames(#"Changed Type")
),
{"Serial ID", "Status", "Active Date", "Activity Date"}
)}
)
)

The key to ensuring @John or my proposed solution meets your desired outcome is knowing exactly what to do into the insert row. Without the rationale behind it, we can only propose based on the description and sample data.

Regards

KT