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

[luisrh]

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

[Greg_Deckler]

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

[luisrh]

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

widgetId	widgetCustomFieldId	widgetCustomFieldKey	widgetCustomFieldDescription	widgetCustomFieldLabel	widgetCustomFieldValue
71826	71826-text_input-1524231673080	text_input-1524231673080	mydescription	Who does the work?	a person
138804	138804-rich_text-1571891035693	rich_text-1571891035693	another desc	Any other comments?	No
138725	138725-rich_text-1571890673280	rich_text-1571890673280	Yet another field desc	what risks	no tracking
138725	138725-rich_text-1571890781095	rich_text-1571890781095	Desc 111	time constraints	may implement
94697	94697-select-1538108637278	select-1538108637278	Desc 222	what stage	Established
115029	115029-text_input-1548991246704	text_input-1548991246704	Desc 333	Change mgmt person	John
118103	118103-radio_group-1578725165404	radio_group-1578725165404	Desc 444	Classification	Medium
95298	95298-text_input-1538109355426	text_input-1538109355426	Desc 555	Link to this	http://google.com
130297	130297-checkbox-1566888702760	checkbox-1566888702760	Desc 666	is this simple	on
128457	128457-rich_text-1585892956077	rich_text-1585892956077	Desc 777	resource calc required	12 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!