Forum Discussion
Anonymous
4 years agoNot applicable
Display shift schedules in calendar
Hello, I have the following table which are the individual shift schedules and i am trying to present this in a calendar visualization. But it is only working for some of the names. Sometimes, if th...
- 4 years ago
See the working here
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJdS8MwFIb/Suj1YEnarvV2itPNIah4M3ZxbEMbyIekitZfb7JCPdF18/LAc16e87HbJRuoerK2RnTJLKHZnKZzTjn3BedjsZ8FsBZkI4wRdR/QBUJZjF462b1JMOSptfq1sybwF4hP6f+j0wi9Ah2Ct5USSsG54L80y6bpY9qsxC5lxN92rYcfZPPS/07mcfJaai0FuQFXS9OckV46+CT3RkilQhIWoL7I8N7Q7Xh6IlKAI0tw7iBaYNEiFrWu8aIrUMp+QKA5w3Qe0dfgoCV3/XBgNroNXUj0EZT2q1pZW+vDGaKpWB7Bz9JUwnxZsn137uCAphlWm50yDs84ivg+mk9+GqMIZYvp5bISD1f8gPtv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, From = _t, Until = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"From", type date}, {"Until", type date}}), MinDate = List.Min(#"Changed Type"[From]), MaxDate = List.Max(#"Changed Type"[Until]), ListOfDates = List.Dates(MinDate,Duration.Days(MaxDate-MinDate)+1,#duration(1,0,0,0)), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([From],Duration.Days([Until]-[From])+1,#duration(1,0,0,0))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "Until"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Date", each ListOfDates), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom"}), #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns1", "Date"), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Value", each 1), #"Merged Queries" = Table.NestedJoin(#"Expanded Custom.1", {"Name", "Date"}, #"Added Custom2", {"Name", "Custom"}, "Added Custom2", JoinKind.LeftOuter), #"Expanded Added Custom2" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom2", {"Value"}, {"Value"}), #"Sorted Rows" = Table.Sort(#"Expanded Added Custom2",{{"Date", Order.Ascending}, {"Name", Order.Ascending}}), #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Name", "Date"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates",null,0,Replacer.ReplaceValue,{"Value"}), #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Date", "Name", "Value"}) in #"Reordered Columns"
Vijay_A_Verma
Most Valuable Professional
4 years agoIs there a data where Until < From? Just check it by putting in a custom column following and see if you get True somewhere..(Apply a filter on True)
=[Until]<[From]
Anonymous
4 years agoNot applicable
Hi Vijay_A_Verma It is working now! You were right - one of the dates turned out to be "True". I just want to say that your solutions are clear and elegant. It's much appreciated. Thanks again!