Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Solved! Go to 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.
Occupation = if [Removed Columns.Date] = null then "Free" else "Busy"
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRegards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHey,
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
Hi,
Share a dataset and show the expected result.
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.
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.
Occupation = if [Removed Columns.Date] = null then "Free" else "Busy"
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you so much. it worked.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |