Matrix

Hi Usman,

You need to create a thirt table for a calendar you can do it in Excel or follow this link to make the custom calendar in Power query.

• In Advance Mode on Power query editor create the link to the 3 tables
• On the line of the Projects do the following steps:
  • Unpivot Projects columns
  • Remove Column attibute (name of the projects)
• Merge the Employes table with the Projects table
• Expand the date table
• Add a custom column with the following code:

Occupation = if [Removed Columns.Date] = null then "Free" else "Busy"

• Pivot Column Occupation using Maximum on Advance options
• Replace Nulls by Free
• Filter out null on column Date
• Merge Dates with previous table
• Expand Table
• Replace null

M code below:

let
    Employees = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    Emp_Format = Table.TransformColumnTypes(Employees,{{"Employees", type text}}),
    Projects= Excel.CurrentWorkbook(){[Name="Projects"]}[Content],
    Format_Projects = Table.TransformColumnTypes(Projects,{{"Date", type date}, {"Project 1", type text}, {"Project 2", type text}, {"Project 3", type text}, {"Project 4", type text}}),
    Unpivot_Projects = Table.UnpivotOtherColumns(Format_Projects, {"Date"}, "Attribute", "Value"),
    Remove_Projects = Table.RemoveColumns(Unpivot_Projects,{"Attribute"}),
    Merge_Emp_Projects = Table.NestedJoin(Emp_Format,{"Employees"},Remove_Projects,{"Value"},"Removed Columns",JoinKind.LeftOuter),
    Expand_emp_projects = Table.ExpandTableColumn(Merge_Emp_Projects, "Removed Columns", {"Date"}, {"Date"}),
    Occupation = Table.AddColumn(Expand_emp_projects, "Occupation", each if [Date] = null then "Free" else "Busy" ),
    Pivot_col = Table.Pivot(Occupation, List.Distinct(Occupation[Employees]), "Employees", "Occupation", List.Max),
    Replace_Null = Table.ReplaceValue(Pivot_col,null,"Free",Replacer.ReplaceValue,{"John Peters", "Mike Dunsing", "Eric Kessler", "John Morales", "Kody Stahley", "Taran Reinert", "Tony Pedigree"}),
    Filter_null = Table.SelectRows(Replace_Null, each ([Date] <> null)),
    Dates= Excel.CurrentWorkbook(){[Name="Calendar"]}[Content],
    Dates_Format = Table.TransformColumnTypes(Dates,{{"Dates", type date}}),
    Merge_Date_Free = Table.NestedJoin(Dates_Format,{"Dates"}, Filter_null,{"Date"},"Changed Type",JoinKind.LeftOuter),
    Expand_Dates_Free = Table.ExpandTableColumn(Merge_Date_Free, "Changed Type", {"John Peters", "Mike Dunsing", "Eric Kessler", "John Morales", "Kody Stahley", "Taran Reinert", "Tony Pedigree"}, {"John Peters", "Mike Dunsing", "Eric Kessler", "John Morales", "Kody Stahley", "Taran Reinert", "Tony Pedigree"}),
    Replace_Null_free = Table.ReplaceValue(Expand_Dates_Free,null,"Free",Replacer.ReplaceValue,{"Dates", "John Peters", "Mike Dunsing", "Eric Kessler", "John Morales", "Kody Stahley", "Taran Reinert", "Tony Pedigree"})





in
    Replace_Null_free

See attach a copy of the excel file (it's a we tranfer link so will only last 7 days).

See the result below:

Regards,

MFelix