Tag: Excel
-
Power Query: How to load only fresh rows and create an Excel history table
I have previously written about the powerful ETL (Extract, Transform and Load) functions that are now in Excel Office 365 desktop edition. We have seen how we can combine tables and unpivot data (Excel articles). In this article I will show you how to create a history table for imported data. It is very common…
-
Next steps in Excel ETL – learning M
Excel 365 Get and Transform Data (was Power Query, actually ETL) allows you to use the M language to create custom columns. Naturally enough I hoped with would either be Excelish or JavaScriptish but no, because it is specialised for report writing it is quite different and you have to learn a new language. Here…
-
Excel ETL – import multiple CSV files
I have previous blogged on how to unpivot data using Excel and Excel 365 (was Excel 2016) just keeps getting better and better. It can now do ETL (Extract, Transform and Load) operations that used to require SSIS. This article tell you how to import multiple CSV files in one operation. (for example, bank statements).…
-
Using Excel 2016 to unpivot data
Normal people, by which I mean those who don’t process data all day will often create data that is present in a crosstab table. This is a poor structure for storing and processing information. We say that its “denormalised” because the three different columns “Europe”, “USA”, “Asia” contain information of the same type – “budget…
-
Create the Union of two tables in Excel revisited
In 2013 I published a technique to create the union of two tables in Excel. This post is still very popular and so I am revisiting this topic. Excel 2016 (and possibly Excel 2013 but I have not tested) has this capability built-in and the new method should always be preferred to the one that…
-
New features in Excel 2016
Excel 2016 has a lot of new features to wow the power user including support for cascading IF statements and a better CONCAT function. There are also a huge number of improvements to the Pivot Table function as it integrates even more closely with the Power BI tool. https://support.office.com/en-us/article/What-s-new-in-Excel-2016-for-Windows-5fdb9208-ff33-45b6-9e08-1f5cdb3a6c73?ui=en-US&rs=en-001&ad=US Anyone who works with Sales will…
-
Microsoft Office 365 Excel is a mobile app platform
I help run an underwater hockey club. To smooth administration I wanted to create an mobile application to record attendance and the payment of weekly subs. My usual approach would be to create a WordPress application or perhaps a mobile app using Cordova to package an HTML and Javascript application. However I am trialling Microsoft…
-
Excel coding style guide
I am introducing some students to Excel and have produced this brief style guide to producing more readable code which I think gives you 80% of everything you need to know. The strategy is to use Excel’s built-in ability to name sets of cells. Please add your comments. All tabular data must be in a…
-
How to create a list from an crosstab table in Excel
If you analyse data in Excel you know that Pivot Tables are magic and that they are generated from simple lists of records (“facts”). Unfortunately very often you data will be given to you by a user who has put it into Crosstab format and to get this back into a list you have to…
-
Microsoft OneDrive is better than Google Docs
I needed to share an Excel document so I investigated Microsoft OneDrive. Microsoft is late to the file syncing party Dropbox and Google Docs having got well ahead. I’ve used Dropbox a lot, because it just works; it is really effective and painless way of sharing files quickly. This is their core business and they…
Dr James Bayley 