Teaching analytics with modern Excel
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 →
SWITCH() function in Excel Power PivotIt's amazing to think that everything we do on our computers (at least for now) is powered by a series of True/False states. And while most of these operations are done behind the scenes, there's …
Continue Reading about How to use the SWITCH() function in Excel Power Pivot →
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 →
We've probably all heard by now that data professionals spend 80% of their time cleaning data. But why? It's true that in data analysis, some assembly is required -- whether to meet the assumptions of …
Continue Reading about You’re probably storing your data in Excel the wrong way →
Adding a calculated column is one of the most typical tasks in data cleaning -- profit ratios, date durations, you name it. Power Query makes this a simple part of the data cleaning process through …
Continue Reading about How to create calculated columns in Excel Power Query →
