Matrix

[Usman]

Hi, how we can matrix in Power query? I want dates from a table in rows and all the employees from another table in columns. Basically I want see who is assigned a job and who is free. The table which has dates contains the employees who has a job. Problem is to know about the employees who are free at a certain date.

[Usman]

It's an Excel PQ, so not the visual is appropriate. I have one table which only contains employee names. And other table in which I've dates in the rows and projects on the columns. When I have a project on certain date I assign an employee to that project. I can get the employees who has a job currently but having difficulty to get employees who are free.

[MFelix]

Hi @Usman,

Did you try my solution the only change is that were you have a JoinKind.LeftOuter you need to do a JoinKind.FullOuter this way merge full information from both tables.

In the end after having pivoting just filter out nulls on the date column.

Regards,
MFelix

[TomMartens]

Hey,

I'm aware that you asked for a Power Query solution providing a matrix, but I couldn't resist to use basic Power BI, by using a Date Table that is related to a HasAJobTable.

Here you can find a simple Power BI pbix file.

Here is a screenshot from the table relations

Here is a screenshot from the table "EmployeeHasJob", I just created a "Calculated Column" that just contains the value 1.

The result, using the matrix visual and conditional formatting, of course this will also work with "Show items with no data" enabled on  the date column

Maybe you find this approach helpful

Tom

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.

[Usman]

Here is the input table which contain dates in rows, jobs in columns and employees assigned to them. employees table only has employees names. expected solution is attached in the pivot table. the only issue is I cannot create a slicer of "Busy, Free" so that I can filter out employees who are free and assign a job to them.

[MFelix · Accepted solution]

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

[Usman]

Thank you so much. it worked.

[MFelix]

Hi @Usman,

Just be carefull if the emplooye table changes there are some steps that may be broken since the name of the columns is based on user name.

Regards,

MFelix

[MFelix]

Hi @Usman,

Although I don't know how you data is setup I assume that both tables are in the format of:

Table Dates: Date

Table Emp: Date ; Employees

• Make a merge of both tables joining by date
• Then expand the table employee
• Replace null by DELETE on colum employee
• Duplicate column employee
• Then pivot by employe copy column with the values from employee
• Delete column named DELETE
• Replace 0 by null

See M language below and result:

let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    Format_dates = Table.TransformColumnTypes(Source,{{"Dates", type date}}),
    Employees = Excel.CurrentWorkbook(){[Name="Employees"]}[Content],
    Format_Emp = Table.TransformColumnTypes(Employees,{{"Employee", type text}, {"Date", type date}}),
    Merge = Table.NestedJoin(Format_dates,{"Dates"},Format_Emp,{"Date"},"Changed Type1",JoinKind.LeftOuter),
    Expand_Emp = Table.ExpandTableColumn(Merge, "Changed Type1", {"Employee"}, {"Employee"}),
    Repace_null = Table.ReplaceValue(Expand_Emp,null,"DELETE",Replacer.ReplaceValue,{"Employee"}),
    Duplicate_Emp = Table.AddColumn(Repace_null, "Employee - Copy", each [Employee], type text),
    Pivot = Table.Pivot(Duplicate_Emp, List.Distinct(Duplicate_Emp[#"Employee - Copy"]), "Employee - Copy", "Employee", List.Count),
    Remove_additional = Table.RemoveColumns(Pivot,{"DELETE"}),
    Replace_zero = Table.ReplaceValue(Remove_additional,0,null,Replacer.ReplaceValue,{"A", "B", "D", "C"})
in
    Replace_zero

Tables:

Dates

Dates

01/01/2018

02/01/2018

03/01/2018

04/01/2018

05/01/2018

06/01/2018

07/01/2018

08/01/2018

09/01/2018

10/01/2018

11/01/2018

12/01/2018

13/01/2018

14/01/2018

15/01/2018

16/01/2018

17/01/2018

18/01/2018

19/01/2018

20/01/2018

21/01/2018

22/01/2018

23/01/2018

24/01/2018

25/01/2018

26/01/2018

27/01/2018

28/01/2018

29/01/2018

30/01/2018

Employees

Employee   Date

A	01/01/2018
A	05/01/2018
A	06/01/2018
B	04/01/2018
B	09/01/2018
B	10/01/2018
C	22/01/2018
C	25/01/2018
C	26/01/2018
D	17/01/2018
D	20/01/2018
D	21/01/2018

Regards,

MFelix