In my previous video I showed a solution to transforming data. We started with students and the courses they took, and converted that to a view that shows, by course, which student took each one. That video showed a one-and-done solution. It wasn’t dynamic; i.e., new students or courses would net be integrated. In this […]
We have a matrix showing Students and the courses they took: Connie took Menu Planning and Whiskey, Bourbon & Rye. But now, we’d like a view that lists the courses and which students took those courses; e.g., Who took the Nutrition course? MarvP in Seattle hit me with this challenge and on one of the […]
Calculating the days until someone’s next birthday or anniversary is a messy affair in Excel. It’s especially messy for people who’s next birthday/anniversary is in the next calendar year. This video shows an easier way to calculate the next birthday in Power Query. It takes a few steps but it’s more direct than the messy […]
Recent videos have included the Drill Down feature that’s in Power Query and PowerBI. My friend, Bart Titulaer asked for more detail about Drill Down because it isn’t so clear. Download the workbook: http://datascopic.net/drilldown In this video I show Drill Down as a means to get down to a value. The scenario: We sell slices […]
So … we’ve built a Power Query model to calculate. It’s complex and it’s working. BUT! Someone comes along and says a calculation needs to be changed. Typically, we could add a conditional column, but here, we don’t have that luxury because of complex query dependencies. If we add a column, something it likely to […]
We have 70 colors and each has a number saying how many labels of each we need: Teal, 11 Neon Green, 3 Copper Red, 5 … etc. How can we get a column of the 451 total entries that we need to generate labels? This video shows how to do it using Excel’s REPT function […]
Data Types were recently release for certain Insiders and in this video I play with the Geography Data Type to show you some strengths and weaknesses. Some things we see: – We can’t copy-paste-as-values to get rid of the Data Type and keep the value – The Geography Data Type tries to fix things for […]
Importing data from a folder in Excel’s Power Query is easy. But what if you want to use a dropdown list to select and import from a specific folder? It involves passing a parameter (as we’ve done in previous videos) and a little M code. This video shows 2 methods and addresses the mysterious error: […]
Let’s simplify the passing of parameters in Power Query, and use it to create a list of random names. In this video we use the Drill Down feature in Power Query so that we can ask for x number of names. But we go further! When if we have only 50 names but need 60? […]