Tracker - Way of working : Work from home Versus Office - Employee % over the years

[Wazza]

Hi there, 

I built a report to track the percentage of people working from home (WFH) versus from the office (WFO). 

We are tracking it in an Excel file, where employees state when they want to come to the office. 

The files from different departments are then all combined and tracked in powerBI to have a global picture. 
The issue is that the file is quite "long" as it's tracking for 2020, 2021, and a forecast for the coming months. 

Excel file: 

Employee ID	01/01/2020	01/02/2020	....	01/01/2021	01/02/2021	....	01/01/2022	01/02/2022
E01	WFH	WFH		WFH	WFH		WFH	WFH
E02	WFH	WFO		WFH	WFH		WFO	WFO
E03	WFO	WFH		WFO	WFO		WFH	WFH
E04	WFO	WFO		WFO	WFO		WFO	WFO

So when I unpivot the table to get something that I can manage in power BI, it becomes: 

E01	01/01/2020	WFH
E01	01/02/2020	WFH
E01	...
E01	01/01/2021	WFH
E01	01/02/2021	WFH
...
E02	01/01/2020	WFO
E02	01/02/2020	WFH
E02	...
E02	01/01/2021	WFH

365 days a year * 2.5 years * 30 employees.. = a lot of rows! 

Any simpler way of doing it to have a more responsive file and a more elegant way of tracking it? 

For example, would it make sense to "regroup employees" per day such as: 

Date	WFH	WFO
01/01/2020	E01, E02	E03,E04
01/02/2020	E01, E03	E02, E04
...	...	...

[v-yingjl · Accepted solution]

Hi @Wazza ,

You can try this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjUwVNJRCnfzIEDG6oDUGiGJ+WOo8oeSELXGGKr8seiDqDXBkMdGxsYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee ID" = _t, #"01/01/2020" = _t, #"01/02/2020" = _t, #"01/01/2021" = _t, #"01/02/2021" = _t, #"01/01/2022" = _t, #"01/02/2022" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", type text}, {"01/01/2020", type text}, {"01/02/2020", type text}, {"01/01/2021", type text}, {"01/02/2021", type text}, {"01/01/2022", type text}, {"01/02/2022", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Employee ID"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Attribute"}, {{"Data", each _, type table [Employee ID=nullable text, Attribute=nullable date, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "WFH", each Table.SelectRows([Data], each [Value] = "WFH")[Employee ID]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "WFO", each Table.SelectRows([Data], each [Value] = "WFO")[Employee ID]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"WFH", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"WFO", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Data"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Date"}})
in
    #"Renamed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Wazza]

I did the unpivot in Excel as a quick workaround but I will use this feature for the next file that I am building. 
Thanks 

[PhilipTreacy]

Hi @Wazza 

You should structure the data into a tabular format e.g.

No need to unpivot with this.  It's in the correct format for use in PBI/Power Pivot etc and makes reporting much easier.

Loading this data into PBI will comprress it so if your concern is file size, this structure is better.  You can't really get around the file/data being 'long', you have to track whatever data you need.

Regards

Phil

[Wazza]

thank you. 
I used Power Query in Excel to convert the file. 
This way, the "processing work" is done beforehand and PowerBI just has to display it 🙂 

[amitchandak]

@Wazza , if you want that in visual, you can use concatenatex

create a measure and use that in matrix with date on row and work from on Column and measure in values

concatenatex(Table, Table[Name])

[Wazza]

Hi, 
Thanks for your answer. 

Regarding the other format, it was just an idea that I had. 

If the only solution is to unpivot the table and have everything like currently, then ok. 
But if there is a simpler way, I am curious about it.