Calculating the Number of Days Until Next Birthday Using Power Query

/in /by

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 formula in Excel.

You’ll also see Power Query’s Column by Example; the POwer Query equivalent for the TODAY() function, and how to add 1 year to a date.

For info about the Amsterdam Excel Summit:

Summit Program

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
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

Power Query Drill Down and RoundUp function – Pies

/in /by

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 of pie and whole pies. In order to figure out how much kitchen time to schedule, we need tally all the slices and all the whole pies and get an equivalent number of whole pies.

Typically, we use Power Query to get to a summary, a table, a list, etc. But, what about those times when we need to do a lot of calculations queries to get to a value and use that value in further calculations/queries?

Without Drill Down, we’d have to use a join or tricky M coding. Power Query’s Drill Down simplifies this.

Also, you see how to round numbers with the Number.RoundUp function. Since we can’t make, say, 5 pies and 2 slices we use Number.RoundUp to get 6 whole pies.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
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

Power Query: Drill Down, Dependencies and Formulas

/in /by

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

Create a Variable Number of Entries in a List using REPT and Split Columns into Rows

/in /by

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 and Power Query’s split-columns and Split-Into-Rows features.

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

The New Geography Data Type in Excel: Let’s Play With It

/in /by

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 us; e.g., it converted “Zion, IL” into just plain “Zion.”
– We can get city names from a zip code … mostly. Some zip codes returned simply “Postal Code in the United States. But if you have to look up 10 instead of 200, hey! There’s a lot to appreciate.

Finally, I look at what happens if I open a file that has Data Types, but the version of Excel doesn’t have Data Types?

Check it out!

For an intro to 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

Import from a Specific Folder with Power Query … and that Formula.Firewall Error

/in /by

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:
Formula.Firewall: Please Rebuild This Data Combination

The first method involves telling Power Query to ignore privacy levels.

The second method shows how to respect the privacy levels and still get Power Query to cooperate. There’s some M coding involved but not too much.

Thanks to the following people for their help:
Imke Feldmann, https://twitter.com/TheBIccountant
Wyn Hopkins https://twitter.com/wynhopkins
Ken Puls https://www.excelguru.ca/blog/

Ken Puls’ blogpost on the Formula.Firewall error:

For an intro to Power Query here’s 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

Using Parameters in Power Query to Generate Random Data + Data Validation

/in /by

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? Something needs to tell us we don’t have that many. So! This video shows how to use Excel’s Data Validation, COUNTA and a Named Range to prevent requesting what’s not available.

Check out the video. Share your thoughts and ask your questions!

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

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
Website: https://ozdusoleil.com

My old blog: http://datascopic.net/blog-2-2

Passing a Parameter in Power Query: Find reviews that need to be done in x days

/in /by

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 Query.

It requires a little M-coding. A little!

Passing a parameter or variable into Power Query seemed like a strange idea. Why not just use a filter? That was my thought until I came across an odd situation:

We need to import data from specific files, but never all at once. But it’s a hassle to navigate from one file to another. It’s better to go ahead and import all the data and then pass a variable to Power Query and let Power Query filter what we need and don’t need.

Excel on Fire shirts and mugs:
https://www.zazzle.com/excelonfire
For an intro to 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

VLOOKUP-True Equivalent in Power Query with Dynamic Tiers

/in /by

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 a non-M-code, rogue way to get Power Query to be responsive when changes are made on the worksheet.

For Excel on Fire swag: https://www.zazzle.com/excelonfire

Patreon: https://www.patreon.com/Data

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