How to create a task calendar in Power BI

[iKettle]

Hi Guys, I have a sharepoint list, with the following data;

I am trying to figure out how to transform this data above so I can filter by week number/date or start of week date. I have created a date table and included start of week date and week number, but I am not sure how to link this up to the tasks in the above data, os to filter by week number etc.... Please provide step by step instruction if possible 🙂

[iKettle]

Thank you so much. It worked great, just needed to understand how to work with that data. 

[collinsg · Accepted solution]

Good day @iKettle ,

To create a list of tasks (where a task is denoted by an "x" in your example),

1. Select columns "Title" through to "Process.lookupValue".
2. On the "Transform" tab choose "Unpivot Columns", "Unpivot Other Columns".
3. Filter the newly created "Value" column for rows containing an asterisk.
4. Change the name and type of the newly created "Attribute" column to "Date" giving it type "date".
5. Load the table.
6. Make a 1 to many relation between your calendar table's date column and the table's date column.

A filter on the calendar table will then propagate to the this table. Here is example of the query code with an abbreviated form of your data.

Hope this helps.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVG7DoMwDPyViJl+BDDQBSl9bIjBtEaymjoVyQB/X6dpBXQCJEe6WL4761zXSXXJVEUPTNLkpArLvrdG8Bc5BXyX9vNlCPgWpqRKY1sw6niO36madCGoezzkPWEXODrO5MjYkXcCh9gZ1pPDIsgOPFne4V14bGEy3WBc2ZYM+XFJjm8F/QoG2e/YWMMoVzB/zFWeWoKSmFTmHH7i3pz2T2F2splA0Gje", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, Task = _t, #"Responsible Party" = _t, #"Notes/Comments" = _t, Process.lookupValue = _t, #"29/12/2024" = _t, #"05/01/2025" = _t, #"12/01/2025" = _t, #"19/01/2025" = _t, #"26/01/2025" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Task", type text}, {"Responsible Party", type text}, {"Notes/Comments", type text}, {"Process.lookupValue", type text}, {"29/12/2024", type text}, {"05/01/2025", type text}, {"12/01/2025", type text}, {"19/01/2025", type text}, {"26/01/2025", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Title", "Task", "Responsible Party", "Notes/Comments", "Process.lookupValue"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "x")),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
#"Changed Type1"

which gives...

from this...