Forum Discussion
Display shift schedules in calendar
- 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"
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"
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_Verma4 years agoMost Valuable Professional
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]
- Anonymous4 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!