Hello,I am quite to Power BI. Consider the below Excel where we have 4 employees and their monthly/weekly work status. I am trying to create a dashboard as seen in the second image. I have a problem with the entries where a person is working on multiple projects in a week.I took the Projects column and split it based on a comma delimiter in Power BI. Now, I need all 4 projects (number of projects may vary) to reflect as a single list in a slicer to control all other graphs.Please also suggest if there is a better way to get Projects worked on inputs from users in the Excel or perhaps a better data format to manage weekly and monthly reports. EmployeeWork HoursLeave HoursWeek NumberProjectsProject HoursJohn3481Project A, Project B20+14Derek4201Project C42Michael4201Project B, Project C30+12William4201Project C, Project D20+22John4202Project D42Derek3482Project A, ProjectB30+4Michael4202Project B, Project C20+22William4202Project A, Project B, Project C, Project D10+10+10+10+2John4203Project C, Project D30+12Derek4203Project B, Project C, Project D10+10+22Michael26163Project D26William4203Project D42John4204Project A, ProjectB30+12Derek3484Project C, Project D20+14Michael4204Project D42William4204Project B, Project C20+22

It does not matter that the number of projects can vary. Power Query is powerful enough to handle this gracefully but it might require some intervention in the M code. I want to see the raw data that you get, not data after "some processing". I bet you don't get data in the form "20+22+32."

Here's M code that's totally dynamic:// RawData let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVLBCsIwDP2V0as7rGkYXp09CYI3D2OHMQqbTgfD/8eUutXVRGhLafKS9/Ja1+o09U+VK4N07Glr2pd5urnulR3ybLlW9AzFTqNq8lpZN7s7vSDQUSSoYwj4vPPQ9a0bpcwq1vcgQ/UD7jqM49A+xA4RZwMvCLiPmBUEXyAbaS30V9HAiq4CJ5SkgCwlUmKk8N02JbYCNQ1mWbxQI08nTvXHNSMI4LpD6iiUPlYmZWyICMrTTOTV4D87tOAhyiOIH5dxEXlODHsUxhX9bt4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Work Hours" = _t, #"Leave Hours" = _t, #"Week Number" = _t, Projects = _t, #"Project Hours" = _t]), ChangeType = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Work Hours", Int64.Type}, {"Leave Hours", Int64.Type}, {"Week Number", Int64.Type}, {"Projects", type text}, {"Project Hours", type text}}), ChopProjectsIntoList = Table.AddColumn(ChangeType, "Chop Projects", each Text.Split([Projects], ",")), ChopHoursIntoList = Table.AddColumn(ChopProjectsIntoList, "ChopHours", each Text.Split([Project Hours], "+")), ZipLists = Table.AddColumn(ChopHoursIntoList, "PairProjectsWithHours", each List.Zip({[Chop Projects], [ChopHours]})), RemoveChops = Table.RemoveColumns(ZipLists,{"Chop Projects", "ChopHours"}), ExpandList = Table.ExpandListColumn(RemoveChops, "PairProjectsWithHours"), TurnListIntoRecords = Table.AddColumn(ExpandList, "TurnListIntoRecord", each Record.FromList([PairProjectsWithHours], {"Project", "Hours"})), RemoveList = Table.RemoveColumns(TurnListIntoRecords,{"PairProjectsWithHours"}), ExpandRecords = Table.ExpandRecordColumn(RemoveList, "TurnListIntoRecord", {"Project", "Hours"}, {"Project", "Hours"}), TrimProject = Table.TransformColumns(ExpandRecords,{{"Project", Text.Trim, type text}}), CleanProject = Table.TransformColumns(TrimProject,{{"Project", Text.Clean, type text}}), HoursToInteger = Table.TransformColumnTypes(CleanProject,{{"Hours", Int64.Type}}), RemoveAnyNullProjects = Table.SelectRows(HoursToInteger, each ([Project] <> null)), RemoveAnyNullHours = Table.SelectRows(RemoveAnyNullProjects, each [Hours] <> null and [Hours] <> "") in RemoveAnyNullHours

Nobody wants to manually create your data set and play with it. Please supply ready-to-copy data. Thanks.

OK, what does it mean that a person has worked 42 hours on this "Project A, Project B, Project C, Project D"? How do you want to allocate 42 hours to the individual projects? If there are no weights assigned to the projects, then the only sensible allocation is just a simple division of the hours among all the projects. Right?

Anonymous

Not applicable

5 years ago

Combine Multiple Fields in a Slicer

Hello,

I am quite to Power BI. Consider the below Excel where we have 4 employees and their monthly/weekly work status. I am trying to create a dashboard as seen in the second image. I have a problem with the entries where a person is working on multiple projects in a week.
I took the Projects column and split it based on a comma delimiter in Power BI. Now, I need all 4 projects (number of projects may vary) to reflect as a single list in a slicer to control all other graphs.

Please also suggest if there is a better way to get Projects worked on inputs from users in the Excel or perhaps a better data format to manage weekly and monthly reports.

Employee	Work Hours	Leave Hours	Week Number	Projects	Project Hours
John	34	8	1	Project A, Project B	20+14
Derek	42	0	1	Project C	42
Michael	42	0	1	Project B, Project C	30+12
William	42	0	1	Project C, Project D	20+22
John	42	0	2	Project D	42
Derek	34	8	2	Project A, ProjectB	30+4
Michael	42	0	2	Project B, Project C	20+22
William	42	0	2	Project A, Project B, Project C, Project D	10+10+10+10+2
John	42	0	3	Project C, Project D	30+12
Derek	42	0	3	Project B, Project C, Project D	10+10+22
Michael	26	16	3	Project D	26
William	42	0	3	Project D	42
John	42	0	4	Project A, ProjectB	30+12
Derek	34	8	4	Project C, Project D	20+14
Michael	42	0	4	Project D	42
William	42	0	4	Project B, Project C	20+22

Need Help

Forum Discussion

Combine Multiple Fields in a Slicer

10 Replies

Related content

Multiple Date Fields

Tables combine

Power Query Combine Multiple Files From Folder

Slicer pointing towards multiple fields

Combining filters on multiple fields