Help with relationships, categories, data

[PWR_YS]

Hello everyone, 

maybe someone can help me. I don't know how to create relationships with columns.

I have a table with the following values, the fat text is the column name I use in PowerBI. 

The output is several graphs for each work item with the date on the x-axis and the values at y, showing the values for each Branch.

Branch	Date	Work item 1	Work item 2	Work item 3
EU	01.01.2020	1	8	4
EU	01.01.2021	2	10	5
ASIA	01.01.2020	3	15	6
ASIA	01.01.2021	4	19	7

But I have gotten additional data for the workitems, called Categories. Each workitem has a category, some workitems are the same category. 

		Category 1	Category 1	Category 2
Branch	Date	Work item 1	Work item 2	Work item 3
EU	01.01.2020	1	8	4
EU	01.01.2021	2	10	5
ASIA	01.01.2020	3	15	6
ASIA	01.01.2021	4	19	7

When I tried to create relationship in PowerBI I had the problem, that if I connected category 1 to several workitems, the category name got changed to category 1_1. 

Example of how it looked in PowerBI: 

Branch:	Date:	Category 1	Category 1_1	Category 2
EU	01.01.2020	1	8	4
EU	01.01.2021	2	10	5
ASIA	01.01.2020	3	15	6
ASIA	01.01.2021	4	19	7

I also tried, to create a list like this as a separate query: 

Category	Workitem
Category 1	Workitem1
Category 1	Workitem2
Category 2	Workitem3

But with this I also did not know, how to create a proper relationship connection. 

I hope I could explain the problems I have in a proper way and that someone can help me.

Nevertheless, thank you very much for reading this far and I wish you all a wonderful week!

[amitchandak]

@PWR_YS ,

Ideally this should have come unpivoted , which means both Category and Work item in rows

		Category 1	Category 1	Category 2
Branch	Date	Work item 1	Work item 2	Work item 3
EU	01.01.2020	1	8	4
EU	01.01.2021	2	10	5
ASIA	01.01.2020	3	15	6
ASIA	01.01.2021	4	19	7

[PWR_YS]

Hello @amitchandak 

Thank you for the answer, when I unpivot it, how should I name the columns? 

And do I then have to manually create a relationship?

Thank you for your help!!

[v-rzhou-msft · Accepted solution]

Hi @PWR_YS ,

According to your statement, I know that [Work item 1/2/3] are in columns. I think your table is looks like as below.

I suggest you to unpivot data table. Select three columns  [Work item 1/2/3] and click unpivot. Then rename this column as Workitem.

Then create a Category table.

Create a relationship between two tables.

Finally, create a matrix to show data as you want.

You can download my sample and get more details from it.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Category	Workitem	Cat1
Category 1	Workitem1	Category 1
Category 1	Workitem2	Category 1_1
Category 2	Workitem3	Category 2

This type of table can. Last column you should have got after unpivot