Forum Discussion
Help with a table visualization
List.Distinct will do that:
let Source = Ressource, GroupRows = Table.Group(Source, {"ProjectName"}, {{"ResourceName", each _, type table}, {"SumTimesheetActualWork", each List.Sum([#"TimesheetActualWork"]), type number}}), TransformColumnToText = Table.AddColumn(GroupRows, "Custom", each Text.Combine(List.Distinct([ResourceName][ResourceName]), ", ")), Cleanup = Table.RemoveColumns(TransformColumnToText,{"ResourceName"}) in Cleanup
ImkeF Your solution worked thanks a lot!!!!
Greg_Deckler for some reason your solution partially worked. A new column was added but in each row it had all the resource names and not just those that worked in that specific project.
Can anyone help me making some relationships between the two tables? I tried but for some reason I always get an error even when I trie to relate the project name.
the columns of each table are as follows:
Projects table: ProjectName, ProjectDepartment, ActualWorkfromTimesheets(in hours), ActualWorkfromProjectonline(in hours), ClientName, Mandaysfromtimesheets, ProposedMandays, Completion%, ResourceNames(the column with all the resource names per project that you helped me with)
Resources Table:
ResourceName, Resource Department, ProjectName, Week#, WeekEndDate, WeekStartDate, TimesheetActualWork(in hours), MandaysfromTimesheets, ProjectDepartment, ClientName, ProposedMandays, Completion%
As you can see at this point my table are a bit two complex. for some reason I couldn't build any relationships between them and I used Table.Nestedjoin and copied some columns from the one to another in order to use in visualizations. But this is not ideal as far as I know.
Both tables have almost the same columns but the rows are different, as you can see in my first post on this thread.
Ideally I would like to have a project specific table, with columns such as:
ProjectName, ProjectDepartment, ActualWorkfromProjectonline, ClientName,
ProposedMandays, Completion%, ResourceNames(the column with all the resource names per project that you helped me with)
and also a resource specific table with the hours from the timesheets and columns such as: ResourceName, Resource Department, ProjectName, Week#, WeekEndDate, WeekStartDate, TimesheetActualWork(in hours), MandaysfromTimesheets
And then just use relationships to make all the data usable in visualizations.
I know I'm asking a lot but you've been really helpful so far!
What does the error-message say?
ImkeF If I try to relate The project name in both tables I get this error: "We cannot create a relationship between "Project[projectname] and Timesheets[projectname]. This could be because there is missing intermidiate data to connect the two columns."
Both columns have the same data. the only difference is that in the Timesheets[ProjectName] there are multiple rows of each project names because its row represents one input of hours in the timesheets from one resource for that project.
That's a bit surprising - didn't we just create a table with unique values on the Product Name?
The recommendation to normalize your data (split data tables into multiple tables in order to reduce redundant information) would normally only show it's benefitial effects if you create a 1:n-relationship. So maybe you should re-analyze your table structure.
Question is, if this is really needed here. Any signs of performance problems already? Otherwise I'd go with one big table.
We constructed a column with the resources that worked in each project.
But that is not my problem now.
I just want to have cleaner tables that are more easy to use.
Let me explain once more how my data looks.
I have two tables. the first is a project table. that has all th information on my projects. stuff like project name, client name, proposed mandays etc.
ProjectName ProposedMandays
a 11
b 34
c 23
the second table is constructed from the timesheets my resources fill every week. each row in this table is an entry in these timesheets. For reference it looks like this:
ProjectName ResourceName TimesheetActualWork Week
a John 8 1
a John 5 2
a George 8 1
b John 3 2
b George 8 2
As you can see this table's data is the actual work from the timesheets, per week per project and per resource so I have multiple rows for the same project/resource/week.
For some reason I can't relate these two tables with project name so I'm forced to copy some columns from the project table to the timesheets table and also the oposite. in order to have all the data I need in the table that I'm using.
I'm constructing visualizations from both tables, I dont have just one main table.
The problem with the way my data looks right now is that it's hard to manipulate. I have some columns that are the same on both tables because I can't make a relationship between the two tables.
I tried merging both tables into one but that didnt work well and wasn't convinient because the project table is arranged per project and the timesheet table is arranged per prioject, per week and per resource. Combining the two tables made the final table really hard to work with...