How do you create a heirachy with this set of data?

[osuraperera86]

I'm working on a portfolio management solution for my team to create a dashboard to visualise the value created. Until now my model had 3 layers for its hierarchy. Recently the business wanted to add the 4th layer (Tasks) to the hierarchy. There are many projects which don't have any tasks, and I'm struggling to think of a way to create a hierarchy including all four layers. The hierarchy data looks like this. Keep in mind that most data (time stamps, statuses....) are built into the project level and not for the task level. 

Task	Project	KA	Objective
a	z	e	g
b	z	e	g
c	z	e	g
d	x	e	g
e	x	e	g
f	x	e	g
d	y	f	g
	q	f	g
	r	f	g
	s	f	g
	l	i	h

The heirachy should be Objective -> KA -> Project -> Task

g,f,y,d  or  g,f,q

Thank you very much. 

[truptis]

Hi @osuraperera86 ,

Try this:

Right click on Objective column -> click on create hierarchy 

Then you will see a new hierarchy created i.e.- Objective Hierarchy 

Then right click on KA -> you will see an option called "Add to hierarchy" -> then select Objective Hierarchy

Repeate same thing for project and task.

You will then have a hierarchical format

@osuraperera86 -> please hit the thumbs up and mark it as a solution if it helps you. Thanks.

[Cookistador]

Select your Objective column and click on Create hierachy (... in the fields pane) 

Then, select KA and Add to Hierachy --> the one you created, then did the same for Proejct and tasks

You should get something similar to my screen

[osuraperera86]

Sorry for the miscommunication. I was after a solution which can be more or less done through a power query. The task level information is not in the same table. There are two SharePoint lists (for this example) extracted. I wanted to see whether I could merge them and create the hierarchy of information inside a table. Atm the model uses bi-directional relationships ( which I try to avoid) - see below.

The question is more about how to make power bi add a line when there is more than one task (when merging the files) and how to ignore when there is no task. Ideally, when merged together the table should hold the hierarchy as I mentioned in the original question but retain data in columns. Not all projects have tasks, but all tasks have projects. 

Objective	Key Activity	Project	Task	Planned Start date	Start date
A	B	C	D	xxx	xxx
A	B	C	E	xxx	xxx
A	B	J		xxx	xxx
F	G	H	I	xxx	xxx