New column to identify first transaction ID by company

[A1I]

Hi

I have a table which includes Transaction ID (5 digit number) and Company ID. I need to create a calculated fields which evaluates if a transaction with respective transaction ID is the first transaction made (i.e. no earlier transactions (with lower number) exist), or if it is a later transaction:

So in the example snapshot above, Company "1" has 3 transaction IDs, 20636, 20637 and 20638. I would need a column where the row with ID 20636 has the lable "First Transaction", and where the others have "Further transaction".

Thanks in advance

[AlienSx]

hi, @A1I 

let
    Source = your_table,   
    fx = (t as table) => 
        Table.FromColumns(
            Table.ToColumns(t) & {{"First Transaction"} & List.Repeat({"Further transaction"}, Table.RowCount(t) - 1)},
            Table.ColumnNames(t) & {"column_where"}
            ),
    gr = Table.Group(Source, {"Company-ID"}, {{"all", each fx(Table.Sort(_, "Transaktion-ID"))}}),
    expand = Table.ExpandTableColumn(gr, "all", {"Transaktion-ID", "Transaktion-Datum", "Anzahl Credits", "Betrag", "column_where"})
in
    expand

[tackytechtom]

Hi @A1I ,

How about this:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough) The idea here was to do a group by company-id + min(transaction-id) and then joining the result of that group by with the original table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bczJCcAwDETRXnx2QFu81GLcfxuxJ1EORqB/EDxmjCRUtKSciK91QqLr4b+ZX1IjIshJi4giJ/0ke8HQR4wiciMnHJGCnMhJdhU50Yg05MQi0tGcDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaktion-ID" = _t, #"Transaktion-Datum" = _t, #"Company-ID" = _t, #"Anzahl Credits" = _t, Betrag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaktion-ID", Int64.Type}, {"Transaktion-Datum", type date}, {"Company-ID", Int64.Type}, {"Anzahl Credits", Int64.Type}, {"Betrag", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Company-ID"}, {{"MinTransID", each List.Min([#"Transaktion-ID"]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Company-ID"}, #"Grouped Rows", {"Company-ID"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"MinTransID"}, {"Grouped Rows.MinTransID"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Grouped Rows", "Custom", each if [#"Transaktion-ID"] = [Grouped Rows.MinTransID] then "First Transaction" else "Further transaction"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped Rows.MinTransID"})
in
    #"Removed Columns"

Let me know, if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/