Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Transpose and group table

Hi everyone,

 

I currently got the task to rearrange a table so it has a different format, but I can't figure our how.

The specifications of the article numbers need to be paste behind the article number (grouped).

I also have a count in the last column. If the count is < then 2 then the specification name doesn't need to be in the table

Unfortunatly it is not possible to do this in the query editor, so it has to be DAX or an other creative way.

Example;

ArticlenumberSpecification 1Specification 2
12345ColorHeight

 

My current table (visual table);

ArticlenumberSpecificationCount
12345Color4
12345Height2
54321Color2
54321Width2
54321Material2
65432Color1

 

It would really help me out!

 

Thanks,

Need Help
Show and Tell
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    First create an index column in query editor：

     

    Then choose column "Specification" >unpivot columns:

    Then back to table Data view, create 2 calculated columns:

     

    Column = RANKX(FILTER('Table','Table'[Articlenumber]=EARLIER('Table'[Articlenumber])),'Table'[Index],'Table'[Index],ASC,Dense)
    Column 2 = 'Table'[Attribute] & 'Table'[Column]

     

    And a measure:

     

    Measure = IF(MAX('Table'[Count])<2,BLANK(),MAX('Table'[Value]))

     

    Finally put column "Articlenumber","column 2" and measure  to a matrix, and you will see:

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    Looks like the Specification columns need to be unpivoted in Power Query.  Where does the Count column come from?

    Perhaps you could post a larger data sample with the desired outcome.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ，

     

    First create an index column in query editor：

     

    Then choose column "Specification" >unpivot columns:

    Then back to table Data view, create 2 calculated columns:

     

    Column = RANKX(FILTER('Table','Table'[Articlenumber]=EARLIER('Table'[Articlenumber])),'Table'[Index],'Table'[Index],ASC,Dense)
    Column 2 = 'Table'[Attribute] & 'Table'[Column]

     

    And a measure:

     

    Measure = IF(MAX('Table'[Count])<2,BLANK(),MAX('Table'[Value]))

     

    Finally put column "Articlenumber","column 2" and measure  to a matrix, and you will see:

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!