Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |