Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Isidro
Helper IV
Helper IV

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.

 

14 REPLIES 14
v-jiascu-msft
Employee
Employee

@Isidro

 

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

 Create a column based on data from other columns .jpg

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

Specializing in Power Query Formula Language (M)

@MarcelBeug @Isidro

 

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

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Specializing in Power Query Formula Language (M)

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.

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.

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

Specializing in Power Query Formula Language (M)

Hello Marcel,

 

The logic is the first 3 digits.

 

Sorry for my english.

 

Thank you very much and best regards.

 

 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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.

Specializing in Power Query Formula Language (M)

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

 

ASIENTOSUBCUENTADEBEHABERCONTRA CTA
34
211000
1000211200
346000002500 
345720000250 
342112000100 



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.

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?

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.