CODEWITHSUNDEEP
excelpowerquery
LeroyBrown
LeroyBrown

Reputation: 55

How do I get Excel to preserve data types from power query when loading to a table?

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.

Upvotes: 2

Views: 3501

Answers (2)

JSmart523
JSmart523

Reputation: 2497

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.

Upvotes: -1

teylyn
teylyn

Reputation: 35915

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

Upvotes: 3

Related Questions