Power Query: Drill Down, Dependencies and Formulas

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 break or we’ll spend a lot of time going through and reconnecting everything.

This video shows how the solution was to create the calculation as a separate table, Drill Down and use that as a variable. We reference that query in an existing formula and don’t have to worry about the query dependencies! DONE!

BONUS! In this video I also show how to deal with the problem of tables re-sizing themselves after every refresh. Check it out!

For an intro to Get & Transform (Power Query) try my Lynda.com course:

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My old blog: http://datascopic.net/blog-2-2