Forum Discussion
How to spread value between two dates ?
Hi everyone, I need some help on my Power BI. I am trying to spread the value of a project over several months, to know exactly how much I will have to produce every month. For exemple, if I...
Hi,
This is kinda complexed. You need to create more rows in powerQuery.
Create a blank Query and copy this code.
Spoiler
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY49C8IwEIb/SgkdE7mLJmQtFMXWYge30CHaDIq2Elqh/94DaSTbvR8P91rL2jA+/G3KGGfn0PuQlW7yJEr/vH98WOgsXuM8TKzjlqFWuj6Rp0TlBiFBAomdaNyyihwBOABsAH6I0qZpKTBi769rayuKd4iIUSmBpj6SjykCCYMyeaNQm8OFAom0JtZIVXMcmqs/030B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ", type text}, {"Order Date", type date}, {"Delivery", type date}, {"Amount", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","$","",Replacer.ReplaceText,{"Amount"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Amount"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"Amount"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"Amount", type number}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each Duration.Days([Delivery] - [Order Date])),
#"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Custom", each { Number.From([Order Date]) +1 ..Number.From([Delivery] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Inserted Division" = Table.AddColumn(#"Changed Type3", "Division", each [Amount] / [Subtraction], type number)
in
#"Inserted Division"
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY49C8IwEIb/SgkdE7mLJmQtFMXWYge30CHaDIq2Elqh/94DaSTbvR8P91rL2jA+/G3KGGfn0PuQlW7yJEr/vH98WOgsXuM8TKzjlqFWuj6Rp0TlBiFBAomdaNyyihwBOABsAH6I0qZpKTBi769rayuKd4iIUSmBpj6SjykCCYMyeaNQm8OFAom0JtZIVXMcmqs/030B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ", type text}, {"Order Date", type date}, {"Delivery", type date}, {"Amount", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type1","$","",Replacer.ReplaceText,{"Amount"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",","",Replacer.ReplaceText,{"Amount"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",".",",",Replacer.ReplaceText,{"Amount"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"Amount", type number}}),
#"Inserted Date Subtraction" = Table.AddColumn(#"Changed Type2", "Subtraction", each Duration.Days([Delivery] - [Order Date])),
#"Added Custom" = Table.AddColumn(#"Inserted Date Subtraction", "Custom", each { Number.From([Order Date]) +1 ..Number.From([Delivery] ) }),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
#"Inserted Division" = Table.AddColumn(#"Changed Type3", "Division", each [Amount] / [Subtraction], type number)
in
#"Inserted Division"
When you understand It you can applie it to your orginal code.
Hope this can help you.
/Adam
Hi Anonymous , Thank you very much for your answer.
I'm not sure to understand entirely your code, but i'm gonna try to apply it to my case and test it.
I can see on your screenshot it worked so it should on mine too !
I'll let you know, thanks again for your time.
Nicho