Power Query’s Column by Examples has been Improved

/in /by

A few months ago I compared Excel’s Flash Fill against Power Query’s Add Column by Example. Add Column by Example didn’t fare so well.

HOWEVER!

I got word that Add Column by Example has been improved. It has and it works great. Check it out.

#AddColumnbyExample
#PowerQuery
#PowerQueryTutorial

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

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

Power Query: Split columns between digits and non-digits

/in /by

This is a long-time problem with working with data: all of the fields are smashed against each other with no delimiter in between. In this video, names are smashed against phone number. How do we peel them apart?

In Power Query there is a brand new feature that splits columns in several ways:
– from Digit to Non-Digit
– from Non-Digit to Digit
– from UpperCase to LowerCase
– from LowerCase to UpperCase

Thus, it’s easy to split a column by the point where a string of letters changes to a number. It’s beautiful!

No more making wild formulas in Excel with the SUBSTITUTE function and Text-to-Columns. NO MORE! Those are officially the olden days!

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

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

Using a Transformation Table in Power Query’s Fuzzy Merges

/in /by

How can we merge data when New York is listed at NYC, New York City and NY, NY? This is a common problem and there’s a new feature in Excel’s Power Query that can help us solve this.

We can build a Transformation Table in Power Query as part of making a join. This video shows how to use a Transformation Table within a Left Outer Join. We go into the new menu for Fuzzy Merges and insert the magical Transformation Table.

This is new to Excel and Power Query but Transformation Tables have been part of PowerBI for several months.

#TransformationTable
#PowerQueryJoins
#FuzzyMerge

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

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

Emojis, Excel, Power Query & Dynamic Arrays. It’s true!

/in /by

Did you ever think that you could write a formula with a picture of grapes and have the result be a picture of a train?

It’s possible to add emojis in Excel. But it gets better! This isn’t like adding a picture just to make things pretty. No no. The emojis can be used like text or numbers.

This video shows 8 uses for emojis in Excel:
– Emojis can be used as values (text or numbers)
– Column headers
– Slicers
– Formulas
– Dynamic Arrays. I show both the FILTER and UNIQUE functions.
– Dropdown lists
– Power Query
– Excel Online

Thanks to Frédéric Le Guen for his blogpost that opened these possibilities. See his blogpost here:

Add Emojis to your charts, formulas

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

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

Power Query: Load to Data Model for Cleansing and Analysis

/in /by

One option when loading a query from Power Query is: Load to Data Model (or create Relationships). In this video that’s what we cover, and why.

In this video we have 3 tables of data and we’d like to answer questions that can only be answered if the data were all in one place.

We could use Power Query but Power Query would require a bunch of queries and isn’t good if we have ad hoc questions. Instead, loading to a Data Model (Relationships) in Power Pivot is the sexy thing to do!

The Data Model will treat the 3 separate tables like a single source of data that we can create one pivot table from.

One thing that’s important with Data Models and relationships. One side of the relationship needs to have all unique values. Example:

We can have Routes: TU-3 and WE-1 multiple times in a table, but we can only match that with a table that has all routes only one time each. More formally: we can have one-to-one relationships and one-to-many. We cannot have many-to-many relationships.

#DataModel
#PivotTable
#SexyData

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

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

Dynamic Arrays: FILTER Against a List, MATCH function with 2 Criteria

/in /by

We’ve used the dynamic array FILTER function to filter for multiple criteria that will return a lot of data. But what happens when we need to filter for things that don’t fit neatly into a group or category?

In this example we have a list of names and want to filter for specific people in our source data. Using the MATCH function and a helper column, it’s pretty easy to do.

This video also includes a quick summary of Amsterdam Excel Summit 2019 and a preview of Excel Summit South 2019 in Australia:

SumProduct

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

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

Binning Values

/in /by

Power Query is fantastic, but there’s an ongoing need to do the equivalent of an approximate match like we do when we use VLOOKUP-True to assign categories to values.

This video shows how to assign values by “binning” which is done by using Column by Example and getting help from Power Query. Power Query will build a formula based on how we train it. Once the formula is built, it’ll probably be wrong. HOWEVER! It’s so much easier to modify the formula than it would be to stack up conditions in a conditional column, or write a messy IF statement in Power Query’s M-Code.

This example is good if your categories don’t change. If you do need variable tiers/categories, it’s better to use the method shown in this video: https://youtu.be/EYgKciBr_dg

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

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

LARGE Function, Averaging Top Scores and Over-Thinking

/in /by

This one is about over-thinking. A friend asked how to average the top 3 of 4 scores. I started thinking about Dynamic Arrays, unpivot and maybe the opportunity to show off some Power Query. But when I went with my first and dullest idea, that’s what gave her exactly what she needed.

I used the LARGE function to pick:
LARGE, 1 (largest value)
LARGE, 2 (second largest value)
LARGE, 3 (third largest value)
add them up and divide by 3. DONE! BOOM!

Sometimes the simplest, dullest solution is all that’s necessary.

#LARGEfunction

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

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

Dependent Dropdown Lists in Excel with Power Query & Dynamic Arrays

/in /by

When we build models in Excel we must anticipate crazy entries. In this tutorial we try to prevent crazy matches between labels and containers. A 4×5 label won’t fit on our 3-ounce spray bottle, and a 1×1 label would look crazy on a 1-gallon jug.

First, we use Power Query and Unpivot to organize our source data. Then, we use the new functions UNIQUE and FILTER to make the dependent drop down lists, taking advantage of Excel’s new calculation engine.

Those work fine, but there’s a known problem with dependent dropdown lists (aka cascading dropdown lists): it’s easy to end up with crazy matches because the lists don’t re-set. It’s easy to select something with the child list and then change the parent list to something crazy. I handle this by using COUNTIFS and the WingDings font to flag crazy entries; i.e. create an alert.

Here is a link to Leila Gharani’s video where she solves this problem by using dependent combo boxes: https://youtu.be/aSPtWo3IiOM

#DynamicArrays
#DropdownLists
#DataValidation

======

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

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