Forum Discussion
SUM figures from converted CSV file to .xlsx file
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.