Mark a record after duplicate with Line.Repeat

[Satch]

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!

[jgeddes · Accepted solution]

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

[Satch]

Thank you! This works for me!