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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lionelv
Frequent Visitor

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 

 

lionelv_0-1673454377000.png

 

 

Thank for your help

 

3 REPLIES 3
adudani
Super User
Super User

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

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. 

 

lionelv_0-1673548609356.png

 

@lionelv , thanks for letting me know.

 

Maybe this is helpful: Get Value from Previous Row using Power Query - BI Gorilla

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors