How to transfer amount into different columns by using "Aging days"

[MahamoodPBI]

Hi Pbi Experts,

I have date and amount column and Aging Days  column 

by using aging days i need to to transfer "Payment Amount in USD" Values in to new columns (<30days, 31 to 60 days , 61 to 90 days,

> 90 days Columns)

need to send Payment Amount in USD Values by using "AP Aging Days" values into "<30days, 31 to 60 days , 61 to 90 days,> 90 days Columns)"

output:

please suggest the above requiremet 

Thanks,

[edhans · Accepted solution]

This code will do it. Just add new columns with these formulas. For example, the 31-60 day column:

End table:

Full sample code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNwIhIyMlHSVDQ30jGNvYxMBAKVYHXYWRviGMbWoAVWGmb2gKE7REyBtikwdaAVdgDlYQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Clearing Date" = _t, Payment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Clearing Date", type date}, {"Payment", Currency.Type}}),
    #"Added Aging" = Table.AddColumn(#"Changed Type", "Aging", each Duration.Days([Clearing Date] - [Posting Date]), Int64.Type),
    #"Added < 30" = Table.AddColumn(#"Added Aging", "< 30 Days", each if [Aging]<30 then [Payment] else null, Currency.Type),
    #"Added 31 to 60" = Table.AddColumn(#"Added < 30", "31 to 60 Days", each if [Aging] > 29 and [Aging] <= 60 then [Payment] else null, Currency.Type),
    #"Added 61 to 90" = Table.AddColumn(#"Added 31 to 60", "61 to 90 Days", each if [Aging] > 60 and [Aging] <= 90 then [Payment] else null, Currency.Type),
    #"Added > 90" = Table.AddColumn(#"Added 61 to 90", "> 90 Days", each if [Aging] > 90 then [Payment] else null, Currency.Type)
in
    #"Added > 90"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

NOTE: This is not the best way to bring this into Power BI, but you didn't say what your goal was. If you are bringing this into Power BI, your final step should be to unpivot the aging columns as follows:

But if that is the goal, than just add one columns as follows:

if [Aging] <30 then "< 30"
else if [Aging] > 29 and [Aging] <= 60 then "31 to 60"
else if [Aging] > 60 and [Aging] <= 90 then "61 to 90"
else "> 90"

again, full code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNwIhIyMlHSVDQ30jGNvYxMBAKVYHXYWRviGMbWoAVWGmb2gKE7REyBtikwdaAVdgDlYQCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Clearing Date" = _t, Payment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Posting Date", type date}, {"Clearing Date", type date}, {"Payment", Currency.Type}}),
    #"Added Aging" = Table.AddColumn(#"Changed Type", "Aging", each Duration.Days([Clearing Date] - [Posting Date]), Int64.Type),
    #"Added Aging Bracket" = 
        Table.AddColumn(
            #"Added Aging", 
            "Aging Bracket", 
            each 
                if [Aging] <30 then "< 30"
                else if [Aging] > 29 and [Aging] <= 60 then "31 to 60"
                else if [Aging] > 60 and [Aging] <= 90 then "61 to 90"
                else "> 90",
            type text)
in
    #"Added Aging Bracket"