Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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...
@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.
@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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
7 | |
6 | |
5 |
User | Count |
---|---|
20 | |
11 | |
10 | |
9 | |
6 |