Teaching analytics with modern Excel
If, as I write in Advancing into Analytics, lookup functions like VLOOKUP() are the duct tape of Excel, then relational joins in Power Query are like a full-on blowtorch, allowing you to contort data …
Continue Reading about How to do a Cartesian/cross join in Excel Power Query →
Have you ever had a group of items separated by commas in Excel that you wanted to break into separate cells? Take the following example: Here we have a list of signups for an upcoming office …
Continue Reading about How to split data into rows using Excel Power Query →
Download the white paper here 👇 Be sure to subscribe for complete access to my analytics resource library Thank you for checking out this white paper. Power Query is the most exciting …
A friend sent me the following Excel question: I am sorting a list, multiple of thousand entries. When I use the magnifying glass and enter a search term I get a pop-up window that says there are …
Continue Reading about How to filter the results of an Excel worksheet in another worksheet →
Have you ever put an analysis together in Power Query, but you'd like to edit the steps once it's already been loaded? Perhaps you want to tweak a filter or add a new column, for example. Let's see …
Continue Reading about How to edit a query that’s already been loaded in Excel Power Query →
null and missing values in Power QueryI've written in the past how Power Query enforces good data hygiene practices that aren't always in Excel. One of those is the presence of a null or dedicated missing value. This has been a real …
Continue Reading about How to understand null and missing values in Power Query →