Redux: Students, Courses, Power Query, Group By & Lists

/in /by

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 video, I show how a violent mob came after me with brooms and things. They wanted a dynamic solution … and here it is!

This video shows how to use Group By in Power Query, and then add a Custom Column to extract data from a collapsed table through use of a list. We use the function: Table.Column

Then I demonstrated that the solution is indeed dynamic. And I close with a Thank You to all the people who’ve been supportive, challenging, and genuinely interested in keeping this world’s data clean.

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

Steamy Data Transformation, Pivot, UnPivot & Expression.Error

/in /by

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 Excel forums, there was an Array Formula solution, and a solution with some List and M-Code trickery. I believe I came up with a sweeter, steamy, more sexy solution in Power Query with Pivot – Don’t Aggregate, UnPivot, Duplicate Column, Merge Columns.

I also show you one of those weird, unhelpful error messages in Power Query (Get and Transform): Expression.Error

Basically, when using Pivot – Don’t Aggregate, we’re asking Power Query to put our raw data into a grid. Don’t count it, sum it, or anything. But, if there are duplicates, Don’t Aggregate craps out.

A regular Pivot could give us a 2, 3 or whatever. But the Don’t Aggregate piece needs a place for each of those 2 or 3.

Check out the video, let me know if you have questions. And check out my courses at LinkedIn Learning for more Power Query and Excel.

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

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