Forum Discussion
Fill Between Date Points
Hi JP8991,
You could refer to my sample to see whether it work or not. Use Query 1 , Query2, Merge 1 and Query3.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Inventing data is hard. If you had your data pivoted and actually had all of the dates you could use Fill Down. ImkeF ?
Hi
please paste this code into the advanced editor and follow the steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByJDMyUdJUNjY3OlWJ1oJUMjuJiJAVjEwBIsYg5SZQAVMgYLWYCETCyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
AddNextDate = Table.AddColumn(#"Added Index", "NextDate", each #"Added Index"{[Index]+1}[Date], type date),
#"AddMonth#" = Table.AddColumn(AddNextDate, "Months#", each Number.Round ( Number.From ( [NextDate] - [Date]) / 30 ) ),
AddMonths = Table.AddColumn(#"AddMonth#", "Months", each {0..[#"Months#"]-1}),
#"Replaced Errors" = Table.ReplaceErrorValues(AddMonths, {{"Months", null}}),
#"Expanded Months" = Table.ExpandListColumn(#"Replaced Errors", "Months"),
newDate = Table.AddColumn(#"Expanded Months", "newDate", each try Date.AddMonths([Date], [Months]) otherwise [Date]),
newValue = Table.AddColumn(newDate, "ValueNew", each if [newDate] = [Date] then [Value] else 0),
#"Removed Other Columns" = Table.SelectColumns(newValue,{"newDate", "Value", "Date", "ValueNew"})
in
#"Removed Other Columns"
The formula to fetch the value from the next row is simple, but not the fastest. So if you run into performance problems, you should use this approach instead: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
Thank you so much for this, excellent code.
Just a few questions/tweaks.
- Would it be possible to have the newDate column go down to the current month i.e. 01/11/2019?
- Would it be possible to have the Value column cumulative so that when the 40, 100 and 148 appear the repeated values are 1377, 1477 and 1625 respectively?
- In my example I have filtered on one centre code, as you have used an index column how would I replicate this outcome when the rest of the data set is used as I am guessing the index would need to start counting from zero for each Centre Code?
Hi JP8991,
You could refer to my sample to see whether it work or not. Use Query 1 , Query2, Merge 1 and Query3.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
