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

eyJrIjoiNWI0ZmUwOWEtMmIzMS00MjRlLTlmZTEtOTA5ZThmNjEwMmNjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9