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 duplicate with C (Credit)
How can I achieve this?
See here what I'm looking for.
Thanks!
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
2 Replies
- jgeddesSuper 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_rows- SatchHelper III
Thank you! This works for me!