Forum Discussion
Satch
1 year agoHelper III
Mark a record after duplicate with Line.Repeat
Hi, I have some records. Some with only qty Some with qty AND a return qty. Those with return qty I duplicate with Line.repeat. But I also want to mark the original wit I (invoice) And the dupl...
- 1 year ago
Here is an example code that does this in Power Query.
Paste the code into the advanced editor of a blank query to see how it works.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjQyNjEF0sZgjlKsDkjCEMg2M7ewBImZgggDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t, LinesTotal = _t, ReturnQty = _t]), change_types = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Product", Int64.Type}, {"LinesTotal", Int64.Type}, {"ReturnQty", Int64.Type}}), group_rows = Table.Group(change_types, {"ID"}, {{"_allRows", each _{0}, type table [ID=nullable number, Product=nullable number, LinesTotal=nullable number, ReturnQty=nullable number]}}), add_mark_column = Table.TransformColumns(group_rows, {{"_allRows", each if Record.Field(_, "ReturnQty") > 0 then Table.FromRecords({Record.AddField(_,"Mark", "I"),Record.AddField(_,"Mark", "C")}) else Table.FromRecords({Record.AddField(_,"Mark", "I")})}}), expand_rows = Table.ExpandTableColumn(add_mark_column, "_allRows", {"Product", "LinesTotal", "ReturnQty", "Mark"}, {"Product", "LinesTotal", "ReturnQty", "Mark"}) in expand_rows
jgeddes
1 year agoSuper User
Here is an example code that does this in Power Query.
Paste the code into the advanced editor of a blank query to see how it works.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMjQyNjEF0sZgjlKsDkjCEMg2M7ewBImZgggDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Product = _t, LinesTotal = _t, ReturnQty = _t]),
change_types = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Product", Int64.Type}, {"LinesTotal", Int64.Type}, {"ReturnQty", Int64.Type}}),
group_rows = Table.Group(change_types, {"ID"}, {{"_allRows", each _{0}, type table [ID=nullable number, Product=nullable number, LinesTotal=nullable number, ReturnQty=nullable number]}}),
add_mark_column = Table.TransformColumns(group_rows, {{"_allRows", each if Record.Field(_, "ReturnQty") > 0 then Table.FromRecords({Record.AddField(_,"Mark", "I"),Record.AddField(_,"Mark", "C")}) else Table.FromRecords({Record.AddField(_,"Mark", "I")})}}),
expand_rows = Table.ExpandTableColumn(add_mark_column, "_allRows", {"Product", "LinesTotal", "ReturnQty", "Mark"}, {"Product", "LinesTotal", "ReturnQty", "Mark"})
in
expand_rowsSatch
1 year agoHelper III
Thank you! This works for me!