Forum Discussion

JP8991's avatar
JP8991
Kudo Commander
6 years ago
Solved

Fill Between Date Points

Hello, 

The below table is filtered on one CentreCode (for ease of explanation) and shows some data.

The below table illustrates what I am after, ideally I would do a transformation on Supply Licensed Places to do away with the need for an extra Cumulative column. The blue highlights are the lines taken from the table above. As you will notice the missing dates between each of the months have now been filled in.

Thanks,

Need Help
  • dax's avatar
    dax
    6 years ago

    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 Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Inventing data is hard. If you had your data pivoted and actually had all of the dates you could use Fill Down. ImkeF ?

    • ImkeF's avatar
      ImkeF
      Community Champion
      6 years ago

      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/ 

      • JP8991's avatar
        JP8991
        Kudo Commander
        6 years ago

        ImkeF 

         

        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?