Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Create a column based on data from other columns

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

ASIENTOSUBCUENTADEBEHABERCONTRA CTA
342110001000211200
342112000100 

 

Thank you very much and best regards.

 

Need Help

14 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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).

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        MarcelBeug
        Community Champion
        9 years ago

        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.