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

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

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

Alam Murray at ComputerGaga postd a challenge that someone had asked him to help with. https://www.linkedin.com/posts/alanmurray-computergaga_excel-powerquery-activity-6621158742408867840-lhby

Data containing meal selections came in looking like rosters, and weren’t helpful to anyone. So, the data needed to be converted 2 ways.
– A count of requests for each offered meal
– Each person on a row with the 3 meals they selected
This was a fun challenge. My solution is in Power Query. 2 Things were key for my solutuon:
– Transpose in Excel. This made it so much easier to work with the data in rows instaed of columns.
– Keep Rows. WOW! This let me easily pull the data apart, then put it back together with left outer joins.

Ultimately, this solution may be considered to have a lot of moving parts and could be simplified. But … it works.

#ComputerGaga
#ExcelChallenge
#KeepRows

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