Forum Discussion

Usman's avatar
Usman
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...
Need Help
  • MFelix's avatar
    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Share a dataset and show the expected result.

  • Usman's avatar
    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's avatar
      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

      • Usman's avatar
        Usman
        Frequent Visitor
        8 years ago

        Thank you so much. it worked.