Forum Discussion
Solved
Calculation with previous month's value
I have excel calculation that I would like to replicate in Power Query. Is this possible in Power Query?
kangx322 try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSUjC6VYHSQxI6CYOaqQMUiZGaqYCVDMxABVzBQoZogiZGgGEgJqjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}), Value = #"Changed Type"[Value], Loop = List.Generate( ()=>[i=0,j=Value{i},k=j], each [i]<List.Count(Value), each [i=[i]+1, j=Value{i}, k=[k]*3+j*0.5], each[k] ), Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type")&{Loop},List.Combine({Table.ColumnNames(#"Changed Type"),{"cumulativeValue"}})) in Custom1
kangx322 try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ1ACElHSUjC6VYHSQxI6CYOaqQMUiZGaqYCVDMxABVzBQoZogiZGgGEgJqjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
Value = #"Changed Type"[Value],
Loop = List.Generate(
()=>[i=0,j=Value{i},k=j],
each [i]<List.Count(Value),
each [i=[i]+1, j=Value{i}, k=[k]*3+j*0.5],
each[k]
),
Custom1 = Table.FromColumns(Table.ToColumns(#"Changed Type")&{Loop},List.Combine({Table.ColumnNames(#"Changed Type"),{"cumulativeValue"}}))
in
Custom1
Thank you.
Could you explain the code little bit?
What if I change my original column name to "Current"? which part of the code would change?Also, would this be only way? I am little worried about the performance as my real data is little big
