Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
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
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"
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 |
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |