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.

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        9 years ago

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

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

      MarcelBeug Anonymous

       

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

       

       

      Best Regards!

      Dale

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        9 years ago

        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