Forum Discussion

lionelv's avatar
lionelv
Frequent Visitor
3 years ago

Increase from the previous month

Hi,

I try to increase the column Amount from the previous month with an Increase factor. In Excel, it’s super easy to do. In D8 you write: “=D7*C8” and you drag down the formula. But how to do that with PowerQuery?

I see two sub-problems (but maybe I’m just taking the problem on the wrong side):

  • How to merge Historical data and New Measure?
  • How to use previous month as input to increase current month? And so on?

 

SourceMonthIncreaseAmount
Histo2015-01-011.009 $        327.46
Histo2015-01-021.003 $        328.44
Histo2015-01-031.002 $        329.10
Histo2015-01-041.005 $        330.75
Histo2015-01-051.012 $        334.72
Histo2015-01-061.007 $        337.06
New measure2015-01-071.001 ??? 
New measure2015-01-081.002 ??? 
New measure2015-01-091.005 
New measure2015-01-101.013 
New measure2015-01-111.014 
New measure2015-01-121.011 

 

 

 

Thank for your help

 

Tutorial Requests

3 Replies

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    hi lionelv,

     

    Try this:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZIxCsMwDEWvYjynQrLkOL5Bp14gZOjgoUMpNC29Ts/SkzUlGlyIHGwwhvck9O1x9MfL/Lj5zgekeEBa9nIhQMzL6T7v/+U4JJDe+anbUIOqbKgDiBgqqxoMNQOhoYqqcVtlhBQNNa4qGV1ZIAVD7bVrMtQEqDGdystdy3l+3ktdIGmBX94tbqiSaXG5iqHBEerMvMPpPyDZ4fTRaZ1j+gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source = _t, Month = _t, Increase = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Month", type date}, {"Increase", type number}, {"Amount", type number}}),
    #"Added Index_0..n" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1_1...n" = Table.AddIndexColumn(#"Added Index_0..n", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1_1...n", {"Index"}, #"Added Index1_1...n", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded table to get previous month amount" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Amount"}, {"Added Index1.Amount"}),
    #"Final Amount( prev month $ * increase)" = Table.AddColumn(#"Expanded table to get previous month amount", "Final Amount( prev month $ * increase)", each [Added Index1.Amount]*[Increase]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Final Amount( prev month $ * increase)",{{"Final Amount( prev month $ * increase)", type number}})
    in
    #"Changed Type1"Appreciate a thumbs up if this was helpful.

     

     

     

    _____________

     

     

    steps taken.

     

    1. create an index column from 0 (col1).

    2. create an index column from 1 (col2).

    3. left join/ self merge table on index column (col1 on col2).

    4. expand the table for previous month's amount.

    5. create custom column to calculate previous month's amount * increase for current month.

     

     

    Please accept this as a solution if the question is solved.