Great video!!✌ Reasoning exercise to know which one to use next time 😀, (the opposite rule) : - what display of rows will get if we wrap vector "v" into "k" columns => WRAPROWS(v,k) - what display of columns will get if we wrap vector "v" into "k" rows => WRAPCOLS(v,k) Fun fact : WRAPROWS(v,k)=TRANSPOSE(WRAPCOLS(v,k)) and viceversa 😉 Easy form. alternative for our case If you do not have WRAPROWS (for vert. vector "v", "k" columns ) =INDEX(v,SEQUENCE(ROWS(v)/k,k)) - for WRAPCOLS equivalent we transpose the previous result Unfortunately WRAP...functions deal only with vectors (1D arrays) so I managed to create simple custom-made functions to stack or unstack 2D arrays also. 😉
Excellent video, as always. A query: If I have multiple column unstacking problem (example, data is in multiple rows with each row having a different combination of id, course, grade, university). Is it possible to unstack it and have data reorganized with columns headings id, course, grade, and university). Thanks,
If your data has a pattern, you can use either a formula or PQ to unstack the data. If you have just a few columns, apply the wrap functions, one at a time and finally VSTACK the data.
hi chanddo can make us the tutorial how to use BA or DA or SQL with help of Ai tools ... It would be great from you to add-on these topic as your explanation is very understandable... waiting for videos
Thanks for this suggestion. I am in the process of adding few more AI videos. Here is one I did few weeks ago. You should definitely watch it - ruclips.net/video/xGqH__hLVcU/видео.html
It is related to the data format I get when I download ingredients data from Euromonitor. Pls let me know how can I share my problem in detail wi th you
hi chandu bhaiya i downloaded a badly structured sample file from internet i have its answer also but i don't understand how to format it. How can you give solution ...
Hmm.. it is hard for me to provide 1:1 help like this. I suggest posting about this in a forum. You can solve a lot of data problems with Power Query. If you have time, learn it. Here is a detailed video - ruclips.net/video/PiFAa_jjaEI/видео.html
Sir I have Microsoft office 2021 version plz guide me power query step bcz I put a lot effort but still not solved this problem it's not working when I follow yours steps sir plz reply the previous video
Straight to the point, with a little bit of humour chucked in..... you do some good videos
Thank you :)
Great video!!✌
Reasoning exercise to know which one to use next time 😀, (the opposite rule) :
- what display of rows will get if we wrap vector "v" into "k" columns => WRAPROWS(v,k)
- what display of columns will get if we wrap vector "v" into "k" rows => WRAPCOLS(v,k)
Fun fact : WRAPROWS(v,k)=TRANSPOSE(WRAPCOLS(v,k)) and viceversa 😉
Easy form. alternative for our case If you do not have WRAPROWS (for vert. vector "v", "k" columns )
=INDEX(v,SEQUENCE(ROWS(v)/k,k))
- for WRAPCOLS equivalent we transpose the previous result
Unfortunately WRAP...functions deal only with vectors (1D arrays) so I managed to create simple custom-made functions to stack or unstack 2D arrays also. 😉
Those are some great additions @Excelambda
GREAT video, thank you Chandoo. The symplest unstacked I have seen with PQ
1:04 I thought, I just got a call from my boss in the dinner time.
haha...
Very cool trick Chandoo, thanks!
Glad you like it!
You continue to amaze me, thank you for the tips
So nice of you
exactly what I was looking for 😊
Great and fun video. Thansks
Great tutorial. Informative and gave me a few laughs along the way. Keep the gold nuggets coming my friend. Cheers JV 👍😎🇦🇺
Glad you enjoyed it
Very useful trick especially power query ones. list and table rows.
I am glad you liked it.
Thank You Sir Nice one
You are welcome Taizoon.
Superb 🧡
Amazing work as always @chandoo😀
You are welcome :)
Excellent!
Glad you liked it!
I liked this! Thank you Chandoo...
My pleasure 😊
OK. What if the first 6 rows are column headers?
Any tip is appreciated.
This function not available in excel 2016, but learning from you is great
You can use the Power Query option in 2016 (or 2013 too).
Thank you!🙂
You’re welcome 😊
Hey Chandu sir, I confused when your Microsoft Teams call rang up😁
Thanks chandu.. really great..but i solve similar kind of problem through Google sheets function..
AWESOME 😍
Excellent video, as always. A query: If I have multiple column unstacking problem (example, data is in multiple rows with each row having a different combination of id, course, grade, university). Is it possible to unstack it and have data reorganized with columns headings id, course, grade, and university). Thanks,
If your data has a pattern, you can use either a formula or PQ to unstack the data. If you have just a few columns, apply the wrap functions, one at a time and finally VSTACK the data.
Great Sir
You are welcome.
Great 👍
You are welcome :)
Amazing
Thanks
hi chanddo
can make us the tutorial how to use BA or DA or SQL with help of Ai tools ...
It would be great from you to add-on these topic as your explanation is very understandable...
waiting for videos
Thanks for this suggestion. I am in the process of adding few more AI videos. Here is one I did few weeks ago. You should definitely watch it - ruclips.net/video/xGqH__hLVcU/видео.html
What if i have two types of group rows like one group with vendor data (3 rows) and then 5 rows with each invoice information?
20/6/23 I promise to be a data analyst
Meet soon 😊
Chatur
What's rhe update did u become?
@@MaharanaPratap69 it’s a learning process, working on it day by day
@@travelsphere7402 thanks bro sounds like it but feeling is different
Hi Chandoo, I have a problem very similar to this that I have been experiencing from last few weeks. How can I seek your help for that?
It is related to the data format I get when I download ingredients data from Euromonitor. Pls let me know how can I share my problem in detail wi th you
I dont have 6 size, my data has different size in each type what to do now
I would like to see some chatGPT and AI related videos, bcz your videos are appearing from layman's POV.
hi chandu bhaiya i downloaded a badly structured sample file from internet i have its answer also but i don't understand how to format it. How can you give solution ...
Hmm.. it is hard for me to provide 1:1 help like this. I suggest posting about this in a forum. You can solve a lot of data problems with Power Query. If you have time, learn it. Here is a detailed video - ruclips.net/video/PiFAa_jjaEI/видео.html
=IF(MOD(ROW(C5);6)=5;TEXTSPLIT(TEXTJOIN(";";;C5:C10);";");"") this gives you all data unstacked in 6 columns. You just have to delete empty rows.
That is a cool function. But if I have TEXTSPLIT() in my Excel, I will also have WRAP functions. So I prefer to use those.
Unable to hear
Sir I have Microsoft office 2021 version plz guide me power query step bcz I put a lot effort but still not solved this problem it's not working when I follow yours steps sir plz reply the previous video
If you have a mac, you cannot use powerquery like this.
@@Sekoleyte android ha
You can use Power Query steps. You just need to enable the "Formula bar" in view ribbon in Power Query editor.
Sir Your videos are nice but in English we want in Hindi
Thank you. I don't know enough Hindi to make videos. Do watch with Hindi sub-titles (RUclips sometimes autogenerates these)
Not sure, y I am not able to hear your voice
Sorry to hear that (excuse the pun).
Try again with a different device.