3

I've used Power Query to pull data and build a table. I've set all of the data types within Power Query, but when I load to excel the table switches all data types to "general". The formatting still looks the same but actual data types have changed. Makes it a pain to build pivot tables since all of the numerical values default to 'count' when adding to a pivot.

Improve this question
3
  • Maybe try loading the powerquery directly to a pivot table (in the close & load to dialogue box) rather than building a pivot table from the export? Commented Oct 27, 2020 at 22:34
  • When you are closing the query editor window having first created the query, have you tried 'close and load to' then 'only create connection' and 'save this data to data model', then loading it? (data > existing connections > double click the query to create a table. You can also insert pivot table where it points to the data model) Commented Oct 27, 2020 at 22:49
  • Unfortunately for this application I cannot use the data model and instead have to produce an excel table. Additionally I have about a dozen pivots that cut up the data from that table and then feed into some reporting tables. It's not pretty but it works and it's what I can do with what I've got right now. Commented Oct 28, 2020 at 20:08

2 Answers 2

Reset to default
3

There seems to be some confusion between data types and formatting.

Data types in Power Query define the type of data that the cell stores, i.e. Decimal number, currency, whole number, date, time, etc.

The Excel worksheet cell property for "General" is a format, not a data type. A cell formatted with "General" will show dates as dates, text as text, decimal numbers as decimal numbers, etc.

A pivot will default to count only when the underlying data type is text, not General.

If you need different formatting in the Pivot Table that you are building from the data, then you can change the formatting for each pivot table column. This does NOT affect the underlying data type.

enter image description here

Edit: PowerPivot will also default the aggregation to "count" instead of "sum" for numeric data type columns, if any cell in the column contains text, blanks or errors. In that case, you either need to clean up your source data, or manually change the PowerPivot aggregation from count to sum.

Edit II: If you're not using the Data Model, then by definition you're not using PowerPivot, because PowerPivot only works with data in the Data Model.

Yes, you can load the queries right into the worksheet, if you are happy with the resulting spreadsheet bulk and lack of Power Pivot functionality for DAX aggregation and table relationships.

Yes, you can build regular pivot tables from the query data in those worksheets, and the default aggregation for a numeric column is SUM(), even if the column contains blank cells (which you could easy enough test yourself, if you just took the trouble and two minutes to create a table with three rows of data, load it in to PQ, load it back into the worksheet, create a pivot table).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for the followup. I tested exactly what you said: I made two tables, one with nulls and one without, queried them and applied the proper data types, then ran pivot tables off of resulting tables. The one with nulls defaulted to Count aggregation and the one without nulls defaulted to Sum. This contradicts what you mentioned in your last edit, hence my confusion. See resulting tables: imgur.com/tIldn4E Is it possible there are settings that can change this behavior?
I ran the same test with null data from the query loaded into the grid and the pivot table defaulted to Sum. Maybe it's not as clear cut as the articles make us believe. So, that means if your data is dirty, you have to do some cleanup or change the aggregation. It sounds as if the latter is a big concern for you. May I ask why? Why not just change it to sum and get on with the rest of your day?
Weird. I did end up manually changing the aggregation and got the work done. It's more of an issue of wanting to understand why Excel is behaving the way that it is. If it's a problem on my end I'd like to get it fixed. Thanks for taking the time to look into it for me though.
-1

You can't do it that way, which is actually a feature not a bug, IMHO.

To format an entire column from a query result, in Excel,

  1. Right-click in a cell of that column, and choose Select - Table Column Data

  2. Format away as normal.

The nice thing about this is that you can have numbers in your query formatted as dates in your worksheet, etc. Also, there are no good ways to display commas in Power Query, but that won't stop you from getting things to look how you want them in Excel.

Improve this answer

2 Comments

You can't do "what" in which way? Why would you need to scroll in order to apply formatting to a column? Frankly, your answer does not make any sense whatsoever. And Power Query does not support formats at all, just data types. The very nature of Power Query makes formatting totally obsolete.
@teylyn, my answer is to the question I felt he was trying to ask rather than club him with technicalities. You're correct that you don't need to scroll unless you want to highlight every value in a table's column and there are too many rows to all fit on the screen, I estimated this to be a safe assumption because he wouldn't have even asked the question if the effort wasn't slightly annoying. You're right that Power Query does not support formats, just data types. Claiming that formatting is obsolete is belied by the fact that he asked this question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.