DAX Unpivot

Super User
159 Views
Highlighted
Super User
Posts: 9,477
Registered: ‎07-11-2015

DAX Unpivot

At a recent user group meeting in Columbus Ohio, one of my members presented me with a problem they were having. The member was working with a SharePoint list and attempting to unpivot a number of columns on thousands of list items. For some reason, the unpivoting, specifically, was causing massive performance issues. Therefore, I suggested unpivoting the data in DAX instead and created this example for the member to demonstrate the technique. Fortunately, this worked very well and was very performant even at scale. So, if you are having trouble with unpivoting data in Power Query, this technique may work for you.

 

The original example data looks like this:

Name Date Column1 Column2 Column3 Column4 Column5 Column6
Meeting 1 Monday, January 1, 2018 Greg Jason        
Meeting 2 Friday, January 5, 2018 Jason Frank George Battina    
Meeting 3 Wednesday, January 10, 2018 John Greg Jason Battina George Frank

 

The DAX Unpivot formula is:

 

Table = 
FILTER(
    UNION(
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column1]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column2]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column3]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column4]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column5]),
        SELECTCOLUMNS('Table1',"Name",[Name],"Date",[Date],"Column1",[Column6])
    ),
    [Column1]<>"")

 

This will make the above data look like this:

Name Date Column1
Meeting 1 1/1/2018 0:00 Greg
Meeting 2 1/5/2018 0:00 Jason
Meeting 3 1/10/2018 0:00 John
Meeting 1 1/1/2018 0:00 Jason
Meeting 2 1/5/2018 0:00 Frank
Meeting 3 1/10/2018 0:00 Greg
Meeting 2 1/5/2018 0:00 George
Meeting 3 1/10/2018 0:00 Jason
Meeting 2 1/5/2018 0:00 Battina
Meeting 3 1/10/2018 0:00 Battina
Meeting 3 1/10/2018 0:00 George
Meeting 3 1/10/2018 0:00 Frank

 

 

 


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

Proud to be a Datanaut!


Attachment