Forum Discussion

Friedrich902's avatar
Friedrich902
Regular Visitor
3 years ago
Solved

Get the first element from list which is not taken

Hello to everyone,   I want to take the first element from a list in the same row which was not taken from a row with a smaller index. The Idea ist to match debit and credit bookings.   Do you k...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hello, Friedrich902 

        s = your_table,
    lst = s[IndexMatching Elements Index],
    new_col = 
        List.Accumulate(
            lst,
            {},
            (s, c) => s & {List.Difference(c ?? {null}, List.RemoveNulls(s)){0} ? }
        ),
    result = Table.FromColumns( Table.ToColumns(s) & {new_col}, Table.ColumnNames(s) & {"Desired Result Custom Function"} )
Friedrich902's avatar
Friedrich902
Regular Visitor
3 years ago

Hello AlienSX, than you so much for the solution. 

I have an additional problem.

 

I want to add a column "Desired Result Custom Function 2" which takes all elements from the list "All Elements Index" which are not taken in the new column "Result Custom Function 1". Is this possible?

 

DateBooking TextTransaction textAccountValueAttributeIndexAll Elements IndexMatching Elements IndexResult Custom Function 1Desired Result Custom Function 2
06.10.2021ACDTransaction ADebitAccountA1209{7,8,9}{7,9}7null
06.10.2021ACDTransaction ADebitAccountA1203376{7,8,9}{7,9}9null
06.10.2021ACDTransaction ADebitAccountA5030415{7,8,9}  8
06.10.2021ACDTransaction ADebitAccountA12040551{7,8,9}{7,9} 8

 

My M Code so far:

let
    Source = #table({"Date", "Booking Text", "Transaction text", "Account", "Value", "Attribute", "Index", "All Elements Index", "Matching Elements Index"}, {{#date(2021,10,6), "ACD", "Transaction A", "Debit", "AccountA", 120, 9, {7, 8, 9} , {7,9}}, {#date(2021,10,6), "ACD", "Transaction A", "Debit", "AccountA", 120, 3376, {7, 8, 9} , {7,9}}, {#date(2021,10,6), "ACD", "Transaction A", "Debit", "AccountA", 50, 30415, {7, 8, 9} , null}, {#date(2021,10,6), "ACD", "Transaction A", "Debit", "AccountA", 120, 40551, {7, 8, 9} , {7,9}
}}),

#"Changed_Type1" = Table.TransformColumnTypes(Source,{{"Booking Text", type text}, {"Transaction text", type text}, {"Account", type text}, {"Attribute", type number}, {"Date", type date}, {"Value", type text}, {"Index", Int64.Type}}),

s = #"Changed_Type1",

lst = s[Matching Elements Index],
    new_col = 
        List.Accumulate(
            lst,
            {},
            (s, c) => s & {List.Difference(c ?? {null}, List.RemoveNulls(s)){0} ? }
        ),
    result = Table.FromColumns( Table.ToColumns(s) & {new_col}, Table.ColumnNames(s) & {"Desired Result Custom Function"} ),
    #"Changed Type" = Table.TransformColumnTypes(result,{{"Desired Result Custom Function", Int64.Type}})

    in 
    #"Changed Type"