Reply
New Member
Posts: 1
Registered: ‎01-04-2019
Accepted Solution

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! Smiley Happy)

 

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! Smiley Happy) 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! Smiley Happy)

 

Best

Martin


Accepted Solutions
Highlighted
Super User
Posts: 1,574
Registered: ‎09-06-2015

Re: Create unique value (with some sort of transpose needed)

You're looking for a pivot-operation (without aggregation).

See file attached.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Attachment

All Replies
Super User
Posts: 9,958
Registered: ‎07-11-2015

Re: Create unique value (with some sort of transpose needed)

I'm going to call out to @ImkeF once again as I think that you should be doing that in Power Query.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
Super User
Posts: 1,574
Registered: ‎09-06-2015

Re: Create unique value (with some sort of transpose needed)

You're looking for a pivot-operation (without aggregation).

See file attached.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Attachment