Teaching analytics with modern Excel
pandas, openpyxl and Power QueryIn an earlier post I provided an overview of how to use the pandas and openpyxl packages together to automate the production of Excel workbooks. In that post, I likened pandas as the "Power Query of …
Continue Reading about How to understand the relationship between pandas, openpyxl and Power Query →
I had a great discussion with host Paul Barnhurst along with fellow guests and Excel MVPs Mark Proctor and Celia Alves on why and how finance and FP&A can be more effective and efficient in …
Continue Reading about How to improve your Excel skills (FP&A Today podcast) →
In a previous post, we looked at some of the limitations of XLOOKUP() and other Excel "duct tape" lookup functions. Relational joins in Power Query were introduced as a more robust alternative. This …
In my book Advancing into Analytics I called VLOOKUP() the "duct tape of Excel:" Technology tactics move quickly. Excel now offers XLOOKUP() as a modern alternative to VLOOKUP(). But the theories …
Continue Reading about How to understand relational joins in Excel Power Query →
One of the best things about Power Pivot and Power Query is that once you've learned how to use them in Excel, you can easily transfer that knowledge to Power BI: But not only do your skills …
Continue Reading about How to load a data model from Excel to Power BI →
When working with tables of data in Power Query, any operation by definition has to involve either operating on rows or columns of a table. A previous post showed some typical techniques for working …
Continue Reading about How to transform columns in Excel Power Query →
