Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • Vijay_A_Verma's avatar
    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"