Forum Discussion
create a special date table
Hi everyone
I have a table of items with a reference date. For example
Item Refdate
ItemA 2017/01/01
ItemB 2017/06/01
ItemC 2018/02/26
This table is frequently updated with new items
I need to create a new unique table like this :
Item Date
ItemA 2017/01/01 (reference date for this item)
Item A 2017/01/02 (next day)
... (and so on)
ItemA today's date
ItemB 2017/06/01
ItemB 2017/06/02
...
ItemB today's date
ItemC 2018/02/26
ItemC 2018/02/27
...
ItemC today's date
Obviously, this table must be updated every day
-to add the new today's date at each item
-to add new items
I trie to figure out something with calendarauto but I don't know how to add the Item name and loop on all items
Thank you for your help
Regards
Marc
Hi marcp,
If there are a quite a handful of items, I can imagine how tedious it would be to achieve your requirement using DAX so I suggest doing it in M.
I was able to achieve what's on the screenshot above using the M script below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjA0V4rViVZyAgqYIQs4AwWM9I3MQCIWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Start Date" = _t]), //returns Today's date based on server time or device's time #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Start Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "List of Dates", each List.Dates([Start Date],Number.From( DateTime.Date(DateTime.LocalNow())-[Start Date]), #duration(1, 0, 0, 0)), type list), #"Expanded List of Dates" = Table.ExpandListColumn(#"Added Custom", "List of Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded List of Dates",{{"List of Dates", type date}}) in #"Changed Type1"
2 Replies
Hi marcp,
If there are a quite a handful of items, I can imagine how tedious it would be to achieve your requirement using DAX so I suggest doing it in M.
I was able to achieve what's on the screenshot above using the M script below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjA0V4rViVZyAgqYIQs4AwWM9I3MQCIWSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, #"Start Date" = _t]), //returns Today's date based on server time or device's time #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Start Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "List of Dates", each List.Dates([Start Date],Number.From( DateTime.Date(DateTime.LocalNow())-[Start Date]), #duration(1, 0, 0, 0)), type list), #"Expanded List of Dates" = Table.ExpandListColumn(#"Added Custom", "List of Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded List of Dates",{{"List of Dates", type date}}) in #"Changed Type1"
