Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
luisrh
Responsive Resident
Responsive Resident

How can I create a Pivot in DAX instead of using Table.Pivot in power query

I am finding some really large performance issues with using Table.Pivot function in M code.  I am using a reference toan unpivoted table and then using Table.Pivot (source, ...).   The pivot is taking about  5 times as long as the unpivoted table load.  I am not doing any summarizations.  

Does anyone have some suggestions to doing this thru DAX?  I am looking but can't seem to find any help online.  

Any help would be greatly appreciated.

 

Thank you...

2 REPLIES 2
Greg_Deckler
Super User
Super User

@luisrh - Hmm, not sure about Table.Pivot. I created a DAX Unpivot once: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/m-p/574832#M256

 

Maybe if you provided sample data and expected output, I could whip something up ( or someone else could as well) Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



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...

@Greg_Deckler thanks you for your help.  below is the sample data.  The unpivoted data could have some duplicate data so there is some work that needs to be done in order to get columns to no be duplicate.   Please see the M code below..

 

widgetIdwidgetCustomFieldIdwidgetCustomFieldKeywidgetCustomFieldDescriptionwidgetCustomFieldLabelwidgetCustomFieldValue
7182671826-text_input-1524231673080text_input-1524231673080mydescriptionWho does the work?a person
138804138804-rich_text-1571891035693rich_text-1571891035693another descAny other comments?No
138725138725-rich_text-1571890673280rich_text-1571890673280Yet another field descwhat risksno tracking
138725138725-rich_text-1571890781095rich_text-1571890781095Desc 111time constraintsmay implement
9469794697-select-1538108637278select-1538108637278Desc 222what stageEstablished 
115029115029-text_input-1548991246704text_input-1548991246704Desc 333Change mgmt personJohn
118103118103-radio_group-1578725165404radio_group-1578725165404Desc 444ClassificationMedium
9529895298-text_input-1538109355426text_input-1538109355426Desc 555Link to thishttp://google.com
130297130297-checkbox-1566888702760checkbox-1566888702760Desc 666is this simpleon
128457128457-rich_text-1585892956077rich_text-1585892956077Desc 777resource calc required12 alerts per month

 

 

widgetCustomFields is a table like the one above...

 

This is the M code that does the pivot.

let
Source = widgetCustomFields,
#"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[widgetCustomFieldId]), "widgetCustomFieldId", "widgetCustomFieldValue"),
KeyLabel_RemoveColumns1 = Table.SelectColumns(Source,{"widgetCustomFieldId", "widgetCustomFieldLabel"}),
KeyLabel_RemoveDuplicates = Table.Distinct(KeyLabel_RemoveColumns1, {"widgetCustomFieldId"}),
KeyLabel_DuplicatedColumn = Table.DuplicateColumn(KeyLabel_RemoveDuplicates, "widgetCustomFieldId", "widgetCustomFieldId - Copy"),
KeyLabel_SplitColumn = Table.SplitColumn(KeyLabel_DuplicatedColumn, "widgetCustomFieldId - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.None, true), {"toDel", "keyPrefix"}),
KeyLabel_RemovedColumn = Table.RemoveColumns(KeyLabel_SplitColumn,{"toDel"}),
KeyLabels_Table = Table.CombineColumns(KeyLabel_RemovedColumn,{ "widgetCustomFieldLabel","keyPrefix"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"widgetCustomFieldColumnName"),
KeyLabels_TransposedLabels = Table.Transpose(KeyLabels_Table),
KeyLabels_TransposedToCols = Table.ToColumns(KeyLabels_TransposedLabels),
FinalResult = Table.RenameColumns(#"Pivoted Column", KeyLabels_TransposedToCols, MissingField.Ignore)
in
FinalResult

 

 

The final columns should look like below:

widgetCustomFieldDescription, widgetCustomFieldLabel, widgetId, widgetCustomFieldKey,  pivotcolum1, pivotcolumn2, pivotcolumn3.....  (these are the values that are created above in Table.ToColumns function call)

 

The bolded fields are not pivoted from source.

 

 

Thank you!

 

 

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.