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? […]
In this video we want to look up whose annual review is due within x days. Quickly list who’s due in 30 days, 14 days, 90 days … To make this easy–especially if this Excel workbook is for someone unfamiliar with Excel–we’ll set things up to “pass a variable” or “pass a parameter” into Power […]
So, we’ve got a discount grid and need to assign the right price to various amounts. Then, you want to add tiers or change the levels. VLOOKUP-True is what you’d use in native Excel. However, in Power Query (Get & Transform) this is very messy. Research revealed intense M-code solutions. In this video I show […]
When asking for help with a spreadsheet, it’s best to distill the need down to concepts. It might not be the real work that you need help with, or even the real data. However, by distilling a challenge down to the fundamental concepts and building nonsensical models, we can see our way past technical jargon, […]
Imagine a grid that has students and subjects. In the Algebra column, Eric has an x. How do we replace the x in the column to show ‘Algebra’? While I was at ExcelWeekend4 someone asked how he could replace each x in a matrix with its respective column header. The solution is tough but it’s […]