Power Query: Combine rows into a single cell with Text.Combine

/in /by

A question came in from someone who wanted to know how to merge rows of data into a single cell.

Imagine you have a person’s name in one column and then details about the person on multiple rows; and then another name and details about that person; and so on …

Now you’d like to merge each person’s details so that each person and their respective details are on a single row with their details in a single cell/column.

To do this, I show Power Query and the Text.Combine function.

This video also demonstrates my example of building a small model so that we can focus on the solution and not a massive amount of our real data.

#Text.Combine

#PowerQuery
#Grouping

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

Excel’s LET function in 3 Acts

/in /by

One of Excel’s newest functions: LET()
Incredible! This function allows the Excel user to create complex formulas that are easier to create and easier to read. But they do require the user to think several steps ahead.

In this video I show 3 examples and make 2 cases in favor of using LET:
– Very basic
– Recycle. This is when we have to do a complex calculation and then do it again later in the formula.
– Clarity. Here, when a complex formula is needed, it’s easier to set up the variables first and then do the final calculation using only the variables. This can be easier to real in several distinct chunks rather than a massive jungle of parentheses or use of helper columns.

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

Try … Otherwise: Power Query’s version of Excel’s IFERROR

/in /by

Thank to my friend Gráinne who showed me this in Power Query:

Try … Otherwise

In native Excel we have the IFERROR function for when we anticipate errors and know what we want to do with them. In this case, we want to add the Shipped and Inventory amounts. But, if someone has put a note in the Inventory column–instead of an error–we want to return the Shipped amount. Pretty easy to do with Try … Otherwise

There’s also a warning: Power Query does not treat 0, null and [blank] as the same things.

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

Excel Discussion: The hard way or the concise way?

/in /by

This video is a little different than the rest. Someone needed help and I came up with 2 solutions–both shown in this video. What’s more important about this is the discussion about the hard solution vs. the concise solution.

Sometimes the hard solution serves us better because we understand it. The concise solution may look impressive, but the question is: can we explain it and be responsible for it?

The solution is for someone who needed to move parts of a string of text. She needed to “move the D.” The solutions include:

– Column from Example
– Split column by delimiter
– Merge columns
– Handling leading zeroes in Power Query
– Data Types

#LeadingZeroes
#PowerQuery
#ColumnFromExample

  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: Calculating Incentive Points using Advanced Group-by & Left Outer Join

/in /by

A friend contacted me and asked how Excel might help calculate points for what he described as a “buddy system” where he goes to the gym. Here’s the challenge:

– Marvin and Drew are paired in this buddy system
– If one attends a class, the team gets 1 point.
– If both attend together, the team gets 4 points.
— How can we easily tally each team’s points?

This video looks at 3 teams (6 people) and a list of classes. In order to solve this, we use Power Query’s: group-by, advanced group-by, load to connection only, and a left outer join.

Download the workbook: https://datascopic.net/buddies

#PowerQuery
#OuterJoin
#GroupBy

 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

COUNTA: An overdue celebration of one of Excel’s best functions

/in /by

COUNTA is an unsung hero. It’s not likely to show up on anyone’s Top 10 Excel functions or features. Yet, COUNTA does a lot!

In this video I show multiple ways that COUNTA continues to be a favorite of mine–even with the glamorous features like XLOOKUP, Power Query, Dynamic Arrays, etc. You’ll see:
– COUNTA helps determine the size of a job before we get started.
– COUNTA helps measure progress.
– COUNTA in conjunction with the new dynamic array function, UNIQUE, gives us insight into our customers.
– COUNTA and RANDARRAY help create fake data for use in training or exploration.

Join the party! Let’s celebrate COUNTA!

#COUNTA
#ExcelCelebration

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

Extracting Data from a Column: 4 Solutions 1 Problem

/in /by

One way to get good at Excel is to find multiple ways to solve a problem. In this video I look at a recent problem where I pasted data into Excel from a website. The data ended up in a tall stack, and what I needed was buried in the mix.

I immediately thought of multiple ways to extract what was needed. This video shows 4 solutions and invites you to think of other solutions.

Solutions shown:
– Painful manual way … but it gets and answer
– ISNUMBER, RIGHT and IF
– Advanced Filter and Flash Fill
– Conditional Formatting

No way is “the right way.” The bottom line is to get the right answer. Take the challenge.

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

#OzduSoleil
#ISNUMBER
#CrapData

Mr. Excel (Bill Jelen) Challenge | Flattening Crazy Data | Podcast 2316

/in /by

Mr. Excel, Bill Jelen, was presented with a challenge where someone’s data was twisted in a weird sort of way and needed to be untwisted; e.g. the data needed to be flattened.

Here is the challenge: https://youtu.be/S9ow-1Jq_rE

Bill asked for other solutions than his own. Here’s mine. This is one of those examples where it helped me most to bring the data into Power Query and then Transpose it. This may not have been the most efficient solution. However, after the Transpose, I could see a clear bath to the end.

The solution involves:
– Transpose
– Unpivot
– Pivot

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

ComputerGagaChallenge 2: Data Normalization and Missing Entries

/in /by

In the previous video I showed a solution to a problem presented by Alan Murray at ComputerGaga. But Haider Ali asked about missing entries.

(To purchase Excel Insights, here it is: https://www.mrexcel.com/store/index.php?l=product_detail&p=381)

The challenge requires data to be converted from jumbled lists and into neat rows. The lists are what people have chosen for an appetizer, dinner and dessert. BUT! What if someone only wants an appetizer and dinner? That adds a serious twist to the solution.
Here, I re-do the solution and include several Power Query moves:

– Grasshopper Leg Pluck
– String of Pearls
– Pile In, Pile Up
They use: left outerjoin, unpivot, split columns into rows, merge columns

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