PQ Challenge Splits and Lists

Подписаться 91 тыс.

Просмотров 12 тыс.

50% 1

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...

Хобби

Опубликовано:

6 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 75

@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

@raimundojs9547 Год назад

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

@AccessAnalytic Год назад

You're very welcome

@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

@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.

@vacilando86 2 года назад

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

@AccessAnalytic 2 года назад

Glad to help, thanks Alpay

@MrWish332 2 года назад

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

@AccessAnalytic 2 года назад

Thanks Vishal

@Dev_Bartwal 2 года назад

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

@AccessAnalytic 2 года назад

Thanks, will do

@Howlandta 2 года назад

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

@AccessAnalytic 2 года назад

That’s great to hear Tyrone

@johannlotter4984 2 года назад

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

@AccessAnalytic 2 года назад

Thanks Johann

@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

@iankr 2 года назад

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

@AccessAnalytic 2 года назад

Yeah I picked up a few pointers here

@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

@OzduSoleilDATA 2 года назад

GOOOOOD Stuff! And thanks for the shoutout. 🚀

@AccessAnalytic 2 года назад

Always a pleasure Oz

@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

@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

@antonysovy5887 2 года назад

Hi, this is another great video.

@AccessAnalytic 2 года назад

Thank you Antony

@martyc5674 2 года назад

Thanks Wyn- Some great tips in this one 👍

@AccessAnalytic 2 года назад

Cheers Martin

@arbazahmad7177 2 года назад

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

@AccessAnalytic 2 года назад

You’re welcome Arbaz

@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

@gopichand5717 2 года назад

Awesome , your videos are helpful

@AccessAnalytic 2 года назад

Glad you think so Gopi, thanks

@johnhackwood1568 Год назад

Great practical example WYN well done!

@AccessAnalytic Год назад

Cheers John

@rameshkandi 2 года назад

Brilliant 👍 Learned few tricks. Thq.

@AccessAnalytic 2 года назад

That’s great Ramesh

@ImranHussain-xv4se 2 года назад

Great video,

@AccessAnalytic 2 года назад

Thank you Imran

@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

@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

@priyankchhajed1407 3 месяца назад

Awesome 🙇‍♀️

@AccessAnalytic 3 месяца назад

Thank you! Cheers!

@Luciano_mp 2 года назад

Great. Thanks!

@AccessAnalytic 2 года назад

No worries Luciano

@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