Forum Discussion
SUM figures from converted CSV file to .xlsx file
I have downloaded a bank statement and converted it from a .csv file to a to .xlsx file. I have changed the format of the cells to "number" but the SUM Function does not work and just returns a Zero?
How do I get the numbers to SUM?
Thanks
Mark
I normally do not use PowerQuery, preferring to stay within the Excel formula environment that I know well. However, for this type of data import task, PQ is the runaway winner. It allows you to omit or reorder columns, to filter types of income to one or more Tables and set the datatype (i.e. date, text or currency) for each column. Best of all, when you come to repeat the task the following year, you simple change the data source and all the additional steps rerun the analysis.
- Thanks Peter, not sure I understand how to use PQ! It seems to open data in another window and it doesn't look as if its possible to add a row and sum a column? I will look for some tutorials online.
Thanks for your help.I treat PowerQuery as a separate application built, I think, by the database team at Microsoft, and then integrated with Excel. It accesses a wide range of data sources (web, databases, text files such as csv) and can read tables from Excel files within a directory. Rather than cutting and pasting data to Excel and then sorting out the mess, it allows the data to be cleaned and transformed before import.
The resulting data will be loaded to an Excel Table and, from there, may be referenced using normal structured references. Summation within a Table is a standard process and can be applied to the imported Table by adding a 'totals' row. Adding further columns of manual input data would be difficult because the next time data is refreshed the row numbers might have changed, so relying on relative position on the worksheet is not a good idea. However, the user input data could also be read into PowerQuery and an inner join used to combine the tables. Similarly rows can be added using the append operation on two or more tables.
Note: Many Excel users do extract data from Excel to PQ in order to process it and then bring it back to Excel for presentation. I tend to do things differently in that once data is present in Excel, that is where I process it. Modern Dynamic Array functionality built using SUMIFS, UNIQUE, BYROW, GROUPBY, PIVOTBY are capable of providing a similar analysis capability, though the environment is limited to a million or so rows.
- How did you do that conversion *exactly* ?
