Forum Discussion
Helper IIPower Query Inserting rows when condition met
Impactful IndividualHi 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
Bi2thellyThis 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