CDSoundzDJ
New Member
Work at a company that the end users love Excel and not to mention love to see the details by clicking on the cross-sections within a Pivot table.
Background . . .
Inside my Excel file I am using PowerQuery to load supporting data into a Data Model. From the Data Model I have created my PowerPivot table.
Problem 1:
When clicking the drill-to-details feature it spawns a new tab and loads the data. So far so good, but it defaults it to 1000 records. I found a workaround for that changing the default row count in the connection section, BUT sometimes this connection is not present so I have to link to some dummy file to make it visible to change the limit row setting.
MY BIGGER issue is when the details come back ALL the column headings contain the data model table name and the column names. ie: MY_SQL[First Name]
Can this behavior be changed? I'd rather just see the field names and not the fully qualified table.field option as this just makes the column names much longer than they need to. I have read about changing this in some connection setting, but all those seem to apply to PowerBI and not Excel.
Problem 2:
I have several metrics listed side-by-side in the pivot and when you click on a # such as 100 you expect the drill-to-details to return 100 rows, but instead it brings back the entire dataset. So it seems like Excel's Pivot is not able to identify both the X and Y axis fields that are needed in the where clause of the drill-to-details.
Note: How I did this was my dataset has Indicator fields that are just 1's and 0's that my pivot sums.
One thing I got to work was instead of one giant dataset, I created a single field called METRIC and then UNIONed all the smaller datasets together allowing the pivot table to just pivot that. So far that works, but now I am unable to add % of totals that were showing next to these metrics.
Thanks in advance for any help you can provide.
Background . . .
Inside my Excel file I am using PowerQuery to load supporting data into a Data Model. From the Data Model I have created my PowerPivot table.
Problem 1:
When clicking the drill-to-details feature it spawns a new tab and loads the data. So far so good, but it defaults it to 1000 records. I found a workaround for that changing the default row count in the connection section, BUT sometimes this connection is not present so I have to link to some dummy file to make it visible to change the limit row setting.
MY BIGGER issue is when the details come back ALL the column headings contain the data model table name and the column names. ie: MY_SQL[First Name]
Can this behavior be changed? I'd rather just see the field names and not the fully qualified table.field option as this just makes the column names much longer than they need to. I have read about changing this in some connection setting, but all those seem to apply to PowerBI and not Excel.
Problem 2:
I have several metrics listed side-by-side in the pivot and when you click on a # such as 100 you expect the drill-to-details to return 100 rows, but instead it brings back the entire dataset. So it seems like Excel's Pivot is not able to identify both the X and Y axis fields that are needed in the where clause of the drill-to-details.
Note: How I did this was my dataset has Indicator fields that are just 1's and 0's that my pivot sums.
One thing I got to work was instead of one giant dataset, I created a single field called METRIC and then UNIONed all the smaller datasets together allowing the pivot table to just pivot that. So far that works, but now I am unable to add % of totals that were showing next to these metrics.
Thanks in advance for any help you can provide.