Forum Discussion
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...
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
Most Valuable ProfessionalSee 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"
Hi Vijay_A_Verma Thanks for your reply! I have only replaced the source by referring directly to another table already in Power Query (it has the same 3 columns but the start and end dates are as from 2021 until 2022) and i have used the exact same code as above but i am getting an error at the following step. Any idea how to fix it?
Here is the new table:
Name From Until Kacy Jones 18/11/2021 08/12/2021 Kade Kennedy 24/11/2021 16/12/2021 Cristian Thompson 20/12/2021 12/12/2021 Kade Kennedy 29/11/2021 09/12/2021 Damian Mclellan 17/12/2021 29/12/2021 Damian Mclellan 20/12/2021 26/12/2021 Cristian Thompson 25/12/2021 29/12/2021 Ishan Rigby 17/12/2021 29/12/2021 Jimmie Harding 19/12/2021 25/12/2021 Brax Oneill 26/12/2021 31/12/2021 Kacy Jones 26/12/2021 31/12/2021 Bear Barry 27/12/2021 02/01/2022 Jorgie Galloway 03/01/2022 09/01/2022 Farah Lyon 07/01/2022 26/01/2022 Salman Goodman 20/12/2021 10/01/2022 Vincenzo Murray 22/12/2021 11/01/2022 Jorgie Galloway 19/12/2021 09/01/2022 Kade Kennedy 06/01/2022 27/01/2022 Kacy Jones 10/01/2022 16/01/2022 Kacy Jones 16/01/2022 22/01/2022 Kade Kennedy 27/01/2022 16/02/2022 Cristian Thompson 23/01/2022 30/01/2022 Kade Kennedy 28/01/2022 17/02/2022 Damian Mclellan 04/02/2022 16/02/2022 Damian Mclellan 31/01/2022 06/02/2022 Cristian Thompson 26/01/2022 16/02/2022 Ishan Rigby 31/01/2022 21/02/2022 Jimmie Harding 31/01/2022 16/02/2022 Brax Oneill 27/01/2022 15/02/2022 Kacy Jones 30/11/2021 30/11/2021 Bear Barry 30/11/2021 30/11/2021 Jorgie Galloway 09/02/2022 20/02/2022 Farah Lyon 16/02/2022 09/03/2022 Salman Goodman 10/02/2022 27/02/2022 Vincenzo Murray 19/02/2022 10/03/2022 Jorgie Galloway 20/02/2022 25/02/2022 Kade Kennedy 21/02/2022 27/02/2022 Kacy Jones 21/02/2022 14/03/2022 Kacy Jones 21/02/2022 13/03/2022 Kade Kennedy 14/02/2022 20/02/2022 Cristian Thompson 04/03/2022 22/03/2022 Kade Kennedy 28/02/2022 06/03/2022 Damian Mclellan 06/03/2022 12/03/2022 Damian Mclellan 09/03/2022 30/03/2022 Cristian Thompson 06/03/2022 13/03/2022 Ishan Rigby 09/03/2022 30/03/2022 Jimmie Harding 14/03/2022 30/03/2022 Brax Oneill 11/03/2022 16/03/2022 Kacy Jones 18/03/2022 18/03/2022 Bear Barry 14/03/2022 20/03/2022 Jorgie Galloway 14/03/2022 04/04/2022 Farah Lyon 09/03/2022 30/03/2022 Salman Goodman 30/11/2021 30/11/2021 Vincenzo Murray 28/03/2022 08/04/2022 Jorgie Galloway 23/03/2022 30/03/2022 Kade Kennedy 17/03/2022 27/03/2022 Kacy Jones 21/03/2022 25/03/2022 Kacy Jones 01/04/2022 21/04/2022 Kade Kennedy 28/03/2022 15/04/2022 Cristian Thompson 30/03/2022 20/04/2022 Kade Kennedy 12/04/2022 03/05/2022 Damian Mclellan 10/04/2022 16/04/2022 Damian Mclellan 18/04/2022 27/04/2022 Cristian Thompson 01/01/2022 01/01/2022 - Vijay_A_Verma
Is 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]
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!