fill-up intermittent date

[shuyin]

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:

Date	Product	Value
5/10/2017	A	1
5/15/2017	A	2
5/11/2017	B	3
5/16/2017	B	4

Expected output:

Date	Product	Value
5/10/2017	A	1
5/11/2017	A	0
5/12/2017	A	0
5/13/2017	A	0
5/14/2017	A	0
5/15/2017	A	2
5/16/2017	A	0
5/10/2017	B	0
5/11/2017	B	3
5/12/2017	B	0
5/13/2017	B	0
5/14/2017	B	0
5/15/2017	B	0
5/16/2017	B	4

 

[Logit]

.
.
Here is the macro code for displaying all dates :

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.

 

[shuyin]

Thanks for the Macro. However, I am looking for a solution with M.

 

[Ed Kelly]

see if this is what you had in mind

see if this is what you had in mind

 

[MarcelBeug]

My suggestion would be the following 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

 

[shuyin]

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:

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:

Product	Date	Value
A	5/10/2017	1
A	5/11/2017	1
A	5/12/2017	1
A	5/13/2017	1
A	5/14/2017	1
A	5/15/2017	2
A	5/16/2017	2
B	5/10/2017	3
B	5/11/2017	3
B	5/12/2017	3
B	5/13/2017	3
B	5/14/2017	3
B	5/15/2017	3
B	5/16/2017	4

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

Thanks

 

[MarcelBeug]

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

#"Grouped Rows" = Table.Group(ExpandedInput, {"Product"}, {{"tab", each Table.FillUp(Table.FillDown(_,{"Value"}),{"Value"}), type table}}),

 

[shuyin]

That works perfectly.
Thank you


Sent from my iPhone using Tapatalk