Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

Super User
Super User

@luisrh - Hmm, not sure about Table.Pivot. I created a DAX Unpivot once:


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:

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.

@ 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!:
Mastering Power BI 2nd Edition

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


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

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)



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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors