Category: Excel
-
Power Query “each”
I am learning Power Query and the “each” function comes up a lot. Here is an explanation of its function an purpose for those of us who don’t have a background in functional programming. https://www.excelguru.ca/blog/2018/01/09/each-keyword-power-query/ This explanation of how Power Query uses anonymous functions that return true of false helped me understand the countorcondition parameter…
-
Power Query Refer to previous row
There is an excellent tutorial from Goodly here. TLDR; use the list index selector {}
-
Power Query SumIF
Yesterday I wrote about how to calculate a running total (cumulative total) using Power Query. This used List.FirstN and the Index to select the elements to sum. A similar challenge is calculating a sum based on conditions (SUMIF) if the condition is Index <= current value then this degenerates to the Running Total case. The…
-
Creating running totals in Power Query
Power Query brings the power of ETL (extract, transform and load) tools to Excel. Almost every job I have now starts with using Power Query to clean and filter the data I am using. But cleaning and filtering is only the most basic of Power Query’s functionality. It can also be used to calculate custom…
-
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…
Dr James Bayley 