Frequent Visitor

8 years ago

Solved

Matrix

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.

Need Help

MFelix

8 years ago

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

9 Replies

MFelix

Super User

8 years ago

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

Ashish_Mathur

Super User

8 years ago

Hi,

Share a dataset and show the expected result.

Usman

Frequent Visitor

8 years ago

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

Super User

8 years ago

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

TomMartens
Super User
8 years ago
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
Usman
Frequent Visitor
8 years ago
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
  Super User
  8 years ago
  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

Forum Discussion

Matrix

9 Replies

Related content

Matrix table

sorting matrix

Customize subtotal in matrix

Matrix formatting

Matrix Row Conditional Formatting