Array of Values to be used in visuals

[JMccoy92]

Hello Everyone, 

I am working on a project to take data from a Power App in dataverse and use it in a Power Bi Report. The data is very basic and includes one table with a row for each employee in the company. There is then various flags and attributes to further describe the employee and where they work. The main goal of this report is to keep track of what employees are working from home, in the office or a hybrid of the two. In the app if an employee is a hybrid employee they have to select which days of the week they work remote. This provides me with an array column value  "Tuesday", "Wednesday", "Friday"  as an example, this could be a wide range of different combinations. The days not included in this list are meant to be days they will be in the office.  One of the requirments for the report is to show  Count of Employees in-office by day of week – Vertical Bar and Count of Employees Remote by day of week – Vertical Bar. I am really struggling to figure out a way to make this work. One option I have thought about doing is turning that column into a list, this creates a separate table with EmployeeNumber and Day of the week, duplicating the employee number based on number of days working remote. I think this would work for the "Count of Employees Remote by day of week – Vertical Bar. " request, I just don't know how I would do the other request or if there would be a better way. 

Any feedback will be greatly appreciated.

Thanks everyone! 

[KNP]

I think it should be a relatively straight forward issue to solve but can you please post some sample data in table format and maybe the Power Query code you have so far?

[JMccoy92]

Work status	Work option	Hybrid working remote days	Status Date	Employee_SK	Organization_SK
Not Submitted	Office full time		null	83	33
Approved	Hybrid	"Friday"	9/30/2021	1294	33
Approved	Hybrid	"Wednesday","Friday"	8/11/2021	1298	33
Not Submitted	Office full time		null	1305	33
Approved	Hybrid	"Wednesday","Friday"	9/30/2021	1293	33
Approved	Hybrid	"Friday","Tuesday"	9/30/2021	1290	33
Approved	Hybrid	"Monday","Tuesday","Friday"	10/7/2021	1303	33
Approved	Hybrid	"Tuesday","Wednesday","Friday"	10/8/2021	1288	33
Not Submitted	Office full time		null	1297	33
Approved	Hybrid	"Monday","Friday"	10/6/2021	1302	33
Approved	Hybrid	"Monday","Wednesday","Friday"	9/30/2021	1291	33
Not Submitted	Office full time		null	1296	33
Not Submitted	Office full time		null	1299	33
Approved	Hybrid	"Monday","Tuesday","Wednesday","Friday"	10/5/2021	1304	33
Not Submitted	Office full time		null	1292	33
Approved	Office full time		9/30/2021	1289	33
Approved	Remote	"Friday"	10/7/2021	1328	33
Submitted	Remote		10/7/2021	1329	33
Not Submitted	Office full time		null	1330	33
Submitted	Hybrid	"Friday"	10/10/2021	1331	33
Approved	Remote		8/12/2021	1332	33
Approved	Remote		8/12/2021	1333	33
Approved	Remote	"Monday","Tuesday","Wednesday","Thursday","Friday"	8/12/2021	1334	33
Approved	Office full time		10/8/2021	1335	33
Not Submitted	Office full time		null	1337	33
Not Submitted	Office full time		null	1338	33
Submitted	Hybrid	"Monday","Tuesday","Thursday","Friday"	9/30/2021	1339	33
Not Submitted	Office full time		null	1340	33
Approved	Remote		7/31/2021	1341	33
Not Submitted	Office full time		null	1342	33

[KNP · Accepted solution]

 There are obviously many ways to solve this but I think you're already on the right track.

From your data, I created the below (attached PBIX also)

This provides the count of remote days, obviously from there you can calculate in office days based on your requirement. I wasn't sure if it was only a five day week so I didn't assume.

Hope this helps. Let me know if you have any questions.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "nVVBboMwEPwK4oyE7YVgjr1UvTSR2kg9kByaxqhIASIKlfL7btIUbLK4xBICgzSzO+PxkmX+sm69125XFm2r9n7gr/K8+FBe3h0OXluUCj/hVeErPiTgDcDfBpn/cDw29fcF83TaNcV58YiP9xMu0hBYKJjguOYijeywN7Wv1Bcig82VYnMuKkPONRI5kNzVNAcWO9UfiZilHRnW3YWLomB2iue6GlGYDXEWJj0bsH8aupIgRa+PIJRDe9LZYZEmM5WZxReaGjGTYVLMyGvuLmbhDk0ddti2PbHmUOTeFm0ujTJ9lLSiF1XWrdKPvBlOoWVJb7TH3SJSV3kAjC52O5ewJB/EAXC7uN8hJDQAbaQFQB/SHjAjEOvPrqHSMSpEz1jaOePcA8Tuzifu0ImE2M/KlBlGaAHcsxTRM1rf4SSE4b8EkfOggegvTdsf",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"Work status" = _t,
        #"Work option" = _t,
        #"Hybrid working remote days" = _t,
        #"Status Date" = _t,
        Employee_SK = _t,
        Organization_SK = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Work status", type text},
      {"Work option", type text},
      {"Hybrid working remote days", type text},
      {"Status Date", type text},
      {"Employee_SK", Int64.Type},
      {"Organization_SK", Int64.Type}
    }
  ),
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type",
    "",
    null,
    Replacer.ReplaceValue,
    {"Hybrid working remote days"}
  ),
  #"Added Custom" = Table.AddColumn(
    #"Replaced Value",
    "Custom",
    each try Text.Split([Hybrid working remote days], ",") otherwise null
  ),
  #"Added Custom1" = Table.AddColumn(
    #"Added Custom",
    "RemoteDayCount",
    each try List.Count([Custom]) otherwise 0
  )
in
  #"Added Custom1"