Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.