- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you need to add an 'addcolumn' in there to bring in the Column Heading information. The way you have it now, assumes that the value data is all the same thing. ie people. But usually the different columns contain different 'types' of information like, color, size, cost, etc. So that info needs to be included in the unpivot...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is great, but one thing is missing for me.
In the table on the right, the column headres are stored in the "Purchase type" column (what would be the Attribute column in Power Query), while this DAX code doesn't create that attribute column. Any ideas on how to achieve that?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am not sure what I amissing but I am with Nart, the column headers NEED to be dynamic. The Union trick is helpful but you are hardcoding "Column1", etc. How do we generate the "Purchase Type" column from large number of columns like attribute column in Power Query, except of course with distinct values...?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Greg_Deckler ,
This is awsome technique, I am trying to implement it but getting following error, could you please guide me on this. All columns which I am trying to unpivot are of date type you can see it on the right side in fields.
Thanks in advance.
Regards,
Pooja Darbhe
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@PoojaDarbhe - I can't see all of the formula so not sure exactly where you are getting the error in your DAX. If we are talking about the last line in your screen shot, you could try to replace "" with BLANK().
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Greg_Deckler,
I have same error message as@PoojaDarbhe , i tred to change "" to (), then i got another error as below, please help, thanks.
changed "" to () as suggested but got another error as below
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous
Write Blank(), that will solve your problem.
Let me know in case of any issues.
Best regards,
Pooja Darbhe
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous ,
Instead of [Output All] write [Outputfg], it should be column name not the value.
Please try this and let me know if that works.
Best regards,
Pooja Darbhe
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the help, there was the problem with last line only.
Regards,
Pooja Darbhe
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That is a one brilliant solution, Thank You!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
interesting solution for unpivoting in DAX. 👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @Greg_Deckler ,
Just stumbled upon your tip. I already knew it, but I was wondering, as the first section of selectcolumns is the same across all, can you bring in a string value created by DAX and apply it to the last bit? A little bit like using Indirect in Excel.