fill-up intermittent date

shuyin

New member
Joined
Aug 11, 2016
Messages
26
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,

I have a table with a date column. The date is intermittent, and need to fill the missing date with the value of zero (0).
I am thinking that I need to list date into a separate table, and merge (full) with the product table for each product, replace null with 0.

Any thoughts about what can be done better?

Thanks,
T


Here is the example:


input:
DateProductValue
5/10/2017A1
5/15/2017A2
5/11/2017B3
5/16/2017B4

Expected output:
DateProductValue
5/10/2017A1
5/11/2017A0
5/12/2017A0
5/13/2017A0
5/14/2017A0
5/15/2017A2
5/16/2017A0
5/10/2017B0
5/11/2017B3
5/12/2017B0
5/13/2017B0
5/14/2017B0
5/15/2017B0
5/16/2017B4
 
.
.
Here is the macro code for displaying all dates :

Code:
Option Explicit


Sub ShwAllDates()
    
    Dim rng As Range
    Dim StartRng As Range
    Dim EndRng As Range
    Dim OutRng As Range
    Dim StartValue As Variant
    Dim EndValue As Variant
    Dim ColIndex As Integer
    Dim i As Long
   
    Set StartRng = Application.Selection
    Set StartRng = Application.InputBox("Start Range (single cell):", StartRng.Address, Type:=8)
    Set EndRng = Application.InputBox("End Range (single cell):", Type:=8)
    Set OutRng = Application.InputBox("Out put to (single cell):", Type:=8)
    Set OutRng = OutRng.Range("A1")
    StartValue = StartRng.Range("A1").Value
    EndValue = EndRng.Range("A1").Value
    If EndValue - StartValue <= 0 Then
        Exit Sub
        End If
        ColIndex = 0
        For i = StartValue To EndValue
            OutRng.Offset(ColIndex, 0) = i
            ColIndex = ColIndex + 1
        Next
    End Sub

It's late here and my mind is fuzzy. Maybe someone else can give you the code for transferring the Product and Value entries adjacent to the correct dates.
 

Attachments

  • Show All Dates.xlsm
    15.4 KB · Views: 5
Thanks for the Macro. However, I am looking for a solution with M.
 
see if this is what you had in mind

see if this is what you had in mind
 

Attachments

  • Dates.xlsx
    28.3 KB · Views: 8
My suggestion would be the following code:

Code:
let
    Source                  = Input,
    ProductsOnly            = Table.SelectColumns(Source,{"Product"}),
    UnuqueProducts          = Table.Distinct(ProductsOnly),
    AddedDateNumbers        = Table.AddColumn(UnuqueProducts, "Date", each {Number.From(List.Min(Input[Date]))..Number.From(List.Max(Input[Date]))}),
    ExpandedDateNumbers     = Table.ExpandListColumn(AddedDateNumbers, "Date"),
    DatesFromNumbers        = Table.TransformColumnTypes(ExpandedDateNumbers,{{"Date", type date}}),
    Reordered               = Table.ReorderColumns(DatesFromNumbers,{"Date", "Product"}),
    MergedWithInput         = Table.NestedJoin(Reordered,{"Date", "Product"},Input,{"Date", "Product"},"NewColumn",JoinKind.LeftOuter),
    ExpandedInput           = Table.ExpandTableColumn(MergedWithInput, "NewColumn", {"Value"}, {"Value"}),
    ReplacedNullsWithZeroes = Table.ReplaceValue(ExpandedInput,null,0,Replacer.ReplaceValue,{"Value"})
in
    ReplacedNullsWithZeroes
 
Thanks Marcel for the solution with M. I have another problem needs further query.
I need to do the fill down, then fill up to [Value] column within each product.

here is the code I used:
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="RD"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    ProductsOnly            = Table.SelectColumns(#"Changed Type",{"Product"}),
    UnuqueProducts          = Table.Distinct(ProductsOnly),
    AddedDateNumbers        = Table.AddColumn(UnuqueProducts, "Date", each {Number.From(List.Min(#"Changed Type"[Date]))..Number.From(List.Max(#"Changed Type"[Date]))}),
    ExpandedDateNumbers     = Table.ExpandListColumn(AddedDateNumbers, "Date"),
    DatesFromNumbers        = Table.TransformColumnTypes(ExpandedDateNumbers,{{"Date", type date}}),
    Reordered               = Table.ReorderColumns(DatesFromNumbers,{"Date", "Product"}),
    MergedWithInput         = Table.NestedJoin(Reordered,{"Date", "Product"},#"Changed Type",{"Date", "Product"},"NewColumn",JoinKind.LeftOuter),
    ExpandedInput           = Table.ExpandTableColumn(MergedWithInput, "NewColumn", {"Value"}, {"Value"}),
    #"Grouped Rows" = Table.Group(ExpandedInput, {"Product"}, {{"tab", each Table.FillDown(_,{"Value"}), type table}}),
    #"Expanded tab" = Table.ExpandTableColumn(#"Grouped Rows", "tab", {"Date", "Value"}, {"Date", "Value"}),
    #"Grouped Rows1" = Table.Group(#"Expanded tab", {"Product"}, {{"tab", each Table.FillUp(_,{"Value"}), type table}}),
    #"Expanded tab1" = Table.ExpandTableColumn(#"Grouped Rows1", "tab", {"Date", "Value"}, {"Date", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tab1",{{"Date", type date}})
in
    #"Changed Type1"

Result:

ProductDateValue
A5/10/20171
A5/11/20171
A5/12/20171
A5/13/20171
A5/14/20171
A5/15/20172
A5/16/20172
B5/10/20173
B5/11/20173
B5/12/20173
B5/13/20173
B5/14/20173
B5/15/20173
B5/16/20174

Do you have a better solution to do the transformation for Product within the table?

Thanks
 
My suggestion would be to group only once and combine the FillUp/Down:

Code:
#"Grouped Rows" = Table.Group(ExpandedInput, {"Product"}, {{"tab", each Table.FillUp(Table.FillDown(_,{"Value"}),{"Value"}), type table}}),
 
That works perfectly.
Thank you


Sent from my iPhone using Tapatalk
 
Back
Top