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

This video revisits a challenge from a few years ago. 3 siblings needed a simple way to reconcile who owed what to whom after all the Christmas gits had been purchased. For example:

If Bo agreed to chip in $200 on gifts that Jay bought, and Jay agreed to chip in $90 on gifts purchased by Bo, what’s an easy way to see that Bo owes Jay $110? But there’s a 3rd sibling, Tanya who needs to be considered.

I solved this a few years ago when Power Query wasn’t available. I decided to revisit this and us Power Query but it proved to complicated. I had multiple queries, joins, custom and conditional columns … the Power Query solution was overkill.

This showed that it’s important to look at what’s at stake and use what’s appropriate. For this challenge, I did use Power Query’s unpivot, but the real work was done by SUMIFS, IF, and good layout.

Here is the 26 Best Microsoft Excel Tips of 2019 podcast
https://www.myexcelonline.com/podcast/026-the-best-microsoft-excel-tips-tricks-in-2019/
#SUMIFS
#UNPIVOT
#PowerQuery

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

We put a lot together in this superbly delicious Excel tutorial. We start with the revised XLOOKUP version 3.0 which has the “if not found” component moved to the 4th position instead of the 6th.

Then we move to import data from a webpage and clean the data using Power Query’s FILL Down, Text.Length and other feature. Then, the final component in this deliciousness is a Left Outer Join. It’s all here for your delight.

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

#XLOOKUP
#PowerQuery
#MSExcel

To do training we need fake data (dummy data) to work with. In this video I show how to use RANDARRAY and the TEXT function to create a list of dummy transactions. This solution also takes advantage of Dynamic Arrays and the new calculation engine behind Excel. We also generate a list of fake dates.

Power Query Tips & Techniques Course
https://www.linkedin.com/learning/excel-power-query-tips-and-techniques-office-365/what-you-should-know?u=2125562
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
#DynamicArrays
#RANDARRAY
#FakeDataGenerator

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

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