#ExcelHash: Your Suggestions are Needed

/in /by

#ExcelHash is a game we’re going to try out and you get to suggest the 4 ingredients.

ExcelIsFun
MrExcel.com
Leila Gharani
MyOnlineTrainingHub
Excel Campus

and I are going to build something based on your suggestions. It might be useful, it might be patently useless. The goal is to create something integrated that does SOMETHING. Anything. 😀

Suggestions so far:
XOR
A picture of a lizard
Slicers
BESSELL
Data Consolidate

The 4 ingredients will be chosen at random on 6AUG.
Please. Leave your suggestions in the comment section or use
#ExcelHash on Twitter.

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

Use Power Query to Identify 2 Consecutive Months of Underperformance

/in /by

If performance is below standards for 2 consecutive months, something bad is going to happen!

Highlighting periods like that are challenging in Power Query because PQ forces us to think in terms of whole columns or whole tables. We can’t easily compare a range of cells against each other and pluck out the offenders.

This is based on a real project where a client could not bill for periods where they fell short of their promise for 2 consecutive months.

In this video I show a solution that includes merging tables, Unpivot, adding custom columns, and adding conditional columns. As an extra step, I make the threshold variable by using Power Query’s Drill Down and a little massagin

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

A Case Study on Data Quality: Find & Clean Skunk Data

/in /by

Too often I see spreadsheets that have models built, complex formulas, etc. The user will come to me and ask for help with automation. But I often discover data quality issues that cause us to back up and assess everything.

I was helping someone with his project. Lots of data in multiple workbooks, summary pages, hidden sheets everywhere, fine details … just wild!

I slowed things down so that we could check the data quality. WOAH! That’s when my client observed how people were making additional rows of data to make amplifying details. Example:

Row1: Food/Catering
Row2: Coffee never arrived

Our goal was to count the Food/Catering problem. But, we have TWO rows … DOUBLING the number of problems that we really have. We don’t want that detail in Row2.

This video summarizes the situation. We look at the data using pivot tables. We then use tables and a Power Query inner join to merge the tables and isolate the data that we want to keep.

We don’t spend a lot of time on the Excel stuff. The focus here is on the importance of knowing your data and keeping data quality on your mind at all times.

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

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