Category: Excel
-
How to add a row number to an Excel table 2020 using Power Query Editor
Back in 2013 I wrote post on how to add a row number to an Excel table. This post is still popular and the method still works well. But it is now 2020 and I would do it differently. Every Excel power user should become familiar with the “Data” tab which gives access to 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…
-
Humour: Excel and big data
As consultants our job is to help the user and unlike the poor second line support person we usually have the time and infrastructure to do it. Here is an amusing rant from one such Sys Admin in The Register. The scenario will be familar to anyone who has ever done a data warehouse project…
-
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 