Forum Discussion

Satch's avatar
Satch
Helper III
1 year ago
Solved

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

  • 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's avatar
      Satch
      Helper III

      Thank you! This works for me!