Display shift schedules in calendar

[Anonymous]

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 there is an overlap in the dates for the same name, it does not give the correct values. The final result should be a table with 3 columns: Date, Name, Value. The Value is either 1 or 0. It should be 1 when the date falls within the range specified in the schedule, otherwise it should be 0.

Name	From	Until
Kacy Jones	04/03/2022	22/03/2022
Kade Kennedy	06/03/2022	12/03/2022
Cristian Thompson	09/03/2022	30/03/2022
Kade Kennedy	06/03/2022	13/03/2022
Damian Mclellan	09/03/2022	30/03/2022
Damian Mclellan	14/03/2022	30/03/2022
Cristian Thompson	18/03/2022	18/03/2022
Ishan Rigby	14/03/2022	20/03/2022
Jimmie Harding	09/03/2022	30/03/2022
Brax Oneill	28/03/2022	08/04/2022
Kacy Jones	23/03/2022	30/03/2022
Bear Barry	17/03/2022	27/03/2022
Jorgie Galloway	21/03/2022	25/03/2022
Farah Lyon	01/04/2022	21/04/2022
Salman Goodman	28/03/2022	15/04/2022
Vincenzo Murray	30/03/2022	20/04/2022
Jorgie Galloway	12/04/2022	03/05/2022
Kade Kennedy	10/04/2022	16/04/2022
Kacy Jones	18/04/2022	27/04/2022

In red below do not give the correct values and therefore do not show correctly or at all in the calendar visualization.

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}}),
        // create lists with dates 1 or 0 corresponding to each Name/From/Until
    allDates = List.Dates(Date.AddMonths(Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())),-1), Number.From(Date.EndOfMonth(DateTime.FixedLocalNow()))- Number.From(Date.AddMonths(Date.StartOfMonth(DateTime.FixedLocalNow()),-1)) ,#duration(1,0,0,0)),
    textDates = List.Transform(allDates, each Text.From(_)),
    dateCols = List.Generate(
            ()=>[d=List.Transform(allDates, 
                        (L)=> if L >=#"Changed Type"[From]{0} and L <=#"Changed Type"[Until]{0} then 1 else 0),
                        idx=0],
            each [idx]<Table.RowCount(#"Changed Type"),
            each [d=List.Transform(allDates, (L)=>
                        if L >=#"Changed Type"[From]{[idx]+1} and L <= #"Changed Type"[Until]{[idx]+1} then 1 else 0),
                        idx=[idx]+1],
            each Table.ToRecords(#"Changed Type"){[idx]} & Record.FromList([d], textDates)),
    dateTable = Table.FromRecords(dateCols),
    #"Removed Columns1" = Table.RemoveColumns(dateTable,{"From", "Until"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Name"}, {
        {"total row", (t)=> let 
            colNames = Table.ColumnNames(t),
            sumDates = List.Generate(
                ()=>[s=t[Name]{0}, idx=0],
                each [idx] < List.Count(colNames),
                each [s = List.Product(Table.Column(t,colNames{[idx]+1})), idx=[idx]+1],
                each [s])
            in sumDates, type list}
        }),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Name"}),
    colNames2 = List.Accumulate(#"Removed Columns2"[total row],{"Date"},(state,current)=>
        state & List.Range(current,0,1)),
    data = List.Accumulate(#"Removed Columns2"[total row],{},(state, current)=>state & {List.RemoveFirstN(current,1)}),
    results = Table.FromColumns({allDates} & data, colNames2),
    typeIt = Table.TransformColumnTypes(results, {{"Date", type date}} &
       List.Transform(List.RemoveFirstN(Table.ColumnNames(results),1), each {_, Int64.Type})
),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(typeIt, {"Date"}, "Name", "Value")
in
    #"Unpivoted Other Columns"

I hope someone can help troubleshoot this problem or provide a better alternative solution. Any help is much appreciated! 

Here is the download link to my PBIX file (click the download icon at the top-right corner of the window): https://drive.google.com/file/d/15bEGr3tmVlRrSCb3LSnAn-KJgCi8J39c/view?usp=sharing

[Vijay_A_Verma · Accepted solution]

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"

[Anonymous]

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]

[Anonymous]

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!