Task Names Separate lines for Same Project

[leanns]

Hi, 

I'm sure this is a simple solution, but I'm struggling to figure it out. 

My problem is that I have a data source (I don't have access to the power query unfortunately which I believe could solve all my problems so this needs to be done in the table editor) that lists all the tasks in a project individually. Each row in this data source has task name, project name, project id etc and I want to pull select tasks out because these are the important ones. In a table that I was able to edit and create, I pulled the columns Task Name, ProjectID, ProjectName, and the start date related to the task to create my separate Task A, Task B columns. 

I can pull the tasks out, but when I put them into a table visual each task is a different line and it's not connecting them to the same project. I filter out each task into it's own column at the moment using an if statement to look for the name of it within the Task Name column of the data source. 

For example see below: 

As you can see in the picture above (I just used excel to show my problem easily), they are the same project with two (or more) tasks that go with it but they won't be on the same row. In the relationship manager, I connected the projectID column with the original column to see if that would help, but it doesn't appear to be. 

Is there a way to do this? 

Thanks!

[HotChilli]

Can you post the data (not a picture) and show what the desired outcome is please?

Also can you clarify why you don't have access to power query?

[leanns]

@HotChilli 

Hi, 

Sorry but I can't post the data because one there is data that I can't share outside of the organization and since I can't access the source I can't delete the sensitive data. 

The desired outcome is this: 

Project Name	Task A	Task B	Task C	Task D
12345	Date1	Date2	Date3	Date4
67890	Date5	Date6	Date7	Date8
25468	Date9		Date10

I want all of the tasks that correspond with the same project Name to go on the same row, but currently they are creating a new row for each task. Not every task will have a date for each project, but there is a possibility of it. 

[HotChilli]

I don't want to see the real data. I want to see some sample data that shows what the table (or tables) looks like.  Mock something up in the same way you've done with the desired outcome.

[leanns]

@HotChilli 

Hi, please see the original post for the currrent state. 

[leanns]

@HotChilli , I realized now that you're looking for how the data is coming into the source. 

Here's how it looks before I manipulated it to pull out my individual tasks:

ProjectName	ProjectID	TaskName	TaskID	Start Date
12345	id1	random task	taskid1	date1
12345D	id1	TaskA	taskid2	Date2
12345	id1	task2	taskid3	date3
12345	id1	TaskB	taskid4	date4
67890d	id2	task1	taskid5	date5
67890	id2	TaskA	taskid6	date6
67890	id2	TaskC	taskid7	date7
56738	id3	TaskD	taskid8	date8

This is basically how the data appears in the table editor and the bolded rows are the tasks I'm looking for. Table below is the manipulation I've done so far to get my desired tasks (these are calculated columns within the table above just pulled out separately to show easily)

TaskA	TaskB	TaskC	TaskD
Date2
	date4
date6
		date7
			date8

Task columns are calculated by: TaskA = if(TaskName = TaskA, StartDate, "")

I hope this helps and I appreciate you helping me. 

[HotChilli · Accepted solution]

Great. Thanks for the data.

Does the ProjectName have typos? (additional upper case and lower case d on some names - i edited them)

I created a matrix visual (with ProjectName in rows, TaskName in Columns, First Startdate in Values).

I used the filter pane to filter TaskName to Task A, B, C, D.

and this is what I got

[leanns]

@HotChilli 

Thanks for the help. I knew it was simpler than I was making it out to be. 

The project name could have different ways of writing it and I believe I was able to figure that out myself. As for the matrix, I recently found out that the dates used for say Task A is considered the start date and the date for the rest of the tasks (B,C,D) is the end date. Is there a way in the matrix to denote this? 

[HotChilli]

possibly add a calculated column to add text "Start Date" to "Table A" and "End Date" to everything else.

Use this new column instead of Task Name in the matrix Column.

[leanns]

@HotChilli 

Hi, so it works and I was able to get rid of the duplicating rows which is great! But now I have a different problem. My task name column doesn't show every single task because there isn't a task associated with it. Is there a way to show all column even if there isn't a date available for it? 

I have this option checked shown in the picture below and it didn't change anything. 

What I currently have: 

Project	TaskA	TaskB	TaskD	TaskF
12345	Date1	Date2		Date3
67890		Date4	Date5
45678	Date6			Date7

What I want: 

Project	TaskA	TaskB	TaskC	TaskD	TaskE	TaskF
12345	Date1	Date2				Date3
67890		Date4		Date5
45678	Date6					Date7

I want all columns to be shown whether theres a value or not. 

TaskName and Date are calculated columns. TaskName is numbering the key tasks from 1-6 and then Date is looking at this column and pulling either the end date or the start date depending on the task name. 

I would assume that there's something I need to add to my if statement in the date column to do this, but I only want the rows that correlate to the key tasks and not the rest. There is over 100,000 rows in this data source and over half are not needed. 

Basically what's happening is that there aren't any tasks in the Original TaskName column that matches the KeyTask Name and because of that there isn't a date to look for since it's not there and thus it won't show up in the matrix. 

Thanks!

[HotChilli]

If the tasks were in a separate related dimension table, the 'show items with no data' should work