Aging

[amhaugan]

Hi,
I have a dataset with the Aging group of trade recivevables of "1-30", "31-60", "not due", "61-90" and > 90 days. The company wants to show receivables in "1-14" and "15-30" days as well. Some of the internal group companies doesnt have their aging set up like this, so for simplicity they have just taken "1-30" receivables divided by two, and added 50% in 1-14 and 50% in 15-30.

I've managed to use List.Replace in order to create duplicate rows for this. The problem is now i have duplicates where i want to change only one of two names.

I want to replace Aging group duplicate names from 1-30 to respectively 1-14 and 15-30

[v-zhangti]

Hi, @amhaugan 

You can try the following methods. In the Power Query, add index column:

Column:

New Aging group = 
IF ( [Aging group] = "1-30"
        && [Index] = CALCULATE ( MIN ( 'Table'[Index] ),
                ALLEXCEPT ( 'Table', 'Table'[Aging group], 'Table'[Customer] )
            ),
    "1-15",
    [Aging group]
)

New Value = 
Var _N1=[Original Value]
Var _N2=CALCULATE(COUNT('Table'[Aging group]),FILTER('Table',[Customer]=EARLIER('Table'[Customer])&&[Aging group]="1-30"))
Return
DIVIDE(_N1,_N2)

Table:

New Table = SUMMARIZE('Table','Table'[Source],'Table'[Customer],'Table'[New Aging group],'Table'[New Value])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Martin_D · Accepted solution]

This is rather a question for the Power Query discussion.

Maybe not the dream solution, but easy to explain: This solution works as long as the dublicate rows are also subsequent rows.

Add an index column

Add a custom column:

- for the duplicate rows (conditions: Customer A or B and Aging Group 1-30), write into the custom column 1-14 if index is even (Number.IsEven([Index]))) otherwise 15-30

- for all other rows, just copy the value of Aging Group into the custom column

There you are. Finally remove the Index column and the original Aging group column and rename the new custom column.

For sure there are nicer solutions, but without having the file, this is an easy to describe solution.