Create a column based on data from other columns

[Anonymous]

I would like to create a column in a query where on the same line as 211000 the subaccount 211200 is carried.

ASIENTO	SUBCUENTA	DEBE	HABER	CONTRA CTA
34	211000	100	0	211200
34	211200	0	100

Thank you very much and best regards.

[v-jiascu-msft]

@Anonymous

Hi,

If your table is as sample as the data you post here, you can try this formula. You should consider the suggestion from other people. It’s complicated in the production. It also has some by-products.

CONTRA CTA =
IF (
    RIGHT ( 'Table1'[SUBCUENTA], 3 ) = "000",
    CALCULATE (
        MAX ( Table1[SUBCUENTA] ),
        FILTER (
            'Table1',
            'Table1'[ASIENTO] = EARLIER ( Table1[ASIENTO] )
                && LEFT ( 'Table1'[SUBCUENTA], 3 ) = LEFT ( EARLIER ( Table1[SUBCUENTA] ), 3 )
        )
    ),
    BLANK ()
)

 

Best Regards!

Dale

[Anonymous]

Hello Dale,

Thank you very much for the information.  Sometimes the account may be several rows below. In any case, your contribution is very valuable.

Best Regards.

[MarcelBeug]

Didn't you want a solution in Power Query?

Are you confirming now that the accounts are related based on the first 3 digits as I suggested earlier and as included in the DAX solution from @v-jiascu-msft ?

I'm trying everything to get the required information from you and now all of a sudden all credits seem to go to someone else.

(Don't misunderstand me: the DAX solution may be just what you are looking for, but I'm not too pleased with the way you are handling this topic @Anonymous).

[v-jiascu-msft]

@MarcelBeug @Anonymous

I am sorry for this. I should have waited longer. @MarcelBeug had followed much. Thank you sincerely!

Best Regards!

Dale

[MarcelBeug]

This would be my suggestion for the solution in Power Query:

let
    Source = Table1,

    // Add temporary Index so only rows with a higher Index can be selected later on:
    Indexed = Table.AddIndexColumn(Source, "Index", 0, 1),

    // Add temporary column with the first 3 digits of each SUBCUENTA:
    AddedFirst3Digits = Table.AddColumn(Indexed, "First3Digits", each Number.IntegerDivide([SUBCUENTA],1000)),

    // Select the field SUBCUENTA from the first record with the same ASIENTO and the same First3Digits and higher Index value,
    // or from default value, record [SUBCUENTA = null], if there are no matches.
    AddedCONTRA_CTA = Table.AddColumn(AddedFirst3Digits,
                                      "CONTRA CTA",
                                      (x) => Table.First(Table.SelectRows(AddedFirst3Digits, 
                                                                    each [ASIENTO]      = x[ASIENTO]
                                                                     and [First3Digits] = x[First3Digits]
                                                                     and [Index]        > x[Index]),
                                                         [SUBCUENTA = null])[SUBCUENTA]),

    // Remove temporary columns
    RemovedColumns = Table.RemoveColumns(AddedCONTRA_CTA,{"Index", "First3Digits"})
in
    RemovedColumns

No worries @v-jiascu-msft

[Anonymous]

Hello Marcel,

I'm going to try the formula and tell you.

Your work is very valuable. I am so grateful that you share it.

Thank you very much and best regards.

[Anonymous]

Sorry for the inconvenience. It was not my intention. Thank you very much for your effort and your contributions.

For me the contribution of @ v-jiascu-msft and yours @MarcelBeug is very valuable.

Best regards.

[MarcelBeug]

OK, fair enough, but I still don't know the required logic.

If your issue is solved, then fine with me (please mark the appropriate post as solution).

If you need further follow up in Power Query, then please provide the required details:

What is the logic to select the required account to move over from the other row: is it the first 3 digits or anything else (PLEASE PLEASE PLEASE SPECIFY!!!!!!!).

[Anonymous]

Hello Marcel,

The logic is the first 3 digits.

Sorry for my english.

Thank you very much and best regards.

[Greg_Deckler]

Do you always want to carry that to the next line or only in specific circumstances?

[Anonymous]

Depends on the circumstances. There may also be several lines below. The subaccount I want to carry must have the same number as the column Asiento.

Thank you very much and best regards.

[MarcelBeug]

Please be more specific.

Do you always have pairs of 2 rows with the same number? If there are more rows with the same number, which one(s) must be carried over to which other row(s)? If multiple numbers must be carried over to the same row, do you want to have them comma separated?

In which case do you want the number from one row to be carried over to the other row?

In your examples, the number from the second row is carried over to the first row, but the number from the first row is not carried over to the second row.

[Anonymous]

Hello,

What I want is to take me from the SUBCUENTA column a certain account, for example, 211200, to the CONTRA CTA column. What they have in common is the number of the ASIENTO column. A ASIENTO number can be composed of different rows. In the following example, the account is three rows below, but may be in the bottom row or two lower row, ...

ASIENTO	SUBCUENTA	DEBE	HABER	CONTRA CTA
34	211000	100	0	211200
34	600000	250	0
34	572000	0	250
34	211200	0	100

I only want to carry the number of the subaccount marked red in the column against cta. The subaccount numbers you carry belong to a certain group.

Thank you very much and best regards.

[MarcelBeug]

It is not possible to select on font color. So how do you know which number to carry over?

Why shouldn't 600000 or 572000 be carried over?

Maybe something like the first 3 digits are the same?