Get the first element from list which is not taken

[Friedrich902]

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 know how the custom function in power query hat to be written?

In the sample data you can see a list where the first element should be taken but not twice.

Date | Booking Text | Transaction text | Account | Attribute | Value | IndexMatching Elements Index | Desired Result Custom Function

06.10.2021 00:00	ACD	Transaction	A	Debit	120	9	{7,9}	7
06.10.2021 00:00	ACD	Transaction	B	Debit	120	3376	{7,9}	9
06.10.2021 00:00	ACD	Transaction	C	Debit	50	30415		null
06.10.2021 00:00	ACD	Transaction	D	Debit	120	40551	{7,9}	null

Thank you in advance.

[Friedrich902]

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?

Date	Booking Text	Transaction text	Account	Value	Attribute	Index	All Elements Index	Matching Elements Index	Result Custom Function 1	Desired Result Custom Function 2
06.10.2021	ACD	Transaction A	Debit	AccountA	120	9	{7,8,9}	{7,9}	7	null
06.10.2021	ACD	Transaction A	Debit	AccountA	120	3376	{7,8,9}	{7,9}	9	null
06.10.2021	ACD	Transaction A	Debit	AccountA	50	30415	{7,8,9}			8
06.10.2021	ACD	Transaction A	Debit	AccountA	120	40551	{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"

[AlienSx · Accepted solution]

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"} )