Forum Discussion
daanp
9 years agoFrequent Visitor
How to create rows based on dates on seperate rows?
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 ...
MarcelBeug
9 years agoCommunity Champion
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
9 years agoFrequent Visitor
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 |