Power Query or Get & Transform (In Excel 2016) lets you perform a series of steps to transform your Excel data.
But what if your data source is not in your Excel spreadsheet but located on your desktop?
If it’s inside a CSV file – Comma Separated Values which is denoted by a .csv file extension & where the columns are separated by commas – it’s very easy to import data from csv and right into Power Query!
It’s very common nowadays to get data in the comma-delimited format.
Let’s suppose you have this set of data from the csv file:
The #1 complaint that I get from Pivot Tables is “Why do my values show as a Count of rather than a Sumof ?”
Well there are three reasons why this is the case:
1. There are blank cells in your values column within your data set; or
2. There are “text” cells in your values column within your data set; or
3. A Values field is Grouped within your Pivot Table.
1. BLANK CELL(S):
So if you have at least one blank cell in a Values column, Excel automatically thinks that the whole column is text based. Pretty stupid but that’s the way it thinks.
2. TEXT CELL(S):
Also if you have a cell that is formatted as Text within your Values column, then it will also cause it to Count rather than Sum. This usually happens when you download data from your ERP or external system and it throws in numbers that are formatted as text e.g. 382821P