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 dupl...
  • jgeddes's avatar
    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