Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I am currently setting up a forecast query for project controlling.
As you can see in the attached snip, I have "Current Budget" as initial value with Index 0.
In a new column the "Remaining Budget" should be calculated:
Remaining Budget (index) = Remaining Budget (index-1) - Expected Bill Amount (aggregated)
The first Remaining Budget for index=0 should just be the "Current Budget".
Thanks a lot in advance!
Best, Tim
Solved! Go to Solution.
Hi @Tim_Ba, there are many ways like @m_dekorte mentioned, but this one is the fastest
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC9DQAhCIDRVQy1MQh4P7MY919DuOKkAGP1PYMJcwJRK9wKIQlU0NvRDqyqxt4U7MUHksHI4PJw8h3nx+dx+ht3xqT3cLxWCT711bbwT8lAQlgb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Expected Bill Amount (aggregated)" = _t, #"Current Budget" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expected Bill Amount (aggregated)", Currency.Type}, {"Current Budget", Currency.Type}}, "sk-SK"),
GenerateBudget = [ a = List.Buffer(ChangedType[#"Expected Bill Amount (aggregated)"]),
lg = List.Generate(
()=> [ x = 0, y = ChangedType{0}[Current Budget] ],
each [x] < List.Count(a),
each [ x = [x]+1, y = [y]-a{x} ],
each [y]
)
][lg],
Merged = Table.FromColumns(Table.ToColumns(ChangedType) & {GenerateBudget}, Value.Type(ChangedType & #table(type table[Remaining Budget=Currency.Type], {{}})))
in
Merged
Hi @Tim_Ba, there are many ways like @m_dekorte mentioned, but this one is the fastest
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddC9DQAhCIDRVQy1MQh4P7MY919DuOKkAGP1PYMJcwJRK9wKIQlU0NvRDqyqxt4U7MUHksHI4PJw8h3nx+dx+ht3xqT3cLxWCT711bbwT8lAQlgb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Expected Bill Amount (aggregated)" = _t, #"Current Budget" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expected Bill Amount (aggregated)", Currency.Type}, {"Current Budget", Currency.Type}}, "sk-SK"),
GenerateBudget = [ a = List.Buffer(ChangedType[#"Expected Bill Amount (aggregated)"]),
lg = List.Generate(
()=> [ x = 0, y = ChangedType{0}[Current Budget] ],
each [x] < List.Count(a),
each [ x = [x]+1, y = [y]-a{x} ],
each [y]
)
][lg],
Merged = Table.FromColumns(Table.ToColumns(ChangedType) & {GenerateBudget}, Value.Type(ChangedType & #table(type table[Remaining Budget=Currency.Type], {{}})))
in
Merged
Hi @dufoq3, many thanks for this straightforward solution. It was quite easy to implement and understand!
Also thanks to @m_dekortefor the effort. As I am a beginner in coding for PowerQuery, I think the solution was a little too advanced for me at this point as I failed to implemented it.
Best, Tim
Hi @Tim_Ba,
There are several ways to achieve this, a straight forward method is using List.Range
let
curBudget = Source[Budget]?{0}?,
expAmount = List.Buffer( List.ReplaceValue( Source[ExpectedAmt], null, 0, Replacer.ReplaceValue )),
Source = Table.FromColumns(
{
{null} & {4,6,7,3,9,3,5,7, 8},
{100} & List.Repeat({null}, 9),
{0..9}
}, type table
[ExpectedAmt = number, Budget=number, Index=number]
),
InsertRemaining = Table.AddColumn(Source, "Remaining Budget", each curBudget - List.Sum( List.Range(expAmount, 0, [Index]+1 )))
in
InsertRemaining
From the Source table, I've extracted the starting value and stored that in the curBudget variable. And the Expected Amount column is loaded into memory, stored in the expAmount variable. As M applies null propagation to avoid errors I've replaced that value with a 0. Here's the result.
I hope this is helpful.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.