Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bi2thelly
Helper I
Helper I

Power Query Inserting rows when condition met

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:

Bi2thelly_0-1660067813984.png

 

Requested Result:

Bi2thelly_1-1660067861946.png

 

6 REPLIES 6
Bi2thelly
Helper I
Helper I

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
Memorable Member
Memorable Member

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

 

 

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

KT_Bsmart2gethe
Impactful Individual
Impactful Individual

Hi @Bi2thelly ,

 

Please see below code and outcome:

KT_Bsmart2gethe_0-1660081796184.png

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

 

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors