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. ASIENTO SUBCUENTA DEBE HABER CONTRA CTA 34 211000 100 0 211200 34 ...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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
      Icon for Community Champion rankCommunity 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).

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.