How to create rows based on dates on seperate rows?

[daanp]

Hi all,

I'm trying to create an inventory table. 

Currently i have this:

user          date          Key

John	1/2/2016	5
Jane	3/2/2016	5
John	6/2/2016	5
Mike	8/2/2016	5

What i need is this:

user         date           Key

John	1/2/2016	5
John	2/2/2016	5
Jane	3/2/2016	5
Jane	4/2/2016	5
Jane	5/2/2016	5
John	6/2/2016	5
John	7/2/2016	5
Mike	8/2/2016	5

so i need to add rows with the missing dates and fill down the Users and Key. 

any advice on how i can accomplish this with power query?

[TomMartens]

Hey,

the little secret is M function List.Dates() ...

Here you will find a little pbix file

In the Query Editor I added a custom column using this function

List.Dates([Date],[Index], #duration(1,0,0,0))

After expanding the column to new rows you will receive what you are looking for

Hope this gets you started

Regards

[MarcelBeug]

It requires some coding (steps Custom1 and Custom2 below).

Transform the dates to numbers, and make a list from the minimum value to the maximum value.

Turn this into a table.

Now you can use standard menu-options:

Transform numbers back to dates.

Merge with the original table and fill down the missing values.

let
    Source = Table1,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", Int64.Type}}),
    Custom1 = {List.Min(#"Changed Type"[Date])..List.Max(#"Changed Type"[Date])},
    Custom2 = Table.FromColumns({Custom1},{"date"}),
    #"Changed Type1" = Table.TransformColumnTypes(Custom2,{{"date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1",{"date"},Table1,{"Date"},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"user", "Key"}, {"user", "Key"}),
    #"Filled Down" = Table.FillDown(#"Expanded Table1",{"user", "Key"})
in
    #"Filled Down"

[daanp]

Hi Marcel,

Thanks for your quick reply.

Your solution works for the dataset i provided.

However, i forgot to mention something. The dataset contains different keys with the same dates:

Jane	3-2-2016	5
John	6-2-2016	5
Mike	8-2-2016	5
John	1-2-2016	6
Jane	3-2-2016	6
Mike	8-2-2016	6

and i need:

Jane	3-2-2016	5
Jane	4-2-2016	5
Jane	5-2-2016	5
John	6-2-2016	5
John	7-2-2016	5
Mike	8-2-2016	5
John	1-2-2016	6
John	2-2-2016	6
Jane	3-2-2016	6
Jane	4-2-2016	6
Jane	5-2-2016	6
Jane	6-2-2016	6
Jane	7-2-2016	6
Mike	8-2-2016	6