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
    Icon for Memorable Member rankMemorable 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.

    • lionelv's avatar
      lionelv
      Frequent Visitor
      3 years ago

      Hi adudani,

       

      Thanks, but it add only one new value and in an other column. Consequantly, it stop there and does'nt continu untile the end of the list. As far as I read sefl referencing column are not allowed in PowerQuery. I still try to find a solution.