Create unique value (with some sort of transpose needed)

Hi All,

(This is my first post, so please correct me if I need to change anything. Thanks! :))

I am having an issue modelling data in my PBI model. It seems like a simple task, but I cannot get my head around it.

In short:

I would love to be able to link to the table using the field "Dimension ID", but this is only possible for me, if the value is unique.

In the linked excel file, I have tried to explain the issue with two tables;

• The raw ERP data
• and the end result which I am hoping someone can help me create in Power BI Desktop. 

Excel file with data example

I hope a bright mind will be able to guide me in the right direction. (Perhaps someone will even upload a .pbix file showing the steps needed to manipulate the data?)

For those who are interested:

This is a Dynamics NAV issue, where dimensions are stored as Dimension Set IDs to conserve space on the database (and to ensure performance), but this creates issues when people (me! :)) want to look across all dimensions of a given transaction. So if I'm able to create unique values of the dimension set IDs, I'll be able to slice across all dimensions. (which would be awesome! :))

Best

Martin