PQ Challenge Splits and Lists

Рет қаралды 12,219

Күн бұрын

In this challenge we need to use Power Query to split data, pair lists, avoid hard coding header references and more...
00:00 Intro
00:42 The Challenge
03:18 Avoid renaming a column directly via adding a conditional column step
05:48 Avoid renaming a column directly via a fancy formula trick
09:46 Extract data from 4th character using Extract From Range
10:50 Extract data from 4th character using Split by Position
11:36 Splitting Text into Lists and Recombining using Table.FromColumns
Link to Gil's Blog Post datachant.com/2017/01/26/powe...
Link to Oz's video • Power Query: Split Mul...
If you'd like to have a go before (or after) watching my approach then you can download the file from our blog here...
accessanalytic.com.au/new-yea...
Feel free to send us your approach and we'll add it to the blog page (unless you would prefer us not to).
Connect with me
wyn.bio.link/
Sign up for our newsletter to get advance notice of the future challenges...
accessanalytic.com.au/free-ex...

Пікірлер: 77

@JaimeHaddad 2 жыл бұрын

Great video Wyn! Looks simple but has a lot of tricks that we need to consider to solve it. Thanks also to the people that contribute. I like that you mention them!!

@AccessAnalytic 2 жыл бұрын

Thanks Jaime, I appreciate the kind feedback

@shirleymoreman6725 2 жыл бұрын

I've only just discovered your challenges Wyn but loving them. Lots of great tips and tricks to improve my Power Query skills - every day's a school day!

@AccessAnalytic 2 жыл бұрын

That’s great Shirley, we’re all learning from these 😀

@Franklinvaz 2 жыл бұрын

Never seen any such scenario oriented videos. Love your works. Thanks. Totally useful for beginners like me to understand the possibilities of transforming data using Power Query.

@AccessAnalytic 2 жыл бұрын

Great, glad it’s useful, thanks for taking the time to let me know

@vacilando86 2 жыл бұрын

With every new video you make our life easier. Respect!

@AccessAnalytic 2 жыл бұрын

Glad to help, thanks Alpay

@khawarmalik5239 2 жыл бұрын

So many new trick I have learned in one single video. Thank you very much for creating this content.

@AccessAnalytic 2 жыл бұрын

You’re welcome Khawar, thanks for leaving a comment

@Howlandta 2 жыл бұрын

I love these challenge videos, so many cool tricks to learn!

@AccessAnalytic 2 жыл бұрын

That’s great to hear Tyrone

@GrainneDuggan_Excel 2 жыл бұрын

I love learning how to tweak the M code to get exactly what we need. Thanks Wyn for sharing multiple techniques to solve the problems.

@AccessAnalytic 2 жыл бұрын

No worries Grainne, I think these sorts of challenges help us push ourselves into learning new techniques. I’m enjoying them.

@raimundojs9547 Жыл бұрын

Exactly what I needed! Thank you so much for sharing!

@AccessAnalytic Жыл бұрын

You're very welcome

@iankr 2 жыл бұрын

Great techniques. Thanks to you and everyone else, Wyn.

@AccessAnalytic 2 жыл бұрын

Yeah I picked up a few pointers here

@johannlotter4984 2 жыл бұрын

Really great video. A bunch of cool tips in there.

@AccessAnalytic 2 жыл бұрын

Thanks Johann

@MrWish332 2 жыл бұрын

Great Video, I always find something new in your Videos. Best Wishes

@AccessAnalytic 2 жыл бұрын

Thanks Vishal

@wayneedmondson1065 2 жыл бұрын

Awesome Wyn!! Just emailed you my solution. Thanks for the challenge! Looking forward to more in 2022 :)) Thumbs up!!

@AccessAnalytic 2 жыл бұрын

Cheers Wayne

@martyc5674 2 жыл бұрын

Thanks Wyn- Some great tips in this one 👍

@AccessAnalytic 2 жыл бұрын

Cheers Martin

@OzduSoleilDATA 2 жыл бұрын

GOOOOOD Stuff! And thanks for the shoutout. 🚀

@AccessAnalytic 2 жыл бұрын

Always a pleasure Oz

@excelemployeeleavetracker1274 2 жыл бұрын

Great video... for the last task regarding the color column...I would pivot column and use the number column as the value. This would save you multiple steps and will match up the numbers to each individual color.

@AccessAnalytic 2 жыл бұрын

Thanks, I’d love to see your approach. I can’t quite picture it. Send in your solution ( details are in the description section ) Cheers

@gopichand5717 2 жыл бұрын

Awesome , your videos are helpful

@AccessAnalytic 2 жыл бұрын

Glad you think so Gopi, thanks

@antonysovy5887 2 жыл бұрын

Hi, this is another great video.

@AccessAnalytic 2 жыл бұрын

Thank you Antony

@johnhackwood1568 Жыл бұрын

Great practical example WYN well done!

@AccessAnalytic Жыл бұрын

Cheers John

@EricaDyson 2 жыл бұрын

Excelllent. That was a great session!

@AccessAnalytic 2 жыл бұрын

Thanks Erica

@MrKamranhaider0 2 жыл бұрын

No doubt a great tip which will be used in many different situations

@AccessAnalytic 2 жыл бұрын

Thanks Kamran

@rameshkandi 2 жыл бұрын

Brilliant 👍 Learned few tricks. Thq.

@AccessAnalytic 2 жыл бұрын

That’s great Ramesh

@adrianoschwenkberg6773 11 ай бұрын

note: on the powerquery options you can de-select the automatic type and automatic promote Header steps

@AccessAnalytic 11 ай бұрын

Yep, that’s a good one to call out

@arbazahmad7177 2 жыл бұрын

Great.. Looks easy but bunch of tricks.. thanks

@AccessAnalytic 2 жыл бұрын

You’re welcome Arbaz

@Dev_Bartwal Жыл бұрын

Awesome 👍👍👍 Keep update us please 😘😘😘 lots of love from India

@AccessAnalytic Жыл бұрын

Thanks, will do

@joserochefort7778 Жыл бұрын

Thanks to you, I'm learning power query like an impressionist builds his canvas, with fine touches, for example I thought that in the query steps we could only refer to the before step and not to any step before; thoughts to people who shared their kwnoledge with you

@AccessAnalytic Жыл бұрын

Glad to help and thanks for taking the time to leave a kind comment letting me know the content is useful

@ImranHussain-xv4se 2 жыл бұрын

Great video,

@AccessAnalytic 2 жыл бұрын

Thank you Imran

@Ratnakumarwrites 2 жыл бұрын

Brilliant sir.

@AccessAnalytic 2 жыл бұрын

Thank you Ratna

@BillSzysz1 2 жыл бұрын

I have just discovered your PQ challenge - great 👍 Of course, this can be done in many ways..... but now we have many new functions in excel so i think we can do this in one cell as well with LAMBDA, LET, TEXT.SPLIT, VSTACK etc. Maybe it's not bad idea for a new "Do the same thing in one cell with excel formulas" challenge? (but i think, it would be for "insider preview" members only) Thanks for video

@AccessAnalytic 2 жыл бұрын

Cheers Bill, I really want to focus this on Power Query as I spend most of my time in Power BI theses days so the awesome new Excel functionality is not so relevant to me

@Luciano_mp 2 жыл бұрын

Great. Thanks!

@AccessAnalytic 2 жыл бұрын

No worries Luciano

@priyankchhajed1407 3 ай бұрын

Awesome 🙇‍♀️

@AccessAnalytic 3 ай бұрын

Thank you! Cheers!

@ziggle314 2 жыл бұрын

This video includes a wonderful set of techniques for optimizing my use of PQ. Thanks a bunch Wyn!

@AccessAnalytic 2 жыл бұрын

No worries! Thanks for the feedback

@vs7388 2 жыл бұрын

awesome

@AccessAnalytic 2 жыл бұрын

Thanks

@NilayMukhopadhyay 2 жыл бұрын

My steps; 1. Split columns (Colours & Values) 2. Renamed (Colour1, Colour2, Colour3 & Value1, Value2, Value3) 3. Unpivoted. 4. Duplicated the query. 5. Filtered both queries attribute column by colour & values. 6. Added index column. 7. Merge queries.

@AccessAnalytic 2 жыл бұрын

Thanks Nilay, if you go down the splitting route then best to split to rows since the number of colours / values could increase with future data.

@NilayMukhopadhyay 2 жыл бұрын

@@AccessAnalytic Yes! Exactly. That's why you guys are experts.

@florincopaci6821 2 жыл бұрын

Hello sir! Thanks again for this clip from which I learned again. Every day is a school day. I ask a question - I use sql and usually when I want to get something I have more than one condition in join - the last case - customers who repeat, who come back and how many times it is repeated etc. I put 2 conditions - one as customer_id = customer_id but the other condition was that order_date> = order_date. It was a self join I tried in Power Query but I don't know how to do the same that the condition 2 to include the operator> = and not only =.Is this possible in Power Query?To join merged 2 tables not only using = operator but also ?I know is a silly question.Thank you

@AccessAnalytic 2 жыл бұрын

Hi Florin, I can’t quite picture the scenario without seeing some data. Initial thought is it sounds more like something DAX would be used for rather than Power Query but I might be misunderstanding. However there seems to be away to do just about anything with M so while I don’t know the answer that doesn’t mean it can’t be done.

@florincopaci6821 2 жыл бұрын

@@AccessAnalytic Thank you so much for the answer!All the best

@AccessAnalytic 2 жыл бұрын

@@florincopaci6821 Posting to the Excel Tech Community with some screenshots / basic sample file will likely get you a quick response techcommunity.microsoft.com/t5/excel/ct-p/Excel_Cat

@florincopaci6821 2 жыл бұрын

@@AccessAnalytic Thank you

@MrWish332 2 жыл бұрын

Hi Wyn, Is there any way to Replace the Column values with the List Items or Replace Entire Column with List, if Yes then please suggest

@AccessAnalytic 2 жыл бұрын

Not sure which part you’re referring to but there is a Table.ColumnNames function to create a list of column names

@MrWish332 2 жыл бұрын

we already have a list, can we replace the list with values in any particular column (Not the column header) or simply replace the entire column with List